Working with ADO.NET Schema APIs

Introduction

In majority of data driven applications developers deal with SQL queries that select, insert, update or delete data from the database. However, at times you need to retrieve schema information from the database. Suppose you are building applications that performs data import and export between two or more databases. As a good solution you would want to retrieve table schema at runtime rather than hard coding it. Luckily, ADO.NET provides a set of classes that allow you to query database schema. In this article I will illustrate how these classes work.

Possible Approaches

Whenever you wish to retrieve database schema you can use couple of techniques:

• Store database schema information in some XML file and then read this XML file in your application.
• Use system stored procedures of the underlying database to retrieve schema information.

As you might have guessed, the first technique is not so good as generating XML file is a manual operation. Even the second approach has its own drawbacks. All databases do not have the same set of stored procedures for retrieving database schema. Further it is not guaranteed that these stored procedures will return information in consistent manner. Considering these limitations it is worth investigating ADO.NET schema API model.

Retrieving Database Schema

In order to retrieve database schema information ADO.NET connection object provides GetSchema() method. The GetSchema() method comes in three flavors:

• GetSchema() method called without any parameters returns you what is known as metadata collections. A metadata collection is a collection that gives metadata information about a specific type of item (table, view, stored procedure etc.)
• GetSchema() method called by passing a metadata collection name returns information about items found in the specified collection. For example, if you specify tables metadata collection then GetSchema() method will return information about all the tables in the database. The information is returned as a DataTable.
• GetSchema() method called by passing metadata collection name and filter criteria returns the items from the collection after applying the filter criteria.

Metadata Collections

Metadata collections are collections that contain information about items whose schema can be retrieved. The common metadata collections are:

• Tables : Gives information about tables belonging to a database
• Views : Gives information about views belonging to a database
• Columns : Gives information about table columns
• ViewColumns : Gives information about columns of views
• Procedures : Gives information about stored procedures
• ProcedureParameters : Gives information about stored procedure parameters
• Indexes : Gives information about indexes
• IndexColumns : Gives information about columns of the indexes
• DataTypes : Gives information about data types
• Users : Gives information about database users

Restrictions

Metadata collections by default return the whole information about an item. For example, ProcedureParameters collection returns information about all the parameters of all the stored procedures in the system. Many times this is not what you want. You want parameter information of a specific stored procedure. Such filtering of metadata collection can be achieved via restrictions. A restriction is a string array that specifies the filter criteria. Not all the metadata collections share the same filter criteria. A particular metadata collection has fixed criterion on which it can be filtered. For example, the tables metadata collection can be filtered on the basis of catalog name, owner name and table name. The other metadata collections may have additional (or less) filter criterions.

Creating a Simple Database Explorer

In order to illustrate what we just discussed, lets create a simple database explorer as shown below: 

The database explorer simply allows you to display databases, tables, views and stored procedures in a tree fashion.

To begin with, create a new Windows Forms application in Visual Studio and drag and drop a TreeView on it. You will need a helper method that you can call from multiple places to quickly add a node to the TreeView. This helper method is shown below:

private TreeNode AddNode(TreeNode parent, 
string text,string tag,int imgIndex)
{
TreeNode child = new TreeNode(text);
child.ImageIndex = imgIndex;
child.SelectedImageIndex = imgIndex;
child.Tag = tag;
if (parent != null)
{
parent.Nodes.Add(child);
}
else
{
treeView1.Nodes.Add(child);
}
return child;
}

The AddNode() helper method accepts parent node, text to be displayed for the newly added node, a unique tag for the new node so that you can identify that node later and image index indicating the icon of the node. Inside it creates a new TreeNode and sets its various properties. If the parent node is null the new node is added directly to the tree view otherwise it gets appended as a child node of the parent node.

You need one more helper method named GetSchemaInfo() that returns schema information about a specific item. The GetSchemaInfo() method is shown below:

private DataTable GetSchemaInfo
(SchemaCollection collection,string entity)
{
DataTable dt = null;
string collectionname = "";
string[] restrictions = null;

switch (collection)
{
case SchemaCollection.Databases:
collectionname = "Databases";
if (entity != "")
{
restrictions = new string[1];
restrictions[0] = entity;
}
break;
case SchemaCollection.Tables:
collectionname = "Tables";
if (entity != "")
{
restrictions = new string[4];
restrictions[0] = entity;
}
break;
case SchemaCollection.Views:
collectionname = "Views";
if (entity != "")
{
restrictions = new string[3];
restrictions[0] = entity;
}
break;
case SchemaCollection.Columns:
collectionname = "Columns";
if (entity != "")
{
restrictions = new string[4];
restrictions[2] = entity;
}
break;
case SchemaCollection.ViewColumns:
collectionname = "ViewColumns";
if (entity != "")
{
restrictions = new string[4];
restrictions[2] = entity;
}
break;
case SchemaCollection.StoredProcedures:
collectionname = "Procedures";
if (entity != "")
{
restrictions = new string[4];
restrictions[0] = entity;
}
break;
case SchemaCollection.Parameters:
collectionname = "ProcedureParameters";
if (entity != "")
{
restrictions = new string[4];
restrictions[2] = entity;
}
break;
}
string strConn = ConfigurationManager.
ConnectionStrings["connstr"].ConnectionString;
SqlConnection cnn = new SqlConnection(strConn);
cnn.Open();
dt = cnn.GetSchema(collectionname,restrictions);
cnn.Close();
return dt;
}

The GetSchemaInfo() method accepts two parameters viz. a value from SchemaCollection enumeration and name of the collection. The SchemaCollection enumeration is user defined enumeration and looks as shown below:

public enum SchemaCollection
{
Databases,
Tables,
Views,
StoredProcedures,
Columns,
ViewColumns,
Parameters
}

The SchemaCollection enumeration simply indicates the metadata collection to be used for retrieving schema information.

Depending on the collection name the applicable restrictions vary. For example, if you are using databases collection then the restrictions array should contain just one element indicating the database name whose schema is to be retrieved. On the other hand if you are using tables collection then the restrictions array contains four elements for specifying catalog name, owner name, table name and table type respectively. The same applies for Views, Columns, ViewColumns, Procedures and ProcedureParameters collections. Then the code retrieves the database connection string from the application configuration file. An instance of SqlConnection object is then created and opened. The GetSchema() method is called on the connection by passing the collection name and restriction array. The DataTable thus obtained is returned back to the caller.

As the form loads you need to retrieve information about all the databases and display database names in the tree view. This is done in the Lod event of the form as shown below:

private void Form2_Load(object sender, 
EventArgs e)
{
TreeNode root= treeView1.Nodes.Add("All Databases");
DataTable db = GetSchemaInfo
(SchemaCollection.Databases, "");
foreach (DataRow row in db.Rows)
{
AddNode(root, row["DATABASE_NAME"].ToString(),
"Database",0);
}
}

Here, the code adds a toot node with text "All Databases". It then calls GetSchemaInfo() method specifying that we want to retrieve information about databases. The returned DataTable contains rows depending on the databases available. Each row contains many columns. We use DATABASE_NAME column to display database name. Notice that while adding the node the tag of the node is set to "Database". This way we can identify whether ta node represents database or any other type of item.

When user selects a particular node we need to populate its child nodes. For example, if a node represents a database clicking on it should populate tables, views and procedures within it. If a node is a table clicking on it should populate its column information and so on. This task is accomplished in the NodeMouseClick event handler of the TreeView control.

private void treeView1_NodeMouseClick
(object sender, TreeNodeMouseClickEventArgs e)
{
DataTable dt = null;
if (e.Node.Tag != null)
{
e.Node.Nodes.Clear();
switch (e.Node.Tag.ToString())
{
case "Database":
TreeNode tblfolder=AddNode(e.Node,
 "Tables", null, 5);
tblfolder.SelectedImageIndex = 6;
TreeNode viewfolder = AddNode(e.Node,
 "Views", null, 5);
viewfolder.SelectedImageIndex = 6;
TreeNode procfolder = AddNode(e.Node,
 "Procedures", null, 5);
procfolder.SelectedImageIndex = 6;

dt = GetSchemaInfo(SchemaCollection.Tables, 
e.Node.Text);
foreach (DataRow row in dt.Rows)
{
AddNode(tblfolder, row["TABLE_NAME"].ToString(), 
"Table",1);
}
dt = GetSchemaInfo(SchemaCollection.StoredProcedures, 
e.Node.Text);
foreach (DataRow row in dt.Rows)
{
AddNode(procfolder, row["SPECIFIC_NAME"].ToString(), 
"Procedure",2);
}
dt = GetSchemaInfo(SchemaCollection.Views, e.Node.Text);
foreach (DataRow row in dt.Rows)
{
AddNode(viewfolder, row["TABLE_NAME"].ToString(),
 "View", 1);
}
break;
case "Table":
dt = GetSchemaInfo(SchemaCollection.Columns, 
e.Node.Text);
foreach (DataRow row in dt.Rows)
{
AddNode(e.Node, row["COLUMN_NAME"].ToString() + 
" (" + row["DATA_TYPE"].ToString() + "," + 
(row["IS_NULLABLE"].ToString() == "YES" ? "NULL" : 
"NOT NULL") + ")", "Column", 3);
}
break;
case "View":
dt = GetSchemaInfo(SchemaCollection.ViewColumns, 
e.Node.Text);
foreach (DataRow row in dt.Rows)
{
AddNode(e.Node, row["COLUMN_NAME"].ToString(), 
"ViewColumn", 3);
}
break;
case "Procedure":
dt = GetSchemaInfo(SchemaCollection.Parameters, 
e.Node.Text);
foreach (DataRow row in dt.Rows)
{
AddNode(e.Node, row["PARAMETER_NAME"].ToString() + 
" (" + row["DATA_TYPE"].ToString() + ")", 
"Parameter", 4);
}
break;
}
}
}

The code above calls the same GetSchemaInfo() helper method depending on the Tag property of the tree node being clicked. If the Tag is "Database" then information about tables, views and stored procedures is fetched and their list is populated. Notice the DataTable column names used in the for loop. Table name, stored procedure name and view name is obtained from TABLE_NAME, SPECIFIC_NAME and TABLE_NAME columns respectively.

The other cases perform similar work Tag values of "Table", "View" and "Procedure". The column name, data type and NULL behavior is retrieved from COLUMN_NAME, DATA_TYPE and IS_NULLABLE columns. Similarly, parameter name and its data type is retrieved from PARAMETER_NAME and DATA_TYPE columns respectively.

Before you run the application do not forget to add the database connection string in the application configuration file as shown below:

<connectionStrings>
<add 
name="connstr" 
connectionString="data source=.\sqlexpress;
initial catalog=northwind;
integrated security=true" 
providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>

That's it! You can now run the application and try navigating different database objects.

 

Posted On :	25 Feb 2008
Current Rating :
Rate This Article :	5 - Excellent 4 - Very Good 3 - Good 2 - Average 1 - Poor

About the Author

Bipin Joshi Bipin Joshi is a blogger and writes about Yoga, spirituality and technology. A former Software Consultant by profession he worked for many years with Microsoft technologies such as C, C++, C#, VB, ASP and ASP.NET. Bipin got selected as a Most Valuable Professional (MVP) by Microsoft for six consecutive years before he decided to take a back seat from the mainstream IT to continue his spiritual interests. More details about him can be read here.