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

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

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.

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.

Sample

1. Start Visual Studio .NET and create a new Visual C# Windows Forms Application project.
2. 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.
3. 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;
   					

4. 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;
   					

5. 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();
   }
   								
   					

6. 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;
   
   }
   					

7. 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;
   
   	}
   					

8. Run the project.
9. 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.
10. 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.
11. 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
12. 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
13. 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.

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:
  Collapse this tableExpand this table

  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.

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