Post
Ask Question

Retrieving Schema Information Using ADO.NET and C#

Description

 
At times, in addition to querying and updating data in a database, you also need to retrieve information about the database as well as its contents. This information is called Database Metadata. In this article, I will describe the ways to retrieve the database schema information using System.Data.SqlClient class and the System.Data.OleDb class.
 
The database schema also known as metadata is how the database defines itself. By using the database schema information, you can see the database structure and all the data in it. When working with databases in .NET, you either use the System.Data.SqlClient class or the System.Data.OleDb class. These two classes retrieve the table schema differently. We will explore both ways.
 
The OleDbConnection object exposes a GetOleDbSchemaTable method that you can use to retrieve schema information for your databases, such as a list of tables or columns. GetOleDbSchemaTable returns a DataTable that is populated with the schema information. There is no direct equivalent to this feature in the SQL Client .NET Data Provider. The SQL Server .NET Data Provider exposes backend schema information through stored procedures and informational views. You simply query SQL Server's Information_Schema and restrict the results to Table_Type = 'BASE TABLE' or Table_Type = 'VIEW'. However do remember that for information schema views, the metadata returned is limited to that which the user has permission to view.
 
You can execute the following piece of code to retrieve information regarding the various tables on your SQL Server database:
  1. SqlConnection cn = new SqlConnection("PutYourConnectionStringOverHere");  
  2. SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Information_Schema.Tables where Table_Type = 'BASE TABLE'", cn");  
  3. DataTable dt = new DataTable();  
  4. da.Fill(dt); 
Using OleDbConnection.GetOleDbSchemaTable Method
 
When we retrieve schema information with OleDb, we don't need a DataAdapter. We're going to let the connection object get the information for us. The GetOleDbSchemaTable method returns schema information from a data source as indicated by a Guid.( A GUID represents a globally unique identifier (GUID). This identifier has a low probability of being duplicated as it is composed of a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required.) You supply a value from the OleDbSchemaGuid enumeration to specify the type of schema information you want, such as tables, columns, and procedures. In addition to taking the Guid schema argument, you can further restrict the results of the GetOleDbSchemaTable( ) through the second argument. This argument is an object array specifying column restrictions. Using the restrictions parameter, you can retrieve information for just a few selected columns in a particular table rather than retrieving information for all columns in your database. The syntax for the method is :
  1. public DataTable GetOleDbSchemaTable(Guid schId, object[] restrictions) 
Each value in the Object array corresponds to a DataColumn in the resulting DataTable. The Restrictions array for the member should have the following structure:
 
{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}
 
When you pass values in the restriction array, include the 'null' keyword for array elements that do not contain values.
 
For E.g.: You can use the following code to retrieve just the columns from the Customers table:
  1. string strConn = "Provider=SQLOLEDB;Data Source= server;" +  
  2.     "Initial Catalog=Northwind;Trusted_Connection=Yes;";  
  3. OleDbConnection con = new OleDbConnection(strConn);  
  4. con.Open();  
  5. object[] objArrRestrict;  
  6. objArrRestrict = new object[] { null, null, "Customers", null };  
  7. DataTable tbl;  
  8. tbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objArrRestrict); 
Retrieving a list of tables using the OleDbSchemaGuid
 
Since Information views are limited to SQL Server and won't work with other data sources, we have seen that the OleDbConnection object provides a GetOleDbSchemaTable() method that can return various types of schema information, similar to what SQL Server accomplishes with its built-in informational views. Each data source handles this task differently, depending on the data source, but the ADO.NET code is generic. Let's see an example
  1. con.Open();  
  2. object[] objArrRestrict;  
  3. //select just TABLE in the Object array of restrictions.  
  4. //Remove TABLE and insert Null to see tables, views, and other objects.  
  5. objArrRestrict = new object[] { null, null, null, "TABLE" };  
  6. DataTable schemaTbl;  
  7. schemaTbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict);  
  8. // Display the table name from each row in the schema  
  9. foreach(DataRow row in schemaTbl.Rows) {  
  10.         listBox.Items.Add(row["TABLE_NAME"]); 
Retrieving a List of Columns in a Table
 
The following code lists the names of columns in the Customers table in the SQL Server Northwind database.
 
OleDbSchemaGuid.Columns returns those columns in tables and views that are accessible to a given log on. If you specify an Object array of {null, null, "Customers", null}, you filter to include only the columns for the Customers table. Lets see the code :
  1. con.Open();  
  2. object[] objArrRestrict;  
  3. objArrRestrict = new object[] { null, null, "Customers", null };  
  4. DataTable schemaCols;  
  5. schemaCols = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objArrRestrict);  
  6. //List the schema info for the selected table  
  7. foreach(DataRow row in schemaCols.Rows) {  
  8.     listBox.Items.Add(row["COLUMN_NAME"]);  
  9. } 

Summary

 
In this article, we saw how to retrieve schema information using the GetOleDbSchemaTable and Visual C#. We also saw how the SQL Server .NET Data Provider exposes backend schema information through informational views. For listing tables in Access Databases, we have two techniques. The first technique uses the GetOLEDBSchemaTable( ) method to return schema information. For the second technique, you'll need a reference to the Primary Interop Assembly for ADO provided in the file ADODB.DLL that uses ActiveX Database Objects Extensions(ADOX) from COM interop. ADOX has a Tables property that exposes a collection of Table objects in the database. I would recommend O'Reilly-ADO.NET CookBook to learn more about this.


Similar Articles