In this short article i want to give a sample code describing how you can retreive statistical information about the T-SQL statements executed over a SqlConnection.
Background
While i was developing PragmaSQL T-SQL editor, guys using the initial versions wanted to know how many rows were affected as a result of a T-SQL statement (DML) executed.My first attempt was tracing the T-SQL statements sent by Management Studio to the server. But this was helpless, since Management Studio is not sending any extra statements to figure out how many rows were affected. My second attemtp was wiring-up to SqlInfoMessageEventHandler and inspecting info messages returned by the server. But sql server was not sending any extra information about the executed statements via this event. Then while i was looking to the SqlConnection properties i noticed StatisticsEnabled property and this was the property that would meet my requirement.
Using the code
Retreiving statistical information over SqlConnection is very simple. Here is the sample code
//
// Sample T-SQL execute code
//
private void ExecuteSql(SqlConnection conn, string scriptText)
{
SqlCommand cmd = null;
try
{
conn.StatisticsEnabled = true;
conn.ResetStatistics();
cmd = new SqlCommand(conn, conn);
cmd.ExecuteNonQuery();
// Here is the sample wrapper to process statistics.
ProcessConnectionStatistics(conn.RetrieveStatistics());
}
finally
{
conn.StatisticsEnabled = false;
if(cmd != null)
cmd.Dispose();
}
}
// Sample function to process SqlConnection statistics
// This function only extracts the entry with name IduRows.
// to reflect the rows affected.
private void ProcessConnectionStatistics(IDictionary stats)
{
foreach (object key in stats.Keys)
{
object statVal = stats[key];
if (key == null || key.ToString().ToLowerInvariant() != "IduRows".ToLowerInvariant())
continue;
string statValStr = statVal.ToString();
if (String.IsNullOrEmpty(statValStr) || statValStr.Trim() == "0")
continue;
MessageBox.Show(String.Format("( %s ) rows affected.",statValStr);
}
}
d66aeed8-da86-471c-bb2e-62942f3d4ebf|0|.0