Getoledbschematable method of oledbconnection object

Getoledbschematable method of oledbconnection object
 
The ole db. NET data provider uses the getoledbschematable method of the oledbconnection object to display the architecture information. Getoledbschematable returns the datatable filled with schema information.

The first parameter of getoledbschematable is the architecture parameter. It is an oledbschemaguid type identifier that specifies the type of Architecture Information to be returned (such as tables, columns, and primary keys ). The second parameter is a restricted object array that filters the rows returned by the able Schema (for example, you can specify the table name, type, owner, and/or schema constraints ).

 

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

Column
Foreign key
Index
Primary Key
Table
View
For a complete list of oledbschemaguid members, see the "oledbschemaguid members" web site in the reference section.

 

Restrictions
The limit is an array of filter value objects. Each element corresponds to a datacolumn In the result able. The oledbschemaguid parameter determines the corresponding limits. For example, when oledbschemaguid of a specified table is specified, the restriction array is as follows:

{Table_catalog, table_schema, table_name, table_type}
To view available limits, click any oledbschemaguid member in the following Microsoft Web site:

Oledbschemaguid Member
When passing the value of the restricted array, use the null keyword of Visual C #. Net for array elements that do not contain the value. For example, you can use oledbschemaguid. Tables to retrieve the table schema. 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 NULL for table_catalog, table_schema, and table_name because you do not filter these objects:

Schematable = cn. getoledbschematable (oledbschemaguid. Tables,
New object [] {null, "table "});
Returned data table
Each object that meets 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 other 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, "table"}); each column returned by datatable is a restricted column (table_catalog, table_schema, table_name, table_type ), the following are other schema columns of 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 that the element subscript of the columns 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 use the itemarray location sequence of the row. Note that the element subscript of the itemarray array starts from 0:

For (INT I = 0; I <schematable. Rows. Count; I ++ ){
Console. writeline (schematable. Rows [I]. itemarray [2]. tostring ());
}
 
Example of creating a list of tables in a database
The following example lists the tables in the SQL Server northwind database.

Oledbschemaguid. Tables returns tables (including views) that can be accessed by specific logon ). 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 ).

Start Visual Studio. NET.
Create a Visual C # console application project. By default, class1.cs is added to the project.
Open the class1 code window. Paste the following code to the top of the code window, above the namespace declaration:
Using system. Data;
Using system. Data. oledb; In the code window, paste the following code into the main function:
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, "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 (); Modify connectionstring to connect to your SQL Server computer using an account with list permissions in the northwind database.
Press F5 to compile and run the project. You will notice that the table is already listed in the console window.
Press enter to end the console application and return to the integrated development environment (IDE ).

 
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. Tables returns tables (including views) that can be accessed by specific logon ). 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.

Create a Visual C # console application project. By default, class1.cs is added to the project.
Open the class1 code window. Paste the following code to the top of the code window, above the namespace declaration:
Using system. Data;
Using system. Data. oledb; In the code window, paste the following code into the main function:
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 (); Modify connectionstring to connect to your SQL Server computer using an account with permissions to retrieve the Employees table schema.
Press F5 to compile and run the project. You will notice that the table is already listed in the console window.
Press enter to end the console application and return to IDE.

 
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. Columns returns the columns in tables and views that can be accessed by a specific logon. If you specify the object Array {null, null, "employees", null}, your filter results only include columns in the Employees table.

Create a Visual C # console application project. By default, class1.cs is added to the project.
Open the class1 code window. Paste the following code to the top of the code window, above the namespace declaration:
Using system. Data;
Using system. Data. oledb; In the code window, paste the following code into the main function:
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 (); Modify connectionstring to connect to your SQL Server computer using an account with permissions to list columns in the Employees table.
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.
Press enter to end the console application and return to IDE.
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_keys returns the primary keys in the directories that can be accessed by specific logins. In this example, oledbconnection is connected 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:

Create a Visual C # console application project. By default, class1.cs is added to the project.
Open the class1 code window. Paste the following code to the top of the code window, above the namespace declaration:
Using system. Data;
Using system. Data. oledb; In the code window, paste the following code into the main function:
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 (); Modify connectionstring to connect to your SQL Server computer using an account with sufficient permissions to list primary keys.
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.
Press enter to end the console application and return to IDE.

This article from csdn blog: http://blog.csdn.net/zaytsing/archive/2009/12/24/5069333.aspx