Recently, I've started making in my free time my own little ORM tool, mainly as an fun way into exploration of the ADO .NET 2.0

Every  dissent ORM tool has to be based on DB object enumeration activities and I've noticed there are not a lot straight "how to" articles on net how to do various things (at least I had a hard time finding them), so I decided to make a simple blog post describing exactly those How-To's

How to enumerate visible SQL server instances

Enumerating visible instances of MS SQL Server can be performed by executing the GetDataSource() of the SqlDataSourceEnumerator type singleton instance.

GetDataSource returns four column data table with next columns:

1. ServerName - Name of the server.
2. InstanceName - Name of the server instance. Blank if the server is running as the default instance.
3. IsClustered - Indicates whether the server is part of a cluster.
4. Version - Version of the server (8.00.x for SQL Server 2000, and 9.00.x for SQL Server 2005).

Code example:

   1: public static IList<string> GetActiveServers()

   2: {

   3:     Collection<string> result = new Collection<string>();

   4:     SqlDataSourceEnumerator instanceEnumerator = SqlDataSourceEnumerator.Instance;

   5:     DataTable instancesTable = instanceEnumerator.GetDataSources();

   6:     foreach (DataRow row in instancesTable.Rows)

   7:     {

   8:         if (!string.IsNullOrEmpty(row["InstanceName"].ToString()))

   9:             result.Add(string.Format(@"{0}\{1}", row["ServerName"], row["InstanceName"]));

  10:         else

  11:             result.Add(row["ServerName"].ToString());

  12:     }

  13:     return result;

  14: }

How to enumerate databases of the given SQL server instance

Enumerating the databases of the given server can be performed by executing the GetSchema method of the SqlConnection instance with a SqlClientMetaDataCollectionNames.Databases string enumeration value passed to method.

Passing that enumeration or it's string equivalent ("Databases") is totally the same, except enumeration looks cooler :)

Code example:

   1: public static IList<string> GetDatabases(string serverName, string userId, string password,

   2:                                               bool windowsAuthentication)

   3:      {

   4:          Collection<string> result = new Collection<string>();

   5:          using (

   6:              SqlConnection connection =

   7:                  GetActiveConnection(serverName, string.Empty, userId, password, windowsAuthentication))

   8:          {

   9:              connection.Open();

  10:              DataTable dt = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);

  11:              foreach (DataRow row in dt.Rows)

  12:              {

  13:                  result.Add(string.Format("{0}", row[0]));

  14:              }

  15:          }

  16:          return result;

  17:      }

In line 6, we are using an instance of SqlConnection type created by GetActiveConnection method.

In line 10, we are calling GetSchema connection instance method which returns a data table with a single column which contains the name of the database

in ADO NET, methods used for retrieving schema information always are overloaded with a version accepting additional string array parameter which is used for passing the restrictions (you can think of it as a filter criteria) which ADO.NET should apply while retrieving resulting set.
Retrieving database schema in our example has only one restriction and that is database name so if we would write something like

   DataTable dt = connection.GetSchema("Databases", new string[] {"Northwind" });

Please notice in that that line that I used "Databases" and not enumeration and that I have passed single string array with "Nortwind" content.
Result of passing that restriction would be that ADO NET would retrieve only databases fulfilling the restriction requirement, which means only Nortwind database data would be returned

GetActiveConnection method creates a new SqlConnection instance using SqlConnectionStringBuilder class which is used to build connection string for given parameters.

Something like this

   1: private static SqlConnection GetActiveConnection(string serverName, string databaseName, string userName,

   2:                                                  string password, bool useIntegratedSecurity)

   3: {

   4:     SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();

   5:     connBuilder.DataSource = serverName;

   6:     connBuilder.InitialCatalog = databaseName;

   7:     connBuilder.IntegratedSecurity = useIntegratedSecurity;

   8:     connBuilder.UserID = userName;

   9:     connBuilder.Password = password;

  10:     return new SqlConnection(connBuilder.ConnectionString);

  11: }

I'll be using this helper methods also in rest of the examples

How to enumerate tables of the given database

In general, the procedure of retrieval tables is the same as the procedure described for databases, in a sense that the GetSchema method of SqlConnection instance is been called but this time with SqlClientMetaDataCollectionNames.Tables ("Tables") enumerated value.

The big difference between those two is in the fact that tables restriction are contained of four different constraint arguments:

1. database name
2. owner/schema name ("dbo")
3. table name (which should contain null value if we want to retrieve all tables of database)
4. table type (which can have values "VIEW" for views and "BASE TABLE" for tables

So, to retrieve the list of tables for a given database we could use code similar to the next one:

   1: public static IList<string> GetTables(string serverName, string databaseName, string userId, string password,

   2:                                       bool windowsAuthentication)

   3: {

   4:     string[] restrictions = new string[4];

   5:     restrictions[0] = databaseName; // database/catalog name   

   6:     restrictions[1] = "dbo"; // owner/schema name   

   7:     restrictions[2] = null; // table name   

   8:     restrictions[3] = "BASE TABLE"; // table type    

   9:     Collection<string> result = new Collection<string>();

  10:     using (

  11:         SqlConnection connection =

  12:             GetActiveConnection(serverName, databaseName, userId, password, windowsAuthentication))

  13:     {

  14:         connection.Open();

  15:         DataTable dt = connection.GetSchema(SqlClientMetaDataCollectionNames.Tables, restrictions);

  16:         foreach (DataRow row in dt.Rows)

  17:         {

  18:             if (!row[2].ToString().StartsWith("sys"))

  19:                 result.Add(string.Format(@"{0}", row[2]));

  20:         }

  21:     }

  22:     return result;

  23: }

Column keys of the columns of the data table returned are:

• Column 0. "table_catalog"
• Column 1. "table_schema"
• Column 2. "table_name"
• Column 3. "table_type"

How to enumerate columns of the given table

The list of restriction parameters for column retrieval is shorter and it contains next 3 string values:

1. Database name
2. Owner/schema name
3. Table name

SqlConnection instance GetSchema method gets this time SqlClientMetaDataCollectionNames.Columns ("Columns") enumerated value and the resulting data table contains next 18 columns:

Column 0 - "TABLE_CATALOG"
Column 1 - "TABLE_SCHEMA"
Column 2 - "TABLE_NAME"
Column 3 - "COLUMN_NAME"
Column 4 - "ORDINAL_POSTION"
Column 5 - "COLUMN_DEFAULT"
Column 6 - "IS_NULLABLE"
Column 7 - "DATA_TYPE"
Column 8 - "CHARACTER_MAXIMUM_LENGTH"
Column 9 - "CHARACTER_OCTET_LENGTH"
Column 10 - "NUMERIC_PRECISION"
Column 11 - "NUMERIC_PRECISION_RADIX"
Column 12 - "NUMERIC_SCALE"
Column 13 - "DATETIME_PRECISION"
Column 14 - "CHARACTER_SET_CATALOG"
Column 15 - "CHARACTER_SET_SCHEMA"
Column 16 - "CHARACTER_SET_NAME"
Column 17 - "COLLATION_CATALOG"

I believe column names are self explanatory and familiar to all of us, so I'll skip explanation of what they stand for

In our little example we would return concatenated string containing the column name and data type, where data type in case of char data types would show maximal number  of characters and in case of decimal precision data.

The code doing that might look like this:

   1: public static IList<string> GetColumns(

   2:        string serverName, string databaseName, string userId,

   3:        string password, bool windowsAuthentication, string tableName)

   4:    {

   5:        SqlConnection connection =

   6:            GetActiveConnection(serverName, databaseName, userId, 

   7:                                password, windowsAuthentication);

   8:  

   9:        string[] restrictions = new string[3];

  10:        restrictions[0] = connection.Database; // database/catalog name      

  11:        restrictions[1] = "dbo"; // owner/schema name      

  12:        restrictions[2] = tableName; // table name      

  13:        IList<string> result = new Collection<string>();

  14:        using (connection)

  15:        {

  16:            connection.Open();

  17:            DataTable columns = connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, restrictions);

  18:            foreach (DataRow row in columns.Rows)

  19:            {

  20:                string columnName = row[3].ToString();

  21:                string columnDataType = row[7].ToString();

  22:                if (columnDataType.IndexOf("char") > -1)

  23:                {

  24:                    // row[8] - CHARACTER_MAXIMUM_LENGTH    

  25:                    columnDataType = string.Format("{0}({1})", columnDataType, row[8]);

  26:                }

  27:                if (columnDataType.IndexOf("decimal") > -1)

  28:                {

  29:                    // row[10] - CHARACTER_OCTET_LENGTH    

  30:                    // row[11] - NUMERIC_PRECISION    

  31:                    columnDataType = string.Format("{0}({1},{2})", columnDataType, row[10], row[11]);

  32:                }

  33:                result.Add(string.Format("{0},{1}", columnName, columnDataType));

  34:            }

  35:            return result;

  36:        }

  37:    }

How to enumerate indexes of the table

List of restrictions which can be used for indexes is the same as the one used for table, with 4 elements: database name, schema, table name and table type

We are executing GetSchema method of SqlConnection instance with SqlClientMetaDataCollectionNames.IndexColumns ("IndexColumns") enumerated value sent as a parameter and the resulting data table contains next 9 columns

• Column 0 - "constraint_catalog"
• Column 1 - "constraint_schema"
• Column 2 - "constraint_name"
• Column 3 - "table_catalog"
• Column 4 - "table_schema"
• Column 5 - "table_name"
• Column 6 - "column_name"
• Column 7 - "ordinal_position"
• Column 8 - "KeyType"
• Column 8 - "index_name"

Column 8 ("KeyType") describes the data type of the index and contains a numeric value which points to certain data type.

There's a list:

34 :  image
35 :  text
48 :  tinyint
52 :  smallint
56 :  int
58 :  smalldatetime
59 :  real
60 :  money
61 :  datetime
62 :  float
98 :  sql_variant
99 :  ntext
104 :  bit
106 :  decimal
108 :  numeric
122 :  smallmoney
127 : bigint
165 :  varbinary
167 :  varchar
173 :  binary
175 :  char
189 :  timestamp
231 :  nvarchar
239 :  nchar

So to enumerate indexes, one might write next code:

   1: public static IList<string> GetIndexes(SqlConnection connection, string tableName)

   2:       {

   3:           string[] restrictions = new string[3];

   4:           restrictions[0] = connection.Database; // database/catalog name      

   5:           restrictions[1] = "dbo"; // owner/schema name      

   6:           restrictions[2] = tableName; // table name      

   7:           IList<string> result = new Collection<string>();

   8:           using (connection)

   9:           {

  10:               connection.Open();

  11:               DataTable columns = connection.GetSchema(SqlClientMetaDataCollectionNames.IndexColumns, restrictions);

  12:               foreach (DataRow row in columns.Rows)

  13:               {

  14:                   string columnName = row["column_name"].ToString();

  15:                   string indexName = row["index_name"].ToString();

  16:                   bool isPrimaryKey = row["constarint_name"].ToString().StartsWith("PK");

  17:                   result.Add(string.Format("Index:{0}, on column:{1}, PK:{2}", indexName, columnName, isPrimaryKey));

  18:               }

  19:               return result;

  20:           }

  21:       }

How to enumerate parameters of the stored procedure

Enumeration of parameters used in a stored procedure is been done through usage of the SqlCommandBuilder static DeriveParameters method which accepts the SqlCommand instance constructed for a given sql connection and ctored procedure

According to http://www.codeproject.com/useritems/DetermineSql2005SPParams.asp, there is a difference in how SQL 2000 and SQL 2005 and there's a need of handling that problem with some additional approach, but according to my personal experience that's not the case - I never had problems he described.
So, IMHO to enumerate parameters of a stored procedure next simple code should be used regardless of the SQL version:

   1: public static SqlParameter[] DiscoverStoredProcedureParameters(SqlConnection sqlConnection,

   2:                                                                string storedProcedureName)

   3: {

   4:     SqlCommand cmd = new SqlCommand(storedProcedureName, sqlConnection);

   5:     cmd.CommandType = CommandType.StoredProcedure;

   6:     using (sqlConnection)

   7:     {

   8:         sqlConnection.Open();

   9:         SqlCommandBuilder.DeriveParameters(cmd);

  10:     }

  11:     SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

  12:     cmd.Parameters.CopyTo(discoveredParameters, 0);

  13:     return discoveredParameters;

  14: }

Test drive

Bellow you can find a source code of a small example which enumerates the databases (on left), tables of selected database (top right) and columns selected table (right down).

Conclusion

ADO NET 2.0 removes the need of using ADOX or SQLDMO components for the tasks covering examining the structure of the database objects. It is almost trivial (with a bit reading of documentation) to do the thing which were before not-so trivial. But...
Although already very simple to use, I would like to see in future ADO NET 3.0 version  next enhancements:

• Replacing the property bags as a way of passing arguments with DTOs as data carriers instead of string arrays. I guess that would have to be done in some new SQL connection related helper class to preserve compatibilty
• For the same reasons I don't like property bags as a method parameter data carriers, I don't like the result data tables
  Having LINQ in place, I don't see the reason why we won't replace the returning DataTables with some more OOP friendly solution

You can download source code of this example here