C # uses GetOleDbSchemaTable to retrieve schema information (tables, columns, primary keys, and so on)

This article demonstrates how to retrieve database schema information using the getoledbschematable method of the OleDbConnection object in ADO. Schema information in the data source includes the database or directories that can be obtained from the data sources, tables, and views in the database, as well as the constraints that exist. The schema information in the table includes the primary key, column, and AutoNumber fields.

Note that there is no method equivalent to getoledbschematable when using the sqlclient.sqlconnection object. The SQL Server. NET data Provider exhibits back-end schema information through stored procedures and an informational view. For more information about the views and stored procedures that are available through Microsoft SQL Server, see Transact-SQL Reference in the MSDN library.

GetOleDbSchemaTable Methods for OleDbConnection objects

The OLE DB. NET Data Provider presents schema information using the getoledbschematable method of the OleDbConnection object. getoledbschematable Returns a DataTablepopulated with schema information.

The first argument to GetOleDbSchemaTable is the schema parameter, which is an identity of the OleDbSchemaGuid type that specifies the type of schema information to return (such as tables, columns, and primary keys). The second parameter is an array of restricted objects that filter the rows returned in the DataTable schema (for example, you can specify restrictions on the name, type, owner, and/or schema of the table).

OleDbSchemaGuid Members

The oledbschemaguid parameter specifies the type of schema table to be returned by the getoledbschematable method. OleDbSchemaGuid members mainly include:

•	Column
•	FOREIGN key
•	Index
•	Primary key
•	Table
•	View

See the "OleDbSchemaGuid members" Web site in the Reference section for a complete list of member OleDbSchemaGuid .

Limit

The limit is an array of filtered value objects, each of which corresponds to a DataColumnin the result DataTable . The oledbschemaguid parameter determines the appropriate limit. For example, when specifying the OleDbSchemaGuid of a table, the restriction array is as follows:

{table_catalog, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}

To view the available restrictions, click the any OleDbSchemaGuid member in the following Microsoft Web site:

OleDbSchemaGuid Members
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/ frlrfsystemdataoledboledbschemaguidmemberstopic.asp (http://msdn.microsoft.com/library/default.asp?url =/library/en-us/cpref/html/frlrfsystemdataoledboledbschemaguidmemberstopic.asp)

When passing the value of a restricted array, use the null keyword for Visual C #. NET for array elements that do not contain values. For example, if you want to retrieve the schema for a table, use oledbschemaguid.tables. However, if a table is specified, aliases, synonyms, views, and other related objects are also returned. Therefore, if you want to filter out all other objects except the table, use the table limit for Table_type. You can use nullfor Table_catalog, TABLE_SCHEMA, and table_name because you do not filter these objects:

SchemaTable = CN. GetOleDbSchemaTable (oledbschemaguid.tables,new object[] {null, NULL, NULL, "TABLE"});

The Returned data table

Each object that conforms to the OleDbSchemaGuid type and restriction rules corresponds to a row in the DataTable returned by the getoledbschematable method. Each restriction column corresponds to a column of the DataTable , followed by additional schema information based on the OleDbSchemaGuid field.

For example, when you use the following code, each row of the returned DataTable is a database table:

SchemaTable = CN. GetOleDbSchemaTable (oledbschemaguid.tables,new object[] {null, NULL, NULL, "TABLE"});

Each column returned in the DataTable is a restricted column (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE), followed by Table_guid, DESCRIPTION, Additional schema columns for Table_propid, date_created, and date_modified.

To get a list of column names (that is, field descriptors, such as Table_catalog, TABLE_SCHEMA, and table_name), you can use the position order of the columns. Note that the element subscript for the Columns array starts at 0:

for (int i = 0; i < SchemaTable.Columns.Count; i++) {Console.WriteLine (Schematable.columns[i]. ToString ());}

To get the values for each column (that is, the actual table names, such as Categories, Customers, and Employees), you can use the position order of the ItemArray for that row. Note that the element subscript for the ItemArray array starts at 0:

for (int i = 0; i < SchemaTable.Rows.Count; i++) {Console.WriteLine (Schematable.rows[i]. ITEMARRAY[2]. ToString ());}

Back to the top

To create an example that lists the tables in the database

The following example lists the tables in the SQL Server Northwind database.

oledbschemaguid.tables will return tables (including views) that can be accessed by a particular login. If you specify an array of objects {null, NULL, NULL, "TABLE"}, your filter results include only the table_type of the table. The table name (table_name) is then given in each row in the returned schema table.

1.	Start Visual Studio. NET.
2.	Create a new Visual C # console Application project. By default, Class1.cs is added to the project.
3.	Open the Class1 Code window. Paste the following code at the top of the code window, above the namespace declaration: Using System.Data; Using System.Data.OleDb;
4.	In the code window, paste the following code into theMainIn the function: OleDbConnection cn = new OleDbConnection (); DataTable schematable; Connect to the Northwind database in SQL Server. Being sure to the use of a account, the have permission to list tables. cn. ConnectionString = "Provider=sqloledb;data source=server; User Id=sa; Password=password;initial Catalog=northwind "; cn. Open (); Retrieve schema information about tables. Because tables include tables, views, and other objects,//restrict to just TABLE in the Object array of rest Rictions. SchemaTable = CN. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] {null, NULL, NULL, "TABLE"}); List the table name from each row in the schema table. for (int i = 0; i < SchemaTable.Rows.Count; i++) {Console.WriteLine (Schematable.rows[i]. ITEMARRAY[2]. ToString ()); }//explicitly Close-don ' t wait on garbage collection. CN.close (); Pause Console.ReadLine ();
5.	Modify ConnectionStringto connect to your SQL Server computer by using an account that has list permissions in the Northwind database.
6.	Press the F5 key to compile and run the project. You will notice that the table is listed in the console window.
7.	Press the ENTER key to end the console application and back to the integrated development environment (IDE).

Back to the top

Example of creating a schema that retrieves a table

The following example lists schema information for the Employees table in the SQL Server Northwind database.

oledbschemaguid.tables will return tables (including views) that can be accessed by a particular login. If you specify an array of objects {null, NULL, "Employees", "Table"}, your filter results only include tables named Employees. The schema information for the returned schema table is then listed.

1.	Create a new Visual C # console Application project. By default, Class1.cs is added to the project.
2.	Open the Class1 Code window. Paste the following code at the top of the code window, above the namespace declaration: Using System.Data; Using System.Data.OleDb;
3.	In the code window, paste the following code into theMainIn the function: OleDbConnection cn = new OleDbConnection (); DataTable schematable; Connect to the Northwind database in SQL Server. Being sure to the use of a account, the have permission to retrieve table schema. cn. ConnectionString = "Provider=sqloledb;data source=server; User Id=sa; Password=password;initial Catalog=northwind "; cn. Open (); Retrieve schema information about the Employees table. SchemaTable = CN. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] {null, NULL, "Employees", "TABLE"}); List the schema info for the Employees table//in the format Field Descriptor:field Value. for (int i = 0; i < SchemaTable.Columns.Count; i++) {Console.WriteLine (Schematable.columns[i]. ToString () + ":" + schematable.rows[0][i]. ToString ()); }//explicitly Close-don ' t wait on garbage collection. cn. Close (); Pause Console.ReadLine ();
4.	Modify ConnectionStringto connect to your SQL Server computer using an account that has permissions to retrieve the Employees table schema.
5.	Press the F5 key to compile and run the project. You will notice that the table is listed in the console window.
6.	Press the ENTER key to end the console application and return to the IDE.

Back to the top

To create an example that lists the columns in a table

The following example lists the column names in the Employees table in the SQL Server Northwind database.

OleDbSchemaGuid.Columns will return the columns in the tables and views that can be accessed by a particular login. If you specify an array of objects {null, NULL, "Employees", null}, your filter results include only the columns in the Employees table.

1.	Create a new Visual C # console Application project. By default, Class1.cs is added to the project.
2.	Open the Class1 Code window. Paste the following code at the top of the code window, above the namespace declaration: Using System.Data; Using System.Data.OleDb;
3.	In the code window, paste the following code into theMainIn the function: OleDbConnection cn = new OleDbConnection (); DataTable schematable; Connect to the Northwind database in SQL Server. Be sure to use a account with permission to list the columns in the Employees table. cn. ConnectionString = "Provider=sqloledb;data source=server; User Id=sa; Password=password;initial Catalog=northwind "; cn. Open (); Retrieve schema information about columns. Restrict to just the Employees TABLE. SchemaTable = CN. GetOleDbSchemaTable (OleDbSchemaGuid.Columns, new object[] {null, NULL, "Employees", null}); List the column name from each row in the schema table. for (int i = 0; i < SchemaTable.Rows.Count; i++) {Console.WriteLine (Schematable.rows[i]. ITEMARRAY[3]. ToString ()); }//explicitly Close-don ' t wait on garbage collection. cn. Close (); Pause Console.ReadLine();
4.	Modify ConnectionStringto connect to your SQL Server computer by using an account that has permissions to list the columns in the Employees table.
5.	Press the F5 key to compile and run the project. You will notice that the columns in the Employees table are listed in the console window.
6.	Press the ENTER key to end the console application and return to the IDE.

Back to the top

Create an example that lists the primary keys in a table

The following example lists the Employees table for the SQL Server Northwind database and the primary key in the Employee table of the SQL Server Pubs database.

OleDbSchemaGuid.Primary_Keys will return the primary key in the directory that can be accessed by a particular login. In this example,OleDbConnection connects to SQL Server instead of connecting to a specific SQL Server database:

cn. ConnectionString = "Provider=sqloledb;data source=server; User Id=sa; Password=password; "

Because the Northwind or Pubs database will be specified in the table_catalog of the restricted array. This code specifies the table owner "dbo" as the table_schema limit. Additionally, the code specifies the table name for the table_name restriction.

To get the primary key for the Employees table in the Northwind database, you can use {"Northwind", "dbo", "Employees"} Object array:

SchemaTable = CN. GetOleDbSchemaTable (oledbschemaguid.primary_keys,new object[] {"Northwind", "dbo", "Employees"});

To get the primary key for the Employee table in the pubs database, you can use {"Pubs", "dbo", "Employee"} object arrays:

SchemaTable = CN. GetOleDbSchemaTable (oledbschemaguid.primary_keys,new object[] {"Pubs", "dbo", "Employee"});

To create an example, you can follow these steps:

1.	Create a new Visual C # console Application project. By default, Class1.cs is added to the project.
2.	Open the Class1 Code window. Paste the following code at the top of the code window, above the namespace declaration: Using System.Data; Using System.Data.OleDb;
3.	In the code window, paste the following code into theMainIn the function: OleDbConnection cn = new OleDbConnection (); DataTable schematable; Connect to SQL Server. Be sure to use a account for permissions to list primary keys//in both the Northwind and Pubs database S. CN. ConnectionString = "Provider=sqloledb;data source=server; User Id=sa; Password=password; "; cn. Open (); Retrieve schema information about primary keys. Restrict to just the Employees TABLE in the Northwind CATALOG. SchemaTable = CN. GetOleDbSchemaTable (OleDbSchemaGuid.Primary_Keys, new object[] {"Northwind", "dbo", "Employees"}); List the primary key for the first row in the schema table. The first three items in the ItemArray in the row is catalog, schema, and table. The fourth item is the primary key. Console.WriteLine (Schematable.rows[0]. ITEMARRAY[3]. ToString ()); Retrieve primary key for the EmplOyee TABLE in the Pubs CATALOG. SchemaTable = CN. GetOleDbSchemaTable (OleDbSchemaGuid.Primary_Keys, new object[] {"Pubs", "dbo", "Employee"}); List the primary key for the first row in the schema table. Console.WriteLine (Schematable.rows[0]. ITEMARRAY[3]. ToString ()); Explicitly close-don ' t wait on garbage collection. cn. Close (); Pause Console.ReadLine ();
4.	Modify the ConnectionStringto connect to your SQL Server computer by using an account that has sufficient permissions to list the primary key.
5.	Press the F5 key to compile and run the project. You will notice that the primary key for the Employee table of the Northwind database and the Pubs database is listed in the console window.
6.	Press the ENTER key to end the console application and return to the IDE.

Source: HTTP://SUPPORT.MICROSOFT.COM/KB/309681/ZH-CN

C # uses GetOleDbSchemaTable to retrieve schema information (tables, columns, primary keys, and so on)