Retrieve dataset schema from database at run time

  Retrieve dataset schema from database at run time

This approach is the slowest and not recommended for production-scale products, because retrieving schema from database takes a lot of time.

You can either retrieve the schema from database separately or together with data. To retrieve schema separately, callDataAdapter.FillSchema. It can be done either before or after Fill. When you call FillSchema, you have a choice to use the original table and column names in the database as the table and column names of the dataset, or to use different names at your choice by providing a mapping of the table and column names. The first parameter ofFillSchema is the dataset, and the second is an enumeration indicating whether you want to use original namesin the database (SchemaType.Source), or the TableMappings and ColumnMappings that you have added into the dataset (SchemaType.Map). See section Mapping Table Names in DataAdapter.Fill and Mapping column Names in DataAdapter.Fill for details.

To fill schema together with data, set DataAdapter's MissingSchemaAction property to enumerationMissingSchemaAction.AddWithKey before calling DataAdapter.Fill.

In the following example, the code to retrieve schema from database into dataset has been commented out, so the result will contain no schema.

Dim cn As OleDbConnection = New OleDbConnection("File Name=DataLink.udl")

Dim cmd As OleDbCommand = cn.CreateCommand()

cmd.CommandText = "Select * from Customers where Country = 'Germany'"

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

'da.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim ds As DataSet = New DataSet()

'da.FillSchema(ds, SchemaType.Source)

da.Fill(ds)

'da.FillSchema(ds, SchemaType.Source)

Dim col As DataColumn

Dim constraints As String

For Each col In ds.Tables(0).Columns

constraints = "Col Name: " & col.ColumnName & ". AutoIncrement: " & col.AutoIncrement &

" MaxLength: " & col.MaxLength & " AllowDBNull: " & col.AllowDBNull

MessageBox.Show(constraints)

Next

MessageBox.Show("Table Customers's primary key: " & ds.Tables(0).PrimaryKey(0).ColumnName)