VB.NET :: Konsep Code Generator Dan Implementasinya

Prinsip dasar code template yang kita gunakan adalah berdasarkan keteraturan yang telah digambarkan pada artikel sebelumnya, penamaan pada tiap komponen code template diambil dari parameter yang kita dapatkan dari database.

Design UI.

Sebelum mulai ke level code, mari kita membuat interface untuk code generator kita, ikutilah langkah -langkahnya sebagai berikut :

1. Buatlah Tampilan seperti terlihat pada gambar dibawah ini 

Berikut Daftar Control dan keterangannya :

Jenis Komponen	Nama	Text
Form	Form1	FormMain
SplitContainer	SplitContainer1
ToolStrip	ToolStrip1
ToolStripLabel	ToolStripLabel1	Database
ToolStripComboBox	ToolStripComboBox1
ListBox	LstTable
TableLayoutPanel	TableLayoutPanel2
Button	BtnGenerateAllClass	Generate All Class
Button	BtnGenerateClass	Generate Class
TabControl	TabControl2
TabPage	TabPage1	Class
TextBox	ClassTextBox
TabPage	TabPage2	Stored Procedure
TextBox	StoredProcedureTextBox

Code Template.

Setelah selesai dengan design UI, mari kita mulai membuat codenya, bukalah code designer dari FormMain, kemudian ikuti imports item dan deklarasi seperti pada contoh berikut :

Deklarasi.

Imports System.Data.SqlClient

Imports System.IO

Public Class FormMain

  Dim MyConnection As String

  Dim SQL As String

  Dim Conn As New SqlConnection(MyConnection)

  Dim CMD As New SqlCommand(SQL, Conn)

  Dim RS As SqlDataReader

  Dim MyDetailTableName As String

  Dim TablePrefix As String = "tbl"

End Class

Pada code diatas terlihat sebuah variable TablePrefix, variable tersebut digunakan untuk menghilangkan prefix pada penamaan table dalam database, sebagai contoh apabila table dalam database selalu menggunakan penamaan seperti : tblBarang, maka penamaan yang akan dibaca oleh code generator adalah Barang.

Form1_Load.

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Conn.ConnectionString = "Data Source=.;Initial Catalog=master;Integrated Security=True;"

    Try

      Conn.Open()

      Conn.Close()

    Catch ex As Exception

      Conn.Close()

      MsgBox("gagal konek")

      Exit Sub

    End Try

    SQL = "Select Name From sys.databases order by name asc"

    Conn.Open()

    CMD.CommandText = SQL

    RS = CMD.ExecuteReader

    Do Until RS.Read = False

      Dim ch As New TreeNode

      CboDatabase.Items.Add(RS("Name"))

    Loop

    Conn.Close()

  End Sub

CboDatabase_SelectedIndexChanged.

Private Sub CboDatabase_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CboDatabase.SelectedIndexChanged

    LstTable.Items.Clear()

    MyConnection = "Data Source=.;Initial Catalog=" & CboDatabase.Text & ";Integrated Security=True;"

    Conn.ConnectionString = MyConnection

    SQL = "Select Name From sys.tables order by name asc"

    Conn.Open()

    CMD.CommandText = SQL

    RS = CMD.ExecuteReader

    Do Until RS.Read = False

      Dim ch As New TreeNode

      LstTable.Items.Add(RS("Name"))

    Loop

    Conn.Close()

    If LstTable.Items.Count > 0 Then LstTable.SelectedIndex = 0

  End Sub

BtnGenerateClass_Click.

Private Sub BtnGenerateClass_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnGenerateClass.Click

    GenerateClass(LstTable.SelectedIndex)

  End Sub

BtnGenerateAllClass_Click.

Private Sub BtnGenerateAllClass_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnGenerateAllClass.Click

    For i As Integer = 0 To LstTable.Items.Count - 1

      GenerateClass(i)

    Next

  End Sub

Fungsi GetDataType.

Fungsi GetDataType berguna untuk mendapatkan tipe data dari database yang nantinya akan kita terjemahkan kedalam tipe data VB.net, tambahkan function ini kedalam form1, berikut codenya.

Function GetDataType(ByVal TypeID As Integer) As String

  Dim xConn As New SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True;")

    Dim xCMD As New SqlCommand("", xConn)

    Dim xRS As SqlDataReader

    Dim MyDataType As String

    xConn.Open()

    xCMD.CommandText = "Select name From sys.types where system_type_id=" & TypeID

    xRS = xCMD.ExecuteReader

    xRS.Read()

    MyDataType = xRS("name")

    xConn.Close()

    Select Case LCase(MyDataType)

      Case "bigint" : MyDataType = "Double"

      Case "int" : MyDataType = "Integer"

      Case "bit" : MyDataType = "Boolean"

      Case "date" : MyDataType = "Date"

      Case "datetime" : MyDataType = "DateTime"

      Case "varchar" : MyDataType = "String"

      Case "nvarchar" : MyDataType = "String"

      Case "time" : MyDataType = "Time"

      Case "tinyint" : MyDataType = "Integer"

      Case "smallint" : MyDataType = "Integer"

    End Select

    Return MyDataType

  End Function

Dari code diatas, anda dapat menambahkan berbagai tipe data yang anda butuhkan atau gunakan dalam database anda.

Fungsi GetPrimaryKey.

Fungsi GetPrimaryKey digunakan untuk mengambil informasi field mana dalam table yang terpilih yang menjadi primary key, fungsi ini berguna untuk secara otomatis membuat fungsi memanggil object berdasarkan field primary key nya, berikut codenya.

 Function GetPrimaryKey(ByVal MyTableName As String) As String

    Dim MyKey As String

    MyConnection = "Data Source=.;Initial Catalog=" & CboDatabase.Text & ";Integrated Security=True;"

    SQL = "SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " & _

          "WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 " & _

          "AND table_name='" & TablePrefix & MyTableName & "'"

    Conn.Open()

    CMD.CommandText = SQL

    RS = CMD.ExecuteReader

    RS.Read()

    MyKey = RS("column_name")

    Conn.Close()

    Return MyKey

  End Function

Apabila anda mempunyai table yang tidak memiliki primary key, nanti anda akan harus memodifikasi sedikit hasil codenya.

Fungsi GenerateClass.

Sub GenerateClass(ByVal xIndex As Integer)

    Dim tbl As Integer

    Dim MyTable As String

    Dim MyPrimaryKey As String

    ClassTextBox.Text = ""

    LstTable.SelectedIndex = xIndex

   

    ClassTextBox.Text = ""

    SQL = "Select Object_ID From sys.Tables where name='" & LstTable.Text & "'"

    Conn.Open()

    CMD.CommandText = SQL

    RS = CMD.ExecuteReader

    RS.Read()

    tbl = RS("Object_ID")

    Conn.Close()

    MyTable = Mid(LstTable.Text, TablePrefix.Length + 1)

    MyPrimaryKey = GetPrimaryKey(MyTable)

    ClassTextBox.Text = "Imports System.Data.SQLClient"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "Public Class " & MyTable

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "Dim Bind as BindingSource"

    Application.DoEvents()

    '--generate property

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "#Region ""Properties"""

    Dim MyListOfField As New Collection

    SQL = "Select Name, system_type_id From sys.columns where object_id=" & tbl & " Order By column_id"

    Conn.Open()

    CMD.CommandText = SQL

    RS = CMD.ExecuteReader

    Do Until RS.Read = False

      MyListOfField.Add(RS("Name") & "," & RS("system_type_id"))

    Loop

    Conn.Close()

    Dim Arr() As String

    For i As Integer = 1 To MyListOfField.Count

      Arr = Split(MyListOfField(i), ",")

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "Private x" & Arr(0) & " As " & GetDataType(Arr(1))

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "Public Property " & Arr(0) & "() As " & GetDataType(Arr(1))

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Get"

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & "Return x" & Arr(0)

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "End Get"

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Set (byval value As " & GetDataType(Arr(1)) & ")"

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & "x" & Arr(0) & " = value"

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "End Set"

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "End Property"

    Next

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "#End Region"

    '--/

    Application.DoEvents()

    '--generate Standard methods

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "#Region ""Standard Methods"""

    '--[create SP for Get Object]

    StoredProcedureTextBox.Text = "Begin Transaction"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "Create Procedure Get_" & MyTable & "_By" & MyPrimaryKey

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "(@" & MyPrimaryKey & " Int)"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "As"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & vbTab & "Select * From TBL_" & MyTable & " Where " & MyPrimaryKey & " = @" & MyPrimaryKey

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "Return"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf

    '--[]/

    '--[get Object]

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "Public Function Get_" & MyTable & "_By" & MyPrimaryKey & "(ByVal My" & MyPrimaryKey & " As Integer) As " & MyTable

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim My" & MyTable & " As New " & MyTable

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Using DataTable As DataTable = ExecuteDataTable(""Get_" & MyTable & "_By" & MyPrimaryKey & """, New SqlParameter(""" & MyPrimaryKey & """, My" & MyPrimaryKey & "))"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & "If DataTable IsNot Nothing AndAlso DataTable.Rows.Count > 0 Then "

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & "With DataTable.Rows(0)"

    For i As Integer = 1 To MyListOfField.Count

      Arr = Split(MyListOfField(i), ",")

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & vbTab & "If Not IsDBNull(.Item(" & Arr(0) & ")) Then My" & MyTable & "." & Arr(0) & " = .Item(" & Arr(0) & ")"

    Next

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & "End With"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & "End IF"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "End Using"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Return My" & MyTable

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "End Function"

    '--[]/

    Application.DoEvents()

    '--[create SP for Get Object]

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "Create Procedure Get_" & MyTable & "_List"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "As"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & vbTab & "Select * From TBL_" & MyTable & " Order By " & MyPrimaryKey

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "Return"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "Commit"

    StoredProcedureTextBox.Text = StoredProcedureTextBox.Text & vbCrLf & "End Transaction"

    '--[]/

    '--[get list of object]

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "Public Function Get_" & MyTable & "_List() As List(of " & MyTable & ")"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim MyList" & MyTable & " As New List(of " & MyTable & ")"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Using DataTable As DataTable = ExecuteDataTable(""Get_" & MyTable & "_List"")"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & "If DataTable IsNot Nothing AndAlso DataTable.Rows.Count > 0 Then"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & "For i As Integer = 0 To DataTable.Rows.Count - 1"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & vbTab & "With DataTable.Rows(i)"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & vbTab & vbTab & "Dim My" & MyTable & " As New " & MyTable

    For i As Integer = 1 To MyListOfField.Count

      Arr = Split(MyListOfField(i), ",")

      ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & vbTab & vbTab & "IF Not IsDBNull(.Item(" & Arr(0) & ")) then My" & MyTable & "." & Arr(0) & " = .Item(" & Arr(0) & ")"

    Next

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & vbTab & vbTab & "MyList" & MyTable & ".Add(My" & MyTable & ")"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & vbTab & "End With"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & vbTab & "Next"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & vbTab & "End IF"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "End Using"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Return MyList" & MyTable

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "End Function"

    '--[]/

    Application.DoEvents()

    '--[Add Data sub routine]

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "Public Sub Add_" & MyTable & "()"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim QueryCollection" & MyTable & " As New Collection"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim MyQueryTransaction As QueryTransaction = QueryTransaction_Add()"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "QueryCollection" & MyTable & ".Add(MyQueryTransaction)"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim Result As String = DAC.SaveTransaction(QueryCollection)"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "End Sub"

    '--[]/

    '--[Update Data sub routine]

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "Public Sub Update_" & MyTable & "()"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim QueryCollection" & MyTable & " As New Collection"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim MyQueryTransaction As QueryTransaction = QueryTransaction_Update()"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "QueryCollection" & MyTable & ".Add(MyQueryTransaction)"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbTab & "Dim Result As String = DAC.SaveTransaction(QueryCollection)"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & "End Sub"

    '--[]/

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "#End Region"

    '--/

    ' create custom method region

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "#Region ""Custom Methods"""

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & ""

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "#End Region"

    ClassTextBox.Text = ClassTextBox.Text & vbCrLf & vbCrLf & "End Class "

    File.WriteAllText(Application.StartupPath & "\Generated Class\" & MyTable & ".vb", ClassTextBox.Text)

  End Sub

Demikian contoh sederhana pembuatan code generator, anda tentunya dapat menambahkan sendiri fungsi - fungsi yang anda butuhkan, semoga bermanfaat.

Source : surya-pradhana