C # Use getoledbschematable to retrieve schema information (tables, columns, primary keys, etc.)-link http://hi.baidu.com/useforprograms/blog/item/b2627

Document directory

• Getoledbschematable method of oledbconnection object
• Example of creating a list of tables in a database
• Example of creating a retrieval table Architecture
• Example of creating a column listing a table
• Example of creating a primary key listing table

This article demonstrates how to use ADO. net OledbconnectionObject GetoledbschematableMethod to retrieve database architecture information. The schema information in the data source includes the directories and existing constraints obtained from the database or data sources, tables, and views in the database. The schema information in the table includes the primary key, column, and automatic number fields.

Note:Sqlclient. sqlconnectionThe object is notGetoledbschematableEquivalent method. The SQL Server. NET data provider displays the backend architecture information through the stored procedure and information view. For more information about views and stored procedures that can be obtained through Microsoft SQL Server, see the transact-SQL reference in the msdn library.
Getoledbschematable method of oledbconnection object

Use the ole db. NET data providerOledbconnectionObjectGetoledbschematableMethod to display the architecture information.GetoledbschematableReturnsDatatable.

GetoledbschematableThe first parameter is the architecture parameter, which isOledbschemaguidSpecifies the type of schema information to be returned (such as tables, columns, and primary keys ). The second parameter is an array of restricted objects.DatatableThe Rows returned in the schema are filtered (for example, you can specify the table name, type, owner, and/or schema restrictions ).

Oledbschemaguid Member

OledbschemaguidParameter specifiedGetoledbschematableThe type of the schema table to be returned.OledbschemaguidMembers include:

•	Column
•	Foreign key
•	Index
•	Primary Key
•	Table
•	View

RelatedOledbschemaguidFor a complete list of members, seeReferencePart of the "oledbschemaguid members" web site.

Restrictions

The limit is an array of filtered value objects. Each element corresponds to the result.DatatableOneDatacolumn.OledbschemaguidParameters determine the corresponding limits. For exampleOledbschemaguidThe limit array is as follows:

{Table_catalog, table_schema, table_name, table_type}

To view available limits, click any of the following Microsoft Web SitesOledbschemaguidMember:

Oledbschemaguid Member
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 limit array value, use the Visual C #. netNullKeyword. For example, if you want to retrieve the structure of a table, useOledbschemaguid. Tables. However, if a table is specified, aliases, synonyms, views, and other related objects are returned. Therefore, if you want to filter out all objects except the table, use table restrictions on table_type. You can use table_catalog, table_schema, and table_nameNullBecause you do not filter these objects:

schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new Object[] {null, null, null, "TABLE"});

Returned data table

EachOledbschemaguidThe object of the type and restriction rule correspondsGetoledbschematableMethod returnDatatable. Each limit column correspondsDatatableIs followed by a column based onOledbschemaguidOther Architecture Information of the field.

For example, if you use the following codeDatatableEach row of is a database table:

schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new Object[] {null, null, null, "TABLE"});

DatatableEach column returned in is a restricted column (table_catalog, table_schema, table_name, table_type) followed by other schema columns table_guid, description, table_propid, date_created, and date_modified.

To obtain a list of column names (that is, field descriptors, such as table_catalog, table_schema, and table_name), you can use the column location sequence. Note:ColumnsThe element subscript of the array starts from 0:

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

To obtain the values of each column (that is, the actual table name, such as categories, MERS MERs, and employees), you can useItemarray. Note:ItemarrayThe element subscript of the array starts from 0:

for (int i = 0; i < schemaTable.Rows.Count; i++) {Console.WriteLine(schemaTable.Rows[i].ItemArray[2].ToString());}

Back to Top

Example of creating a list of tables in a database

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

Oledbschemaguid. TablesThe tables (including views) that can be accessed by specific logins are returned ). If you specify the object Array {null, "table"}, your filter results only include table_type of table. Then, the table name (table_name) is listed in each row of the returned schema table ).

1.	Start Visual Studio. NET.
2.	Create a Visual C # console application project. By default, class1.cs is added to the project.
3.	Open the class1 code window. Paste the following code to the top of the code window, located inNamespaceDeclaration above: using System.Data; using System.Data.OleDb;
4.	In the code window, paste the following codeMainFunction: OleDbConnection cn = new OleDbConnection(); DataTable schemaTable; //Connect to the Northwind database in SQL Server. //Be sure to use an account that has 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 restrictions. 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.	ModifyConnectionstringTo connect to your SQL Server computer using an account with list permissions in the northwind database.
6.	Press F5 to compile and run the project. You will notice that the table is already listed in the console window.
7.	Press enter to end the console application and return to the integrated development environment (IDE ).

Back to Top

Example of creating a retrieval table Architecture

The following example lists the Architecture Information of the Employees table in the SQL Server northwind database.

Oledbschemaguid. TablesThe tables (including views) that can be accessed by specific logins are returned ). If the specified object Array {null, null, "employees", "table"} is specified, your filtering results only include tables named employees. Then list the Architecture Information of the returned architecture table.

1.	Create a Visual C # console application project. By default, class1.cs is added to the project.
2.	Open the class1 code window. Paste the following code to the top of the code window, located inNamespaceDeclaration above: using System.Data; using System.Data.OleDb;
3.	In the code window, paste the following codeMainFunction: OleDbConnection cn = new OleDbConnection(); DataTable schemaTable; //Connect to the Northwind database in SQL Server. //Be sure to use an account that has 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.	ModifyConnectionstringTo connect to your SQL Server computer using an account with permissions to retrieve the Employees table schema.
5.	Press F5 to compile and run the project. You will notice that the table is already listed in the console window.
6.	Press enter to end the console application and return to IDE.

Back to Top

Example of creating a column listing a table

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

Oledbschemaguid. ColumnsColumns in tables and views that can be accessed by specific logon are returned. If you specify the object Array {null, null, "employees", null}, your filter results only include columns in the Employees table.

1.	Create a Visual C # console application project. By default, class1.cs is added to the project.
2.	Open the class1 code window. Paste the following code to the top of the code window, located inNamespaceDeclaration above: using System.Data; using System.Data.OleDb;
3.	In the code window, paste the following codeMainFunction: OleDbConnection cn = new OleDbConnection(); DataTable schemaTable; //Connect to the Northwind database in SQL Server. //Be sure to use an account that has 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.	ModifyConnectionstringTo connect to your SQL Server computer using an account with permissions to list columns in the Employees table.
5.	Press F5 to compile and run the project. You will notice that the columns in the Employees table are already listed in the console window.
6.	Press enter to end the console application and return to IDE.

Back to Top

Example of creating a primary key listing table

The following example lists the primary keys in the Employees table of the SQL Server northwind database and the employee table of the SQL Server pubs database.

Oledbschemaguid. primary_keysThe primary keys in the directories that can be accessed by specific logins are returned. In this example,OledbconnectionConnect to SQL Server instead of a specific SQL Server database:

cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=sa;Password=password;"

Because the rose 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 restriction. In addition, the Code also specifies the table name restricted by table_name.

To obtain the primary key of the Employees table in the Rose database, you can use the {"northwind", "DBO", "employees"} object array:

schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,new Object[] {"Northwind", "dbo", "Employees"});

To obtain the primary key of the employee table in the pubs database, you can use the {"pubs", "DBO", "employee"} object array:

schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,new Object[] {"Pubs", "dbo", "Employee"});

To create an example, follow these steps:

1.	Create a Visual C # console application project. By default, class1.cs is added to the project.
2.	Open the class1 code window. Paste the following code to the top of the code window, located inNamespaceDeclaration above: using System.Data; using System.Data.OleDb;
3.	In the code window, paste the following codeMainFunction: OleDbConnection cn = new OleDbConnection(); DataTable schemaTable; //Connect to SQL Server. //Be sure to use an account that has permissions to list primary keys //in both the Northwind and Pubs databases. 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 are 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.	ModifyConnectionstringTo connect to your SQL Server computer with an account with sufficient permissions to list primary keys.
5.	Press F5 to compile and run the project. You will notice that the primary keys of the employee tables of the rose and pubs databases are listed in the console window.
6.	Press enter to end the console application and return to IDE.