Obtaining Additional Provider Information

Many of the data providers expose additional information that might be useful for debugging, tracing, and diagnosing purposes. Depending on the provider, you might be able to get information about the database server version or any number of other pieces of useful information, even metadata about columns, tables, and databases contained within the server.

Using the RetrieveStatistics Method

One of the useful methods exposed by the SqlConnection class for debugging, tracing, and diagnosing purposes is the RetrieveStatistics() method. This method returns a name/value pair collection of statistics about the connection itself. You can use this information to obtain statistics before and after executing commands to compare and examine the impact of each of your commands. The code in Listing 19.5 illustrates how to use this method.

Listing 19.5. Using the RetrieveStatistics Method

using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; using System.Collections; namespace SqlStatistics { class Program { static void Main(string[] args) {     SqlConnection conn = new SqlConnection(         "data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");     conn.StatisticsEnabled = true;     conn.Open();     Hashtable ht = (Hashtable)conn.RetrieveStatistics();     foreach (string key in ht.Keys)     {         Console.WriteLine("{0} : {1}", key, ht[key]);     }     Console.ReadLine(); } } } 

Note that the connection won't gather statistics unless indicated by the StatisticsEnabled property due to the overhead involved in maintaining metrics. The output from the preceding code on a freshly created connection is as follows:

NetworkServerTime : 0 BytesReceived : 0 UnpreparedExecs : 0 SumResultSets : 0 SelectCount : 0 PreparedExecs : 0 ConnectionTime : 0 ExecutionTime : 109 Prepares : 0 BuffersSent : 0 SelectRows : 0 ServerRoundtrips : 0 CursorOpens : 0 Transactions : 0 BytesSent : 0 BuffersReceived : 0 IduRows : 0 IduCount : 0 

Obtaining Schema Information from Data Providers

Another extremely valuable piece of information that you can get from a data provider is a schema. The GetSchema() method of a DbConnection class is the means by which you can interrogate a data provider of an enormous amount of valuable data.

When you invoke this method with no arguments, it provides you with the list of schemas that you can query. For example, when you call GetSchema() on a SQL Server 2005 connection, you get the following list of schemas contained in a table called MetaDataCollections:

MetaDataCollections DataSourceInformation DataTypes Restrictions ReservedWords Users Databases Tables Columns Views ViewColumns ProcedureParameters Procedures ForeignKeys IndexColumns Indexes UserDefinedTypes 

You can experiment with the results for querying each of the above metadata collections. For example, a query to the data provider for the Databases metadata collection resulted in the following list of databases:

master tempdb model msdb ReportServer ReportServerTempDB SampleDB 

The database list also included the database creation date and the dbid of the database.