Tentang "Column Schema" pada suatu Table

PRA KATA

Suatu saat adakalnya kita ingin mendapatkan informasi detail tentang suatu table, mulai dari nama kolom, tipe datanya serta atrribute yang lainnya. banyak hal yang bisa dilakukan dengan mengetahui infromasi "Schema" suatu table, salah satunya mungkin dengannya kita bisa membuat sebuah progam aplikasi untuk menggenerate script SQL seperti Insert, Update maupun delete, atau untuk keperluan lainnya.

PEMBAHASAN

Tulisan ini diperkaya dan dimodifikasi dari sumber berikut ini (How To Retrieve Column Schema Using the DataReader GetSchemaTable Method and Visual Basic .NET) dengan gaya pembahasan yg punya blog ini tentunya.

Untuk mendapatkan informasi seputar Column schema dari suatu tabel ada beberapa cara yang bisa dilakukan, pada tulisan ini kita akan memanfaatkan fasilitas ExecuteReader(commandBehavior) dari OleDBCommand.

Inti dari cara mendapatkan column schema dari suatu tabel adalah pada pemberian parameter dari fungsi ExecuteReader yaitu CommandBehavior, CommandBehavior adalah suatu enumeration yang memberikan gambaran mengenai hasil query dan efeknya pada database. Anggota dari CommandBehavior adalah sbb : CloseConnection, Default, KeyInfo, SchemaOnly, SequentialAccess, SingleResult dan SingleRow.

kemudian dari sekian banyak member CommandBehavior itu, mana yg sesuai dengan keinginan kita, yaitu untuk mendapatkan column schema dari suatu tabel?? jawabnya adalah hanya dua yaitu KeyInfo, SchemaOnly. Perbedaan antara penggunaan parameter enum Keyinfo dan SchemaOnly adalah sebagai berikut, jika kita menggunakan param enum KeyInfo query yang dihasilkan adalah berisi informasi kolom dan Primary Key dari suatu tabel, sedang jika yang kita gunakan param enum SchemaOnly maka query yang dihasilkan hanya berisi Informasi Kolom saja. uppss kayaknya cukup neh seputar teorinya, lebih lanjutnya tentang ExecuteReader dan commandBehaviour bisa langsung merujuk ke MSDN-nya.

Berikutnya kita akan mencoba mengimplementasikan  sedikit teori diatas kedunia nyata(tentunya sesuia dengan tema kita diatas).

1. Buatlah sebuah project baru(Windows Application) dari VS.Net 2005 atau yang lainnya
2. Pada Form1 tambahkan komponen Berikut ini, dan atur letaknya sesui selera anda :)  :
1. Button, Name:Button1
2. GridView, Name:Grid
3.  Tambahkan sebuah modul, beri nama modGlobal atau suka2 anda, modul ini berisi procedure dan function yang digunakan pada program utama, terdiri dari function GetSQLDataType(DataTypeNum) yang berfungsi untuk mendapatkan nama type data provider(dalam hal ini SLQ Server), karena pada schema yang tersedia hanya kode nomor dari Type Data Providernya saja, kemudian SetGridColText dan SetGridColCheckBox yang berfungsi untuk mensetting Column text dan Ceheck Box pada grid.. <* lumayan neh bonus tulisan, gw nyarinya lumayan susah neh, gw sharing gratis tiss, mudah2an ada yang berminat.. *>. kopikan kode program berikut ini pada modul yang anda buat:

Public Function GetSQLDataType(ByVal TheDataTypeNum As Integer) As String
        Dim MyDTName As String = ""
        Select Case TheDataTypeNum
            Case 20 : MyDTName = "BigInt"
            Case 128 : MyDTName = "Binary"
            Case 11 : MyDTName = "Bit"
            Case 129 : MyDTName = "Char"
            Case 135 : MyDTName = "DateTime"
            Case 131 : MyDTName = "Decimal"
            Case 5 : MyDTName = "Float"
            Case 205 : MyDTName = "Image"
            Case 3 : MyDTName = "Int"
            Case 6 : MyDTName = "Money"
            Case 130 : MyDTName = "Nchar"
            Case 203 : MyDTName = "Ntext"
            Case 131 : MyDTName = "Numeric"
            Case 202 : MyDTName = "Nvarchar"
            Case 4 : MyDTName = "Real"
            Case 135 : MyDTName = "SmallDateTime"
            Case 2 : MyDTName = "SmallInt"
            Case 6 : MyDTName = "SmallMoney"
            Case 12 : MyDTName = "SQLVariant"
            Case 201 : MyDTName = "Text"
            Case 128 : MyDTName = "TimeStamp"
            Case 17 : MyDTName = "TinyInt"
            Case 72 : MyDTName = "UniqueIdentifier"
            Case 204 : MyDTName = "VarBinary"
            Case 200 : MyDTName = "Varchar"
        End Select
        Return MyDTName
    End Function 

Public Function SettGridColText(ByVal hdrText As String, ByVal hdrName As String, _

ByVal lbrCol As Integer , ByVal TheReadO As Boolean, ByVal AuMode As DataGridViewAutoSizeColumnMode, _

Optional ByVal VisibleCol As Boolean = True, Optional ByVal ContAllignment As DataGridViewContentAlignment = DataGridViewContentAlignment.BottomCenter) As DataGridViewTextBoxColumn

        Dim TheCol As New DataGridViewTextBoxColumn

        With TheCol

            .HeaderText = hdrText

            .Name = hdrName

            .Width = lbrCol

            .ReadOnly = TheReadO

            .AutoSizeMode = AuMode

            .Visible = VisibleCol

            .DefaultCellStyle.Alignment = ContAllignment 'Contain Alligment

        End With

        Return TheCol

      End Function

      Public Function SettGridColCehckBox(ByVal hdrText As String, ByVal hdrName As String, _

ByVal lbrCol As Integer , ByVal TheReadO As Boolean, _

ByVal AuMode As DataGridViewAutoSizeColumnMode, _

Optional ByVal VisibleCol As Boolean = True, _

Optional ByVal ContAllignment As DataGridViewContentAlignment _

= DataGridViewContentAlignment.BottomCenter) As DataGridViewCheckBoxColumn

        Dim TheCol As New DataGridViewCheckBoxColumn

        With TheCol

            .HeaderText = hdrText

            .Name = hdrName

            .Width = lbrCol

            .ReadOnly = TheReadO

            .AutoSizeMode = AuMode

            .Visible = VisibleCol

            .DefaultCellStyle.Alignment = ContAllignment 'Contain Alligment

        End With

        Return TheCol

End Function

4. Kemudian pada form1 tambahkan kode program berikut ini

Private Sub SettGridView()

        'No

        Dim ColNo As New DataGridViewTextBoxColumn

        ColNo = SettGridColText("No", "hdrNo", 25, True, DataGridViewAutoSizeColumnMode.None)

        'Tax Name

        Dim ColTaxName As New DataGridViewTextBoxColumn

        ColTaxName = SettGridColText("Name", "hdrName", 150, True,_

      DataGridViewAutoSizeColumnMode.None, True,_

      DataGridViewContentAlignment.MiddleLeft)

        'Prosentase

        Dim ColProvDataType As New DataGridViewTextBoxColumn

        ColProvDataType = SettGridColText("Prov DType", "hdrProvDataType", 80, True,_

DataGridViewAutoSizeColumnMode.None, True, _

DataGridViewContentAlignment.MiddleLeft)

        Dim ColSysDataType As New DataGridViewTextBoxColumn

        ColSysDataType = SettGridColText("Sys DType", "hdrSysDataType", 80, True,_

DataGridViewAutoSizeColumnMode.None, True,_

DataGridViewContentAlignment.MiddleLeft)

        Dim ColLength As New DataGridViewTextBoxColumn

        ColLength = SettGridColText("Length", "hdrLength", 50, True,_

DataGridViewAutoSizeColumnMode.None, True,_

DataGridViewContentAlignment.MiddleRight)

        Dim ColPKey As New DataGridViewCheckBoxColumn

        ColPKey = SettGridColCehckBox("PK", "hdrPkey", 40, False,_

 DataGridViewAutoSizeColumnMode.None, True,_

DataGridViewContentAlignment.MiddleCenter)

        Dim ColSelect As New DataGridViewCheckBoxColumn

        ColSelect = SettGridColCehckBox("Select", "hdrSelect", 40, False,_

 DataGridViewAutoSizeColumnMode.None, True,_

 DataGridViewContentAlignment.MiddleCenter)

        With grid

            .Columns.Clear()

            .AllowUserToAddRows = False

            .AllowUserToDeleteRows = False

            .Columns.Add(ColNo)

            .Columns.Add(ColTaxName)

            .Columns.Add(ColProvDataType)

            .Columns.Add(ColSysDataType)

            .Columns.Add(ColLength)

            .Columns.Add(ColPKey)

            .Columns.Add(ColSelect)

        End With

    End Sub

    Private Sub getSchema()

        Dim cn As New OleDbConnection()

        Dim cmd As New OleDbCommand()

        Dim schemaTable As DataTable

        Dim myReader As OleDbDataReader

        Dim myField As DataRow

        Dim myProperty As DataColumn

        'Open a connection to the SQL Server Northwind database.

        cn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVER_ANDA;User ID=USER_SA_ANDA;Password=PASSWORD_SA_AND;Initial Catalog=Northwind"

        cn.Open()

        'Retrieve records from the Employees table into a DataReader.

        cmd.Connection = cn

        cmd.CommandText = "SELECT * FROM Employee"

        myReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)

        'Retrieve column schema into a DataTable.

        schemaTable = myReader.GetSchemaTable()

        Dim iRow As Integer = 0

        'For each field in the table...

        For Each myField In schemaTable.Rows

            'For each property of the field...

            grid.Rows.Add()

            grid.Item("hdrName", iRow).Value = myField.Item(0).ToString()

            For Each myProperty In schemaTable.Columns

                If myProperty.ColumnName = "DataType" Then

                    grid.Item("hdrProvDataType", iRow).Value = _

 GetSQLDataType(Val(myField("ProviderType").ToString()))

                    grid.Item("hdrSysDataType", iRow).Value = myField("DataType").ToString()

                    grid.Item("hdrLength", iRow).Value = myField("ColumnSize").ToString()

                End If

            Next

            iRow = iRow + 1

        Next

        myReader.Close()

        cn.Close()

    End Sub

5. Terakhir pada button1 tambahkan kode berikut ini

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles _

                        Button1.Click

        SettGridView()

        getSchema()

    End Sub

6. Now Run programmnya, dan klik button 1 amati apa yg terjadi. Selamat berxperimen dan berimprovisasi, nanti kalo udah bisa kasih tau gw ya..
7. Program lengkap dapat didownload disini
   

FINALLY

Akhir kata semoga bermanfaat.

SUMBER

1.       MSDN Online

2.       http://support.microsoft.com/kb/310108