GetSchema VB Subroutine

by Bill Dowling 30. July 2010 13:56
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


.Net Coding Examples | Access DB

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List