If you read my article on finding SQL Server instances, you’ll know that I mentioned that I planned on writing a series of articles around a code generator that I have been playing with off and on for the last couple of years. Since this generator was designed to create class definitions around tables, views, and procedures in an SQL Server database, I needed to figure out and write the code to get schema information from a given connection. As it turns out, ADO.NET provides a fairly robust mechanism for implementers of data providers to return this information from the underlying data store. In this article, I will be focusing on the information available from the System.Data.SqlClient namespace – but, techniques shown here apply to all of the data providers built into the .NET framework. I have similar code using System.Data.OracleClient that I can provide if it is of interest.
I’ll start off simple and create a small C# console application that can list all of the databases in an SQL Server instance.
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4:
5: namespace SchemaInfo
6: {
7: class Program
8: {
9: static void Main ( string[] args )
10: {
11: // build a connection string to a sql server instance
12: SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder ();
13: connectionBuilder.DataSource = "SAURON";
14: connectionBuilder.IntegratedSecurity = true;
15:
16: // now, lets list all of the databases in this instance
17: using ( SqlConnection connection = new SqlConnection ( connectionBuilder.ConnectionString ) )
18: {
19: connection.Open ();
20:
21: // get the database information
22: DataTable databases = connection.GetSchema ( SqlClientMetaDataCollectionNames.Databases );
23:
24: // print out the connections
25: foreach ( DataColumn column in databases.Columns )
26: {
27: Console.Write ( "{0,-25}", column.ColumnName );
28: }
29: Console.WriteLine ();
30:
31: // print out the rows...
32: foreach ( DataRow database in databases.Rows )
33: {
34: for ( int i = 0; i < database.ItemArray.Length; i++ )
35: {
36: Console.Write ( "{0,-25}", database.ItemArray[i] );
37: }
38: Console.WriteLine ();
39: }
40: }
41: }
42: }
43: }
The key to the above codes success is the SqlConnection.GetSchema method. By passing in a string that represents the name of one of the provider’s supported schema collections, you can get a DataTable back containing the requested information. There are essentially two types of schema collections – Common Schema Collections and Provider Specific Schema Collections. As the names imply, the common schema collections are the schema collections implemented by all of the .NET framework providers, where as the provider specific collections apply to a specific provider.
You can get a list of the schema collections supported by your provider by calling the GetSchema method with no arguments. Here is the above example, with the schema collection name commented out:
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: using System.Windows.Forms;
5:
6: namespace SchemaInfo
7: {
8: class Program
9: {
10: static void Main ( string[] args )
11: {
12: // build a connection string to a sql server instance
13: SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder ();
14: connectionBuilder.DataSource = "SAURON";
15: connectionBuilder.IntegratedSecurity = true;
16:
17: // now, lets list all of the databases in this instance
18: using ( SqlConnection connection = new SqlConnection ( connectionBuilder.ConnectionString ) )
19: {
20: connection.Open ();
21:
22: // get the database information
23: DataTable databases = connection.GetSchema ( /*SqlClientMetaDataCollectionNames.Databases*/ );
24:
25: // print out the connections
26: foreach ( DataColumn column in databases.Columns )
27: {
28: Console.Write ( "{0,-25}", column.ColumnName );
29: }
30: Console.WriteLine ();
31:
32: // print out the rows...
33: foreach ( DataRow database in databases.Rows )
34: {
35: for ( int i = 0; i < database.ItemArray.Length; i++ )
36: {
37: Console.Write ( "{0,-25}", database.ItemArray[i] );
38: }
39: Console.WriteLine ();
40: }
41: }
42: }
43: }
44: }
You can find a lot of information on using the GetSchema method and the various schema collections here.
So, now that we’ve introduced the concept GetSchema and schema collections, let’s do something a little more complex. Let’s build a database explorer type control. Something like a very simplified version of the Object Explorer in SQL Server Management Studio.
I purposefully left the implementation of gathering field and parameter info to the reader – but, I hope that this was enough to introduce the key concepts of using the GetSchema method of the SqlConnection class to dynamically discover schema infromation. In the entity generator that I have been discussing, I use a variant of this exact control. The major differences being that it supports multiple connection nodes and displays images for the various database objects.