Microsoft KB Archive/318452

= HOW TO: Retrieve Meta Data from Excel by Using GetOleDbSchemaTable in Visual C# .NET =

Article ID: 318452

Article Last Modified on 9/3/2003

-

APPLIES TO


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

-



This article was previously published under Q318452



For a Microsoft Visual Basic .NET version of this article, see 318373.

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

REFERENCES



SUMMARY
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).

back to the top

Description of the Technique
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.

back to the top

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

This article assumes that you are familiar with the following topics:
 * Microsoft Visual C# .NET.
 * Microsoft ADO.NET data access.
 * Microsoft Excel workbooks and worksheets.

back to the top

Sample
 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. private OleDbConnection cn; private String strCn = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test.xls;Extended Properties=Excel 8.0&quot;; private DataTable dtTables; private CurrencyManager cm; private DataTable dtColumns; private DataView dvColumns;   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. private void button1_Click(object sender, System.EventArgs e) { GetTablesList; } private void GetTablesList {   try {           cn = new OleDbConnection(strCn); cn.Open; dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null,null,null,&quot;TABLE&quot;}); dataGrid1.DataSource = dtTables; dataGrid1.ReadOnly = true; cn.Close; }   catch(System.Data.OleDb.OleDbException myException) {       for (int i=0; i < myException.Errors.Count; i++) {           MessageBox.Show(&quot;Index #&quot; + i + &quot;\n&quot; +            &quot;Message: &quot; + myException.Errors[i].Message + &quot;\n&quot; +            &quot;Native: &quot; +                          myException.Errors[i].NativeError.ToString + &quot;\n&quot; +            &quot;Source: &quot; + myException.Errors[i].Source + &quot;\n&quot; +            &quot;SQL: &quot; + myException.Errors[i].SQLState + &quot;\n&quot;); }   }    GetColumnsList; }

private void GetColumnsList {      if( cm == null) cm = (CurrencyManager)this.BindingContext [dtTables]; cm.PositionChanged += new EventHandler(this.cm_PositionChanged ); int r = cm.Position;

String strTable = dtTables.Rows[r][&quot;TABLE_NAME&quot;].ToString ; cn = new OleDbConnection(strCn); cn.Open; dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]{null,null, strTable, null}); dvColumns = new DataView(dtColumns); dvColumns.Sort = &quot;ORDINAL_POSITION&quot;; dataGrid2.DataSource = dvColumns; dataGrid2.ReadOnly = true; cn.Close; } private void cm_PositionChanged( object sender, System.EventArgs e) { GetColumnsList; }                     Insert the following code to lay out and format the Tables DataGrid by using TableStyles. Note the use of the PropertyDescriptor to facilitate non-default formatting of the date columns. private void button2_Click(object sender, System.EventArgs e) { FormatTablesGrid(dtTables); }

private void FormatTablesGrid(DataTable dt2format) {   DataGridTableStyle gs = new DataGridTableStyle; gs.MappingName = dt2format.TableName; DataGridColumnStyle cs = new DataGridTextBoxColumn; cs.MappingName = &quot;TABLE_NAME&quot;; cs.HeaderText = &quot;Table Name&quot;; cs.Width = 75; gs.GridColumnStyles.Add(cs); cs = new DataGridTextBoxColumn; cs.MappingName = &quot;TABLE_TYPE&quot;; cs.HeaderText = &quot;Table Type&quot;; cs.Width = 75; gs.GridColumnStyles.Add(cs);

CurrencyManager cm = (CurrencyManager)this.BindingContext[dt2format]; PropertyDescriptor pd = cm.GetItemProperties[&quot;DATE_CREATED&quot;]; cs = new DataGridTextBoxColumn(pd, &quot;d&quot;); cs.MappingName = &quot;DATE_CREATED&quot;; cs.HeaderText = &quot;Date Created&quot;; cs.Width = 75; gs.GridColumnStyles.Add(cs);

cm = ( CurrencyManager)this.BindingContext[dt2format]; pd = cm.GetItemProperties[&quot;DATE_MODIFIED&quot;];

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

dataGrid1.TableStyles.Add(gs); button2.Enabled = false;

}                     Insert the following code to lay out and format the Columns DataGrid by using TableStyle: private void button3_Click(object sender, System.EventArgs e)   { FormatColumnsGrid(dtTables); }       private void FormatColumnsGrid(DataTable dt2format) {       DataGridTableStyle gs = new DataGridTableStyle; gs.MappingName = dtColumns.TableName; DataGridColumnStyle cs = new DataGridTextBoxColumn; cs.MappingName = &quot;COLUMN_NAME&quot;; cs.HeaderText = &quot;Column Name&quot;; cs.Width = 100;

gs.GridColumnStyles.Add(cs);

cs = new DataGridTextBoxColumn; cs.MappingName = &quot;ORDINAL_POSITION&quot;; cs.HeaderText = &quot;Ordinal Position&quot;; cs.Width = 100;

gs.GridColumnStyles.Add(cs);

cs = new DataGridTextBoxColumn; cs.MappingName = &quot;DATA_TYPE&quot;; cs.HeaderText = &quot;Data Type&quot;; cs.Width = 75;

gs.GridColumnStyles.Add(cs);

cs = new DataGridTextBoxColumn; cs.MappingName = &quot;CHARACTER_MAXIMUM_LENGTH&quot;; cs.HeaderText = &quot;Text Length&quot;; cs.Width = 75;

gs.GridColumnStyles.Add(cs);

cs = new DataGridTextBoxColumn; cs.MappingName = &quot;NUMERIC_PRECISION&quot;; cs.HeaderText = &quot;Numeric Precision&quot;; cs.Width = 75;

gs.GridColumnStyles.Add(cs); dataGrid2.TableStyles.Add(gs);

button3.Enabled = false;

}                   </li> Run the project.</li> Click Retrieve Meta Data (Button1).

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.</li> 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.</li> 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.

The blanks columns that are not displayed are:

TABLE_CATALOG

TABLE_SCHEMA

TABLE_GUID

DESCRIPTION

TABLE_PROPID

</li> 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_PRECISION from the 28 columns returned by the GetOleDbSchemaTable method.

The columns that are not displayed are:

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 updated automatically. The TableStyles previously applied to each DataGrid remain in effect.</li></ol>

back to the top

Troubleshooting
<ul> The column data types returned by the GetOleDbSchemaTable method for an Excel data source are not the same in all cases to the data types returned by the OpenSchema method in earlier versions of ADO:

</li> GetOleDbSchemaTable, similar to OpenSchema, returns a single column (&quot;F1&quot;) from an empty Excel worksheet when, in fact, no data or column headings are present.</li></ul>

back to the top

<div class="references_section">