Use this step-by-step guide to retrieve table and column meta data from a Microsoft Excel data source by using the GetOleDbSchemaTable method with the Microsoft OLE DB Managed Provider and the Microsoft OLE DB Provider for Jet.
The GetOleDbSchemaTable method exposed by the System.Data.OleDb class of the Microsoft .NET Framework is the .NET successor to the OpenSchema method in earlier versions of ActiveX Data Objects (ADO).
After you connect to an Excel data source by using Microsoft ADO.NET, extract a list of table meta data by using the GetOleDbSchemaTable method. Next, use the same method with different arguments to obtain column meta data for the selected table. You can also use the DataGridTableStyle method to lay out and format query results in a data grid.
Start Visual Studio .NET and create a new Visual C# Windows Forms Application project.
Add three Button controls and two DataGrid controls to Form1. Change the Text property of the Button controls to name the buttons Retrieve Meta Data, Format Tables List, and Format Columns List, respectively.
Switch to the code module for the form and add the following statements before any other code:
using System.ComponentModel;using System.Data;using System.Data.OleDb;
In the Form class, insert the following module-level declarations. Have the connection string point to an Excel workbook file that contains some rows and columns of data.
In the Form class, after the Windows Form Designer generated code section, insert the following code. This code calls GetOleDbSchemaTable to load the table and column lists, populates the DataGrids, and updates the Columns list when the selected table changes.
The Tables list (DataGrid1) is filled with all the columns of information returned for each table in the Excel workbook by the GetOleDbSchemaTable method. The Columns list (DataGrid2) fills at the same time with all the columns of information returned for the columns in the first table in the Tables list.
From the Tables list, select a different table.
The Columns list displays the columns from the selected table as a result of the cm_PositionChanged event handler.
Click Format Tables List (Button2).This defines and applies a TableMappingStyle to DataGrid1, making the column headers more friendly and displaying only the four columns of useful data
TABLE_NAME TABLE_TYPE DATE_CREATED DATE_MODIFIED
rather than the nine columns returned by the GetOleDbSchemaTable method.
Click Format Columns List (Button3). This defines and applies a TableMappingStyle to DataGrid2, making the column headers more friendly and displaying only the five most useful columns of data COLUMN_NAME ORDINAL_POSITION DATA_TYPE MAXIMUM_CHARACTER_LENGTH NUMERIC_PRECISIONfrom the 28 columns returned by the GetOleDbSchemaTable method.