.NET and me Coding dreams since 1998!

12Nov/0711

How to build your own SQL Server Explorer

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).

image[5]

 

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

Share this post :

del.icio.us Tags: ,,
Comments (11) Trackbacks (0)
  1. Hi,

    in the section "How to enumerate tables ..", you’re calling a function GetActiveConnectionString which i can’t find in any of the above code. is this an error?

    thanks

    Joe

  2. Hi Joe,

    I’ve updated article with correct source code and made a small example containing the code from this post

    Nikola

  3. Thanks for sharing your code.

    I think the formatting for the decimal data type should be:

    string.Format("{0}({1},{2})", columnDataType, row["numeric_precision"], row["numeric_scale"]);

  4. It seems that GetDataSource will not work when a machine is not connected to network.  How can I get a list of SQL Server Instances when the machine is not connected to network and has multiple SQL Server Instances running on it?

  5. It seems that GetDataSource will not work when a machine is not connected to network.   How can I get list of SQL Server Instances when the machine is not connected to network but it is having multiple SQL Server Instances running in it?

  6. Excellent Article. Much clearer than other documentation out there. Thanks

  7. Wonderful article but like Peter identified, it seems it doesn’t retrieve any Sql Server instances for standalone machines. How can one get a list of Sql Server instances when the machine is not connected to network but it is having multiple SQL Server Instances running in it?.

    Thanks a lot in advance for your reply.

  8. I just figured out that I could test for the list returned by GetDataSource and if it is null when I am not connected to a LAN, then I could prompt the user for a Sql Server instance. This  is a manual way of doing this and the user would have to know the instances beforehand.

    We can hard code the instances as shown below and the program would still work.

    //string[] strLocalServers = new string[] {"chikeo-pc", "chikeo-pcsqlexpress"};

               //Later add code to check if List servers is null and then to accept entry of server instance name from the user if so.

               //If IList<string> servers is null, that means that the host computer is not connected to a local area network.

               //IList<string> servers = SqlHelper.GetActiveServers();

               int machineNameLength = Environment.MachineName.Length;

               int selectedIndex = -1;

    Please if anyone has any other way of doing this, please let me know. If it can be done with SQL-DMO, can SQL-DMO be used for enumerating SQL 2005 as well and how can I use it without installing Enterprise Manager?

    Thanks indvance.

  9. thanks a lot  malovicn

    you helped very much in the indexColumns part.

    thanks and best regards

  10. Is there any other way to find whether a column of a table is PK or FK? You have used the following code to determine that.

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

    What if the primary key name doesn’t start with ‘PK’ or ‘FK’?

  11. Fantastic


Leave a comment

No trackbacks yet.