C# - the method to get a number of open Sql connections

What would be the best way to create a method which returns the number of open Sql connections?  Let say I'm debugging my code (or even at some points in the code) - i would like to check at some point and make sure that all connections where closed.

SqlConnection connection = new SqlConnection( connectionString)

Thanks
niceguy971Asked:
Who is Participating?
 
Easwaran ParamasivamConnect With a Mentor Commented:
Effectively  create your connection object with using statement. While leaving the using block the connection will be closed automatically.

http://weblogs.asp.net/jasonsalas/archive/2005/02/08/368811.aspx

No need to verify whether the connection is open or not. using is your best friend.
0
 
niceguy971Author Commented:
It's a good approach..but it does NOT answer my original question. Let say... I would like to have a method which returns the number of open Sql connections. How to create such method? It's also very interesting from the theoretical standpoint.

Thanks
0
 
jagrut_patelConnect With a Mentor Commented:
There is no built-in support for this that I am aware of. You can try this way.

public class MyDbConnection
{
    private static int connectionCount;

    public MyDbConnection(string connectionString)
    {
        this.DbConnection = new SqlConnection(connectionString);
        this.DbConnection.StateChange += new StateChangeEventHandler(connection_StateChange);
    }


    public SqlConnection DbConnection
    {
        get;
        private set;
    }

    public static int ConnectionCount
    {
        get
        {
            return connectionCount;
        }
    }

    void connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if (e.CurrentState == ConnectionState.Open)
            connectionCount++;
        else if (e.CurrentState == ConnectionState.Closed)
            connectionCount--;
    }
}

Open in new window


Usage

string connectionString = "Your connection string";

MyDbConnection conn1 = new MyDbConnection(connectionString);
conn1.DbConnection.Open();
//conn1.DbConnection.Close();

MyDbConnection conn2 = new MyDbConnection(connectionString);
using (conn2.DbConnection)
{
    conn2.DbConnection.Open();
}

MyDbConnection conn3 = new MyDbConnection(connectionString);
conn3.DbConnection.Open();
//conn3.DbConnection.Close();

MyDbConnection conn4 = new MyDbConnection(connectionString);
conn4.DbConnection.Open();
conn4.DbConnection.Close();

MessageBox.Show(MyDbConnection.ConnectionCount.ToString()); 

Open in new window


HTH!
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Easwaran ParamasivamCommented:
Really intresting!! But using the 'using' keyword requires no need to verify this extra work. That is the best practice. No connection or memory leak will happen. We have used in our real project and found good improvement in performance as well.
0
 
käµfm³d 👽Connect With a Mentor Commented:
I totally agree with EaswaranP: if you're following best practices, you shouldn't need to count the number of connections  ; )


To answer your specific question, you might try this, though I can't say I've used it myself:  http://fuchangmiao.blogspot.com/2009/03/sql-server-open-connections-exist-in.html
0
 
niceguy971Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.