Knowledge Base

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 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test.xls;Extended Properties=Excel 8.0";
       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,"TABLE"});
        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("Index #" + i + "\n" +
            "Message: " + myException.Errors[i].Message + "\n" +
            "Native: " +                          
myException.Errors[i].NativeError.ToString() + "\n" +
            "Source: " + myException.Errors[i].Source + "\n" +
            "SQL: " + myException.Errors[i].SQLState + "\n");
        }
    }
            
    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]["TABLE_NAME"].ToString ();
        cn = new OleDbConnection(strCn);
    cn.Open();
    dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]{null,null, strTable, null});
        dvColumns = new DataView(dtColumns);
        dvColumns.Sort = "ORDINAL_POSITION";
        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 = "TABLE_NAME";
    cs.HeaderText = "Table Name";
    cs.Width = 75;
        
    gs.GridColumnStyles.Add(cs);
            
    cs = new DataGridTextBoxColumn();
    cs.MappingName = "TABLE_TYPE";
    cs.HeaderText = "Table Type";
    cs.Width = 75;
        
    gs.GridColumnStyles.Add(cs);

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

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

    cs = new DataGridTextBoxColumn(pd, "d");
        cs.MappingName = "DATE_MODIFIED";
        cs.HeaderText = "Date Modified";
        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 = "COLUMN_NAME";
            cs.HeaderText = "Column Name";
            cs.Width = 100;

        gs.GridColumnStyles.Add(cs);

        cs = new DataGridTextBoxColumn();
            cs.MappingName = "ORDINAL_POSITION";
            cs.HeaderText = "Ordinal Position";
            cs.Width = 100;

            gs.GridColumnStyles.Add(cs);

            cs = new DataGridTextBoxColumn();
            cs.MappingName = "DATA_TYPE";
        cs.HeaderText = "Data Type";
        cs.Width = 75;

        gs.GridColumnStyles.Add(cs);

            cs = new DataGridTextBoxColumn();
            cs.MappingName = "CHARACTER_MAXIMUM_LENGTH";
            cs.HeaderText = "Text Length";
        cs.Width = 75;

        gs.GridColumnStyles.Add(cs);

            cs = new DataGridTextBoxColumn();
            cs.MappingName = "NUMERIC_PRECISION";
            cs.HeaderText = "Numeric Precision";
        cs.Width = 75;

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

            button3.Enabled = false;

    }

Run the project.
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.
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.

The blanks columns that are not displayed are:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_GUID
DESCRIPTION
TABLE_PROPID
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
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.

back to the top

Troubleshooting

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:

Column Type	Classic ADO	ADO.Net (OleDb)
Numeric	5 - adDouble	5 - OleDbType.Double
Currency	6 - adCurrency	6 - OleDbType.Currency
Date/Time	7 - adDate	7 - OleDbType.Date
Boolean	11 - adBoolean	11 - OleDbType.Boolean
Text < 255	202 - adVarWChar	130 - OleDbType.WChar
Memo	203 - adLongVarWChar	130 - OleDbType.WChar

GetOleDbSchemaTable, similar to OpenSchema, returns a single column ("F1") from an empty Excel worksheet when, in fact, no data or column headings are present.

back to the top

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA

back to the top

Additional query words: getoledbschematable excel openschema

Keywords: kbhowtomaster KB318452

Microsoft KB Archive/318452

Contents

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

IN THIS TASK

SUMMARY

Description of the Technique

Requirements

Sample

Troubleshooting

REFERENCES