DbSchemaInfo Source Code

This code was downloaded from MOBZystems, Home of Tools. No license, use at will. And at your own risk!

''' <summary>
''' DbSchemaInfo. Read schema information from OleDbConnection objects
''' </summary>
Public Class DbSchemaInfo
  ''' <summary>
  ''' DbSchemaTableInfo. Contains information about a table,
  ''' i.e. a table name and a list of column names
  ''' </summary>
  Public Class DbSchemaTableInfo
    ''' <summary>
    ''' The name of this table
    ''' </summary>
    Public TableName As String
    ''' <summary>
    ''' The column names of this table
    ''' </summary>
    Public ColumnNames() As String
  
    ''' <summary>
    ''' Fill this DbSchemaTableInfo with information
    ''' about the specified table using the specified
    ''' OleDbConnection
    ''' </summary>
    Public Sub New(ByVal Connection As OleDb.OleDbConnection, ByVal TableName As String)
      Me.TableName = TableName
  
      ' Get schema information about the columns of this table
      Dim ColumnDataTable As DataTable = Connection.GetSchema( _
        OleDb.OleDbMetaDataCollectionNames.Columns, _
        New String() {Nothing, Nothing, TableName, Nothing} _
      )
      ' Declare the Tables array to size
      ReDim ColumnNames(0 To ColumnDataTable.Rows.Count - 1)
      ' Set column names for each column
      For Row As Integer = 0 To ColumnDataTable.Rows.Count - 1
        Dim ColumnName As String = CStr(ColumnDataTable.Rows(Row).Item("COLUMN_NAME"))
        ColumnNames(Row) = ColumnName
      Next
    End Sub
  End Class
  
  ''' <summary>
  ''' The list of tables associated with the connection
  ''' </summary>
  Public Tables() As DbSchemaTableInfo
  
  ''' <summary>
  ''' Create a DbSchemaInfo object with information from an OleDbonnection
  ''' </summary>
  Public Sub New(ByVal Connection As OleDb.OleDbConnection)
    FillFromConnection(Connection)
  End Sub
  
  ''' <summary>
  ''' Create a DbSchemaInfo object with information from 
  ''' a connection string for an OleDbonnection
  ''' </summary>
  Public Sub New(ByVal ConnectionString As String)
    Using Connection As New OleDb.OleDbConnection(ConnectionString)
      Connection.Open()
      FillFromConnection(Connection)
      Connection.Close()
    End Using
  End Sub
  
  ''' <summary>
  ''' Fill the information in this object from the specified connection
  ''' </summary>
  ''' <param name="Connection"></param>
  Public Sub FillFromConnection(ByVal Connection As OleDb.OleDbConnection)
    ' Get the data table containing table information
    Dim TableDataTable As DataTable = Connection.GetSchema( _
      OleDb.OleDbMetaDataCollectionNames.Tables, _
      New String() {Nothing, Nothing, Nothing, "Table"} _
    )
  
    ' Declare the Tables array to size
    ReDim Tables(0 To TableDataTable.Rows.Count - 1)
  
    ' Create a DbSchemaTableInfo for each table
    For Row As Integer = 0 To TableDataTable.Rows.Count - 1
      ' Get the table name
      Dim TableName As String = CStr(TableDataTable.Rows(Row).Item("TABLE_NAME"))
      ' Get the schema information for this table
      Tables(Row) = New DbSchemaTableInfo(Connection, TableName)
    Next
  End Sub
End Class