Ali Özgür - The Pragmatic Developer

My Amazon.com Wish List

RecentComments

Comment RSS
aliozgur posted on October 22, 2007 11:49
Installation/Update:

1. Copy the AddIn (.zip) file to your local folder.

2. Open PragmaSQL and go to Tools->AddIn Manager menu and open AddIn Manager dialog

3. In the AddIn Manager dialog click Install AddIn button. In the open file dialog select previously copied add-in file.

4. Restart PragmaSQL

Manual AddIn Update:

1. Browse to C:\Documents and Settings\[Your user name]\ Application Data\PragmaSQL

2. In the specified folder above there is another folder name AddIns. In this folder there is one folder for each add-in you installed.

3.  Open the add-in folder you want to update

4.  Copy add-in files (if add-in files are in zip file extract files to your local) to the respective add-in folder.

5.  Restart PragmaSQL


Posted in: PragmaSQL AddIns  Tags:
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

C#-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);

  }

}


Posted in: .NET Development , CodeProject  Tags: