Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-05
6
Medium Priority
?
300 Views
Last Modified: 2013-12-17
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
Comment
Question by:niceguy971
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 668 total points
ID: 36486406
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
 

Author Comment

by:niceguy971
ID: 36486446
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
 
LVL 8

Assisted Solution

by:jagrut_patel
jagrut_patel earned 664 total points
ID: 36486463
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36486477
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
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 668 total points
ID: 36492610
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
 

Author Closing Comment

by:niceguy971
ID: 36532936
Thanks
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question