Microsoft KB Archive/318373

= HOW TO: Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in Visual Basic .NET =

Article ID: 318373

Article Last Modified on 9/3/2003

-

APPLIES TO


 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0

-



This article was previously published under Q318373



For a Microsoft Visual C# .NET version of this article, see 318452.

IN THIS TASK
SUMMARY
 * Description of the Technique
 * Requirements
 * Sample
 * Troubleshooting

REFERENCES



SUMMARY
This step-by-step article describes how to retrieve table and column metadata from Microsoft Excel data sources by using the GetOleDbSchemaTable method with the Microsoft OLE DB Managed Provider and the Microsoft OLE DB Provider for Jet.

The GetOleDbSchemaTable method that is exposed by the System.Data.OleDb class of Microsoft .NET Framework is the .NET successor to the OpenSchema method in earlier versions of Microsoft ActiveX Data Objects (ADO).

back to the top

Description of the Technique
After you connect to an Excel data source by using ADO.NET, you extract a list of table metadata by using GetOleDbSchemaTable, and then use the same method with different arguments to obtain column metadata for the selected table. You can also use a DataGridTableStyle object to lay out and format your query results in a data grid.

back to the top

Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you will need:
 * Visual Studio .NET installed on a compatible Microsoft Windows operating system
 * At least one Microsoft Excel workbook (.xls) file with some rows and columns of data

This article assumes that you have at least basic familiarity with the following topics:
 * Visual Basic .NET
 * ADO.NET data access
 * Excel workbooks and worksheets

back to the top

Sample
 Start Microsoft Visual Studio .NET, and create a new Visual Basic .NET Windows Application project. Add three Button controls and two DataGrid controls to the default form (Form1). Change the Text property of the Button controls to Retrieve Metadata, Format Tables List, and Format Columns List respectively.  Switch to the form's code module, and add the following Imports statements at the top: Imports System.Data Imports System.Data.OleDb Imports System.ComponentModel   Insert the following module-level declarations in the Form class after the &quot;Inherits System.Windows.Forms.Form&quot; line. Adjust the connection string as necessary to point to an Excel workbook file that contains some rows and columns of data. Dim cn As OleDbConnection Dim strCn As String = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=c:\test.xls;Extended Properties=Excel 8.0&quot; Dim dtTables As DataTable Dim WithEvents cm As CurrencyManager Dim dtColumns As DataTable Dim dvColumns As DataView   Insert the following code in the Form class after the &quot;Windows Form Designer generated code&quot; region. This code calls GetOleDbSchemaTable to load the table and columns lists, populates the DataGrid controls, and updates the columns list when the selected table changes. Private Sub Button1_Click(ByVal sender As System.Object, _           ByVal e As System.EventArgs) Handles Button1.Click Call GetTablesList End Sub

Private Sub GetTablesList cn = New OleDbConnection(strCn) cn.Open dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) DataGrid1.DataSource = dtTables DataGrid1.ReadOnly = True cn.Close Call GetColumnsList End Sub

Private Sub GetColumnsList If cm Is Nothing Then cm = CType(Me.BindingContext(dtTables), CurrencyManager) End If       Dim r As Integer = cm.Position Dim strTable As String = dtTables.Rows(r)(&quot;TABLE_NAME&quot;) cn = New OleDbConnection(strCn) cn.Open dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _           New Object {Nothing, Nothing, strTable, Nothing}) dvColumns = New DataView(dtColumns) dvColumns.Sort = &quot;ORDINAL_POSITION&quot; DataGrid2.DataSource = dvColumns DataGrid2.ReadOnly = True cn.Close End Sub

Private Sub cm_PositionChanged(ByVal sender As Object, _           ByVal e As System.EventArgs) Handles cm.PositionChanged Call GetColumnsList End Sub   Insert the following code to lay out and format the Tables DataGrid by using TableStyles. Note the use of PropertyDescriptor to facilitate non-default formatting of the date columns. Private Sub Button2_Click(ByVal sender As System.Object, _           ByVal e As System.EventArgs) Handles Button2.Click Call FormatTablesGrid(dtTables) End Sub

Private Sub FormatTablesGrid(ByVal dt2format As DataTable) Dim gs As DataGridTableStyle = New DataGridTableStyle gs.MappingName = dt2format.TableName

Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn With cs           .MappingName = &quot;TABLE_NAME&quot; .HeaderText = &quot;Table Name&quot; .Width = 75 End With gs.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn With cs           .MappingName = &quot;TABLE_TYPE&quot; .HeaderText = &quot;Table Type&quot; .Width = 75 End With gs.GridColumnStyles.Add(cs)

Dim cm As CurrencyManager = CType(Me.BindingContext(dt2format), CurrencyManager) Dim pd As PropertyDescriptor = cm.GetItemProperties(&quot;DATE_CREATED&quot;)

cs = New DataGridTextBoxColumn(pd, &quot;d&quot;) With cs           .MappingName = &quot;DATE_CREATED&quot; .HeaderText = &quot;Date Created&quot; .Width = 75 End With gs.GridColumnStyles.Add(cs)

cm = CType(Me.BindingContext(dt2format), CurrencyManager) pd = cm.GetItemProperties(&quot;DATE_MODIFIED&quot;)

cs = New DataGridTextBoxColumn(pd, &quot;d&quot;) With cs           .MappingName = &quot;DATE_MODIFIED&quot; .HeaderText = &quot;Date Modified&quot; .Width = 75 End With gs.GridColumnStyles.Add(cs)

DataGrid1.TableStyles.Add(gs) Me.Button2.Enabled = False

End Sub   Insert the following code to lay out and format the Columns DataGrid by using TableStyle: Private Sub Button3_Click(ByVal sender As System.Object, _           ByVal e As System.EventArgs) Handles Button3.Click Call FormatColumnsGrid(dtColumns) End Sub

Private Sub FormatColumnsGrid(ByVal dt2format As DataTable) Dim gs As DataGridTableStyle = New DataGridTableStyle gs.MappingName = dtColumns.TableName

Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn With cs           .MappingName = &quot;COLUMN_NAME&quot; .HeaderText = &quot;Column Name&quot; .Width = 100 End With gs.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn With cs           .MappingName = &quot;ORDINAL_POSITION&quot; .HeaderText = &quot;Ordinal Position&quot; .Width = 100 End With gs.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn With cs           .MappingName = &quot;DATA_TYPE&quot; .HeaderText = &quot;Data Type&quot; .Width = 75 End With gs.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn With cs           .MappingName = &quot;CHARACTER_MAXIMUM_LENGTH&quot; .HeaderText = &quot;Text Length&quot; .Width = 75 End With gs.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn With cs           .MappingName = &quot;NUMERIC_PRECISION&quot; .HeaderText = &quot;Numeric Precision&quot; .Width = 75 End With gs.GridColumnStyles.Add(cs)

DataGrid2.TableStyles.Add(gs) Me.Button3.Enabled = False

End Sub </li> Run the project.</li> Click Retrieve Metadata to fill the Tables list (DataGrid1) with all of the columns of information that are returned for each table in the Excel workbook by GetOleDbSchemaTable. The Columns list (DataGrid2) is filled at the same time with all of the columns of information that are returned for the columns in the first table in the Tables list.</li> Select a different table in the Tables list. The Columns list changes to display the columns from the selected table because of the cm_PositionChanged event handler that you implemented.</li> Click Format Tables List to define and apply a TableMappingStyle to DataGrid1. This makes the column headers more &quot;friendly&quot; and displays only the four columns of useful data from the nine columns that are returned by GetOleDbSchemaTable. These columns are displayed:

TABLE_NAME

TABLE_TYPE

DATE_CREATED

DATE_MODIFIED

These blanks columns are not displayed:

TABLE_CATALOG

TABLE_SCHEMA

TABLE_GUID

DESCRIPTION

TABLE_PROPID

</li> Click Format Columns List to define and to apply a TableMappingStyle to DataGrid2. This makes the column headers more &quot;friendly&quot; and displays only the 5 most useful columns of data from the 28 columns that are returned by GetOleDbSchemaTable.

COLUMN_NAME

ORDINAL_POSITION

DATA_TYPE

MAXIMUM_CHARACTER_LENGTH

NUMERIC_PRECISION

These mostly blank columns are not displayed:

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME (already known)

COLUMN_GUID

COLUMN_PROPID

COLUMN_HASDEFAULT (always false)

COLUMN_DEFAULT

COLUMN_FLAGS

IS_NULLABLE (always true)

TYPE_GUID

CHARACTER_OCTET_LENGTH

NUMERIC_SCALE

DATETIME_PRECISION

CHARACTER_SET_CATALOG

CHARACTER_SET_SCHEMA

CHARACTER_SET_NAME

COLLATION_CATALOG

COLLATION_SCHEMA

COLLATION_NAME

DOMAIN_CATALOG

DOMAIN_SCHEMA

DOMAIN_NAME

DESCRIPTION

</li> Click a different row in DataGrid1 to select a different table. The Columns list is automatically updated. The TableStyles that you previously applied to each DataGrid remain in effect.</li></ol>

back to the top

Troubleshooting
<ul> The column data types that are returned by GetOleDbSchemaTable for an Excel data source are not identical in all cases to the data types that are returned by the OpenSchema method in classic ADO: </li> GetOleDbSchemaTable, like OpenSchema, returns a single column &quot;F1&quot; from an empty Excel worksheet, although in fact no data or column headings are present.</li></ul>

back to the top

<div class="references_section">