Treeless Software VB example gets Schema information from an Access DB Table

This VB Subroutine, gets Schema information (Column Name, Type, Size) from an Access DB Table.

Private m_connDB As OleDbConnection = Nothing   ' Current DB Connection

Private m_sSchemaTable(1, 2) As String          ' Table Schema Info

Private Enum eSchemaFields As Integer
        ColumnName = 0
        ColumnSize = 1
        DataType = 2
    End Enum

''' <summary>
    ''' Get the Field Definition for Record
    ''' </summary>
    ''' <param name="_TableName">Name of Table for Schema</param>
    ''' <param name="_SchemaTable">String Array to Save data to</param>
    ''' <remarks>The Schema Table must be ByRef NOT ByVal</remarks>
    Private Sub GetSchema(ByVal _TableName As String, _
                          ByRef _SchemaTable(,) As String)
        Dim sSQL As String = "SELECT * FROM " + _TableName + ";"
        Dim cmd As New OleDbCommand
        cmd.Connection = m_connDB
        cmd.CommandText = sSQL
        '*********************************************************
        ' Get the column and primary key information for our table
        '*********************************************************
        Dim rdr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
        Dim dt As DataTable = rdr.GetSchemaTable
        rdr.Close()
        ReDim _SchemaTable(dt.Rows.Count - 1, 2)
        Dim nIndex As Integer = 0
        'For Each dc As DataColumn In dt.Columns
        '    Debug.Print(dc.ColumnName)
        'Next
        '**************************************************************
        ' Each DataRow is a column in our table, save what info we need
        '**************************************************************
        For Each dr As DataRow In dt.Rows
            _SchemaTable(nIndex, eSchemaFields.ColumnName) = dr.Item("ColumnName").ToString
            _SchemaTable(nIndex, eSchemaFields.ColumnSize) = dr.Item("ColumnSize").ToString
            _SchemaTable(nIndex, eSchemaFields.DataType) = dr.Item("DataType").ToString
            nIndex += 1
        Next
    End Sub

Treeless Software

PC and Web Software Development.

Latest News

New VB.Net and SQL Examples

New Website Launched

July 7, 2011

Useful Links

Search