Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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
0
niceguy971
Asked:
niceguy971
3 Solutions
 
Easwaran ParamasivamCommented:
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_patelCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 👽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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now