¿Sabes crear un generador de codigo en vb.net?

Hola, bienvenidos a mi post. Introducción Esta herramienta es útil cuando tienes que construir tu propia Clase (Class) que utilizaras para darle mantenimiento a una tabla ubicada en una base de datos y servidor específicos. Este modulo te ilustra el uso del componente SQLDMO y el espacio de nombres System.IO. El programa es sencillo y lo primero que se hace es seleccionar los parámetros del Servidor, Base de Datos y Tabla al que se desea conectar. Luego presionas el botón Generar clase y el programa te presente en un texbox personalizado el código necesario para construir tu propia clase. El código veras la definición de los tipos de datos para las propiedades, las propiedades en si, un constructor, código simple de Inserción y Actualización y por último un método para realizar búsquedas. Si se logra darle la utilidad conveniente a este modulo te ahorra el tiempo para esta creando las clase por cada tabla que tengas que manipular datos. Solo tendrías que crear el modulo de la clase y copiar el código que te genera este programa. Codigo de ClassItemCombo: Public Class ClassItemCombo Private _Codigo As String Private _Descripcion As String Private _Indice As Integer Public Sub New(ByVal Code As String, ByVal Name As String) _Codigo = Code _Descripcion = Name End Sub Public Property Codigo() As String Get Return _Codigo End Get Set(ByVal Value As String) _Codigo = Value End Set End Property Public Property Descripcion() As String Get Return _Descripcion End Get Set(ByVal Value As String) _Descripcion = Value End Set End Property Public Property Indice() As Integer Get Return _Indice End Get Set(ByVal Value As Integer) _Indice = Value End Set End Property Public Overrides Function ToString() As String Return Descripcion End Function End Class Codigo de FRMGENERARCLASE.VB: Imports System.IO Public Class FrmGenerarClase Inherits System.Windows.Forms.Form Private vIndCambio As Boolean #Region " Windows Form Designer generated code " Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call End Sub 'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub 'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer 'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents CmdGenerar As System.Windows.Forms.Button Friend WithEvents TxtCodigo As System.Windows.Forms.TextBox Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox Friend WithEvents CmdTablas As System.Windows.Forms.Button Friend WithEvents CmdBaseDatos As System.Windows.Forms.Button Friend WithEvents CboDatabase As System.Windows.Forms.ComboBox Friend WithEvents CboServidores As System.Windows.Forms.ComboBox Friend WithEvents CmdServidores As System.Windows.Forms.Button Friend WithEvents CboTablas As System.Windows.Forms.ComboBox Private Sub InitializeComponent() Me.CmdGenerar = New System.Windows.Forms.Button Me.TxtCodigo = New System.Windows.Forms.TextBox Me.GroupBox1 = New System.Windows.Forms.GroupBox Me.CmdTablas = New System.Windows.Forms.Button Me.CmdBaseDatos = New System.Windows.Forms.Button Me.CboTablas = New System.Windows.Forms.ComboBox Me.CboDatabase = New System.Windows.Forms.ComboBox Me.CboServidores = New System.Windows.Forms.ComboBox Me.CmdServidores = New System.Windows.Forms.Button Me.GroupBox1.SuspendLayout() Me.SuspendLayout() ' 'CmdGenerar ' Me.CmdGenerar.Location = New System.Drawing.Point(340, 496) Me.CmdGenerar.Name = "CmdGenerar" Me.CmdGenerar.Size = New System.Drawing.Size(120, 32) Me.CmdGenerar.TabIndex = 0 Me.CmdGenerar.Text = "Generar Clase" ' 'TxtCodigo ' Me.TxtCodigo.Location = New System.Drawing.Point(12, 112) Me.TxtCodigo.Multiline = True Me.TxtCodigo.Name = "TxtCodigo" Me.TxtCodigo.ReadOnly = True Me.TxtCodigo.ScrollBars = System.Windows.Forms.ScrollBars.Both Me.TxtCodigo.Size = New System.Drawing.Size(448, 372) Me.TxtCodigo.TabIndex = 3 Me.TxtCodigo.Text = "" Me.TxtCodigo.WordWrap = False ' 'GroupBox1 ' Me.GroupBox1.Controls.Add(Me.CmdTablas) Me.GroupBox1.Controls.Add(Me.CmdBaseDatos) Me.GroupBox1.Controls.Add(Me.CboTablas) Me.GroupBox1.Controls.Add(Me.CboDatabase) Me.GroupBox1.Controls.Add(Me.CboServidores) Me.GroupBox1.Controls.Add(Me.CmdServidores) Me.GroupBox1.Location = New System.Drawing.Point(8, 4) Me.GroupBox1.Name = "GroupBox1" Me.GroupBox1.Size = New System.Drawing.Size(452, 104) Me.GroupBox1.TabIndex = 4 Me.GroupBox1.TabStop = False ' 'CmdTablas ' Me.CmdTablas.Enabled = False Me.CmdTablas.Location = New System.Drawing.Point(372, 72) Me.CmdTablas.Name = "CmdTablas" Me.CmdTablas.Size = New System.Drawing.Size(72, 24) Me.CmdTablas.TabIndex = 13 Me.CmdTablas.Text = "Tablas" ' 'CmdBaseDatos ' Me.CmdBaseDatos.Enabled = False Me.CmdBaseDatos.Location = New System.Drawing.Point(372, 44) Me.CmdBaseDatos.Name = "CmdBaseDatos" Me.CmdBaseDatos.Size = New System.Drawing.Size(72, 24) Me.CmdBaseDatos.TabIndex = 12 Me.CmdBaseDatos.Text = "Base Datos" ' 'CboTablas ' Me.CboTablas.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList Me.CboTablas.Location = New System.Drawing.Point(164, 72) Me.CboTablas.Name = "CboTablas" Me.CboTablas.Size = New System.Drawing.Size(204, 21) Me.CboTablas.TabIndex = 11 ' 'CboDatabase ' Me.CboDatabase.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList Me.CboDatabase.Location = New System.Drawing.Point(164, 44) Me.CboDatabase.Name = "CboDatabase" Me.CboDatabase.Size = New System.Drawing.Size(204, 21) Me.CboDatabase.TabIndex = 10 ' 'CboServidores ' Me.CboServidores.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList Me.CboServidores.Location = New System.Drawing.Point(164, 16) Me.CboServidores.Name = "CboServidores" Me.CboServidores.Size = New System.Drawing.Size(204, 21) Me.CboServidores.TabIndex = 9 ' 'CmdServidores ' Me.CmdServidores.Location = New System.Drawing.Point(372, 16) Me.CmdServidores.Name = "CmdServidores" Me.CmdServidores.Size = New System.Drawing.Size(72, 24) Me.CmdServidores.TabIndex = 8 Me.CmdServidores.Text = "Servidores" ' 'FrmGenerarClase ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(472, 540) Me.Controls.Add(Me.GroupBox1) Me.Controls.Add(Me.TxtCodigo) Me.Controls.Add(Me.CmdGenerar) Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle Me.Name = "FrmGenerarClase" Me.Text = "FrmGenerarClase" Me.GroupBox1.ResumeLayout(False) Me.ResumeLayout(False) End Sub #End Region Private Sub CmdGenerar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdGenerar.Click If Not Me.CboServidores.SelectedItem Is Nothing _ And Not CboDatabase.SelectedItem Is Nothing _ And Not CboTablas.SelectedItem Is Nothing Then Me.Cursor = System.Windows.Forms.Cursors.WaitCursor Call GenerarCodigo(Convert.ToString(Me.CboServidores.SelectedItem), _ CType(CboDatabase.SelectedItem, ClassItemCombo).Descripcion, _ CType(CboTablas.SelectedItem, ClassItemCombo).Descripcion) Call PresentarArchivo() Me.Cursor = System.Windows.Forms.Cursors.Default End If End Sub Private Sub GenerarCodigo(ByVal Servidor As String, ByVal BaseDatos As String, ByVal Tabla As String) Dim CadCnn As String Dim Cnn As SqlClient.SqlConnection Dim Ad As SqlClient.SqlDataAdapter Dim Dt As New DataTable Dim Dc As DataColumn Dim Isql As String, i As Integer Dim st As IO.StreamWriter Dim LstCampos As String, N As Integer CadCnn = "Data Source=" + Servidor + ";Initial Catalog=" + BaseDatos + ";Integrated Security=yes;" Isql = "Select * From " + Tabla Cnn = New SqlClient.SqlConnection(CadCnn) Ad = New SqlClient.SqlDataAdapter(Isql, Cnn) Ad.Fill(Dt) If (Not File.Exists(Application.StartupPath & "test.txt")) Then st = File.CreateText(Application.StartupPath & "test.txt") Else File.Delete(Application.StartupPath & "test.txt") st = File.CreateText(Application.StartupPath & "test.txt") End If 'Cantidad de Campos N = Dt.Columns.Count 'Declaraciones de Propiedades Privadas For Each Dc In Dt.Columns st.WriteLine("Private _" + Dc.Caption + " As " + Mid(Dc.DataType.ToString, 8, Len(Dc.DataType.ToString) - 7)) LstCampos += Dc.Caption + "," Next 'Quitamos la ultima coma LstCampos = Mid(LstCampos, 1, Len(LstCampos) - 1) st.WriteLine("") 'Creacion de Propiedades st.WriteLine("#Region ""Propiedades""") For Each Dc In Dt.Columns st.WriteLine("Public Property " + Dc.Caption + "() As " + Mid(Dc.DataType.ToString, 8, Len(Dc.DataType.ToString) - 7)) st.WriteLine(ControlChars.Tab + "Get") st.WriteLine(ControlChars.Tab + ControlChars.Tab + "Return(_" + Dc.Caption + ")") st.WriteLine(ControlChars.Tab + "End Get") st.WriteLine(ControlChars.Tab + "Set (Value As " + Mid(Dc.DataType.ToString, 8, Len(Dc.DataType.ToString) - 7) + ")") st.WriteLine(ControlChars.Tab + ControlChars.Tab + "_" + Dc.Caption + " = Value") st.WriteLine(ControlChars.Tab + "End Set") st.WriteLine("End Property") Next st.WriteLine("#End Region") st.WriteLine("") st.WriteLine("'------Instruccion INSERT-------") 'Construccion de la declaracion INSERT st.WriteLine("""INSERT INTO " & CType(CboTablas.SelectedItem, ClassItemCombo).Descripcion & " (" + LstCampos + ")"" _ ") st.WriteLine("& ""VALUES("" _") For Each Dc In Dt.Columns st.Write(ControlChars.Tab + " & Me." + Dc.Caption) If Not N - 1 = Dc.Ordinal Then st.WriteLine(" & "","" _") End If Next st.WriteLine(" & "")""") st.WriteLine("") st.WriteLine("'------Instruccion UPDATE-------") 'Construcion de la declaracion UPDATE st.WriteLine("""UPDATE " & CType(CboTablas.SelectedItem, ClassItemCombo).Descripcion & " Set "" _") For Each Dc In Dt.Columns st.Write(ControlChars.Tab + "& """ + Dc.Caption + " = "" & " + "Me." + Dc.Caption) If Not N - 1 = Dc.Ordinal Then st.WriteLine(" & "","" _") End If Next 'Constructores st.WriteLine("") st.WriteLine("") st.WriteLine("'------Constructor New-------") st.WriteLine("#Region ""Constructores""") st.WriteLine("Public Sub New()") For Each Dc In Dt.Columns st.WriteLine(ControlChars.Tab + Dc.Caption + " = Nothing") Next st.WriteLine("End Sub") st.WriteLine("#End Region") 'Metodo Buscar st.WriteLine("") st.WriteLine("'------Metodo Buscar-------") st.WriteLine("#Region ""Metodos""") st.WriteLine("Public Sub Buscar()") st.WriteLine(ControlChars.Tab + "Dim Isql As String") st.WriteLine(ControlChars.Tab + "Dim Rst As SqlDataReader") st.WriteLine(ControlChars.Tab + "Dim CmdQuery As SqlCommand") st.WriteLine("") st.WriteLine(ControlChars.Tab + "Isql = ""Select * From " & CType(CboTablas.SelectedItem, ClassItemCombo).Descripcion & " Where """) st.WriteLine(ControlChars.Tab + "CmdQuery = New SqlCommand(Isql, ConClinica)") st.WriteLine(ControlChars.Tab + "Rst = CmdQuery.ExecuteReader(CommandBehavior.Default)") st.WriteLine(ControlChars.Tab + "If Rst.Read Then") For Each Dc In Dt.Columns st.WriteLine(ControlChars.Tab + ControlChars.Tab + "Me." + Dc.Caption + " = Rst(""" + Dc.Caption + """)") Next st.WriteLine(ControlChars.Tab + "End If") st.WriteLine("") st.WriteLine("End Sub") st.WriteLine("#End Region") st.Close() Dt.Dispose() Ad = Nothing End Sub Private Sub PresentarArchivo() Dim st As IO.StreamReader Dim Linea As String TxtCodigo.Text = "" st = File.OpenText(Application.StartupPath & "test.txt") Do Linea = st.ReadLine TxtCodigo.Text += Linea + ControlChars.CrLf Loop Until Linea Is Nothing st.Close() End Sub Private Sub CmdBaseDatos_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdBaseDatos.Click Dim CadCnn As String = "Data Source=" + Convert.ToString(CboServidores.SelectedItem) + ";Initial Catalog=Master;Integrated Security=Yes;" Dim Cnn As New SqlClient.SqlConnection(CadCnn) Dim Cmdquery As New SqlClient.SqlCommand("Select dbid,name From sysdatabases", Cnn) Dim RstTablas As SqlClient.SqlDataReader Try Cnn.Open() Me.Cursor = System.Windows.Forms.Cursors.WaitCursor Me.CboDatabase.Items.Clear() RstTablas = Cmdquery.ExecuteReader While RstTablas.Read Dim ItemCombo As New ClassItemCombo(Convert.ToString(RstTablas(0)), Convert.ToString(RstTablas(1))) CboDatabase.Items.Add(ItemCombo) End While Me.Cursor = System.Windows.Forms.Cursors.Default RstTablas.Close() Me.CmdTablas.Enabled = False Me.CboTablas.Items.Clear() Catch ex As Exception MessageBox.Show("Conección fallida. " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally Cmdquery = Nothing Cnn = Nothing End Try End Sub Private Sub CmdTablas_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdTablas.Click Dim CadCnn As String = "Data Source=" + Convert.ToString(CboServidores.SelectedItem) + ";Initial Catalog=" + CType(CboDatabase.SelectedItem, ClassItemCombo).Descripcion + ";Integrated Security=Yes;" Dim Cnn As New SqlClient.SqlConnection(CadCnn) Dim Cmdquery As New SqlClient.SqlCommand("Select id,name From sysobjects Where xtype = 'U' Order by name", Cnn) Dim RstTablas As SqlClient.SqlDataReader Try Cnn.Open() Me.Cursor = System.Windows.Forms.Cursors.WaitCursor Me.CboTablas.Items.Clear() RstTablas = Cmdquery.ExecuteReader While RstTablas.Read Dim ItemCombo As New ClassItemCombo(Convert.ToString(RstTablas(0)), Convert.ToString(RstTablas(1))) CboTablas.Items.Add(ItemCombo) End While Me.Cursor = System.Windows.Forms.Cursors.Default RstTablas.Close() Catch ex As Exception MessageBox.Show("Conección fallida. " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally Cmdquery = Nothing Cnn = Nothing End Try End Sub Private Sub CmdServidores_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdServidores.Click Dim i As Integer Dim oNames As SQLDMO.NameList Dim oSQLApp As SQLDMO.Application Me.Cursor = System.Windows.Forms.Cursors.WaitCursor oSQLApp = New SQLDMO.Application oNames = oSQLApp.ListAvailableSQLServers CboServidores.Items.Clear() For i = 1 To oNames.Count CboServidores.Items.Add(oNames.Item(i)) Next i Me.CmdBaseDatos.Enabled = False Me.CboDatabase.Items.Clear() Me.CmdTablas.Enabled = False Me.CboTablas.Items.Clear() Me.Cursor = System.Windows.Forms.Cursors.Default End Sub Private Sub CboServidores_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboServidores.SelectedIndexChanged Me.CmdBaseDatos.Enabled = True End Sub Private Sub CboDatabase_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboDatabase.SelectedIndexChanged Me.CmdTablas.Enabled = True End Sub Private Sub FrmGenerarClase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End Sub End Class