asp.net c# how to know how many connection object are currently open but not closed by web page

Question

am working on one project which is web based application many developers are used connection object but they are not closed it.

so i can i get the details by webpage that how many connection object are currently used but not closed

am using some code which is mention here

protected void btnCheckConnection_Click(object sender, EventArgs e)
        {
            GetIntegratedSecurityConnectionString();
            SetUpPerformanceCounters();          
            CreateConnections();
        }

private static string GetIntegratedSecurityConnectionString()
        {
            string @SqlConn = "Server=190.196.19.12;UID=test1;PWD=test1;Database=test";
            return @SqlConn;
        }  



private void CreateConnections()
    {
        // List the Performance counters.  
        WritePerformanceCounters();

        // Create 4 connections and display counter information.  
        SqlConnection connection1 = new SqlConnection(
              GetIntegratedSecurityConnectionString());
        connection1.Open();
        Console.WriteLine("Opened the 1st Connection:");
        WritePerformanceCounters();

        SqlConnection connection2 = new SqlConnection(
              GetSqlConnectionStringDifferent());
        connection2.Open();
        Response.Write("Opened the 2nd Connection:");
        WritePerformanceCounters();

        SqlConnection connection3 = new SqlConnection(
              GetSqlConnectionString());
        connection3.Open();
        Console.WriteLine("Opened the 3rd Connection:");
        WritePerformanceCounters();

        SqlConnection connection4 = new SqlConnection(
              GetSqlConnectionString());
        connection4.Open();
        Console.WriteLine("Opened the 4th Connection:");
        WritePerformanceCounters();

        connection1.Close();
        Console.WriteLine("Closed the 1st Connection:");
        WritePerformanceCounters();

        connection2.Close();
        Console.WriteLine("Closed the 2nd Connection:");
        WritePerformanceCounters();

        connection3.Close();
        Console.WriteLine("Closed the 3rd Connection:");
        WritePerformanceCounters();

        connection4.Close();
        Console.WriteLine("Closed the 4th Connection:");
        WritePerformanceCounters();
    }


private enum ADO_Net_Performance_Counters
        {
            NumberOfActiveConnectionPools,
            NumberOfReclaimedConnections,
            HardConnectsPerSecond,
            HardDisconnectsPerSecond,
            NumberOfActiveConnectionPoolGroups,
            NumberOfInactiveConnectionPoolGroups,
            NumberOfInactiveConnectionPools,
            NumberOfNonPooledConnections,
            NumberOfPooledConnections,
            NumberOfStasisConnections,
            NumberOfFreeConnections
            // The following performance counters are more expensive to track.  
            // Enable ConnectionPoolPerformanceCounterDetail in your config file.  
            //     SoftConnectsPerSecond  
            //     SoftDisconnectsPerSecond  
            //     NumberOfActiveConnections  

        }



  private void SetUpPerformanceCounters()
        {
            connection.Close();
            this.PerfCounters = new PerformanceCounter[11];
            string instanceName = GetInstanceName();
            testInstanceName = instanceName;
            Type apc = typeof(ADO_Net_Performance_Counters);
            int i = 0;
            foreach (string s in Enum.GetNames(apc))
            {
                this.PerfCounters[i] = new PerformanceCounter();
                this.PerfCounters[i].CategoryName = ".NET Data Provider for SqlServer";
                this.PerfCounters[i].CounterName = s;
                this.PerfCounters[i].InstanceName = instanceName;
                i++;
            }
        }
        [DllImport("kernel32.dll", SetLastError = true)]
        static extern int GetCurrentProcessId();


private string GetInstanceName()
        {
            //This works for Winforms apps.  
            /*
            string instanceName =
                System.Reflection.Assembly.GetEntryAssembly().GetName().Name;
                */
            //This works for Web apps.  

            //string instanceName = System.Environment.UserDomainName; //System.Reflection.Assembly.GetEntryAssembly().GetName().Name;
            string instanceName = System.Reflection.Assembly.GetCallingAssembly().GetName().Name;

            // Must replace special characters like (, ), #, /, \\  
            string instanceName2 =
                AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(', '[')
                .Replace(')', ']').Replace('#', '_').Replace('/', '_').Replace('\\', '_');

            // For ASP.NET applications your instanceName will be your CurrentDomain's   
            // FriendlyName. Replace the line above that sets the instanceName with this:  
            // instanceName = AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(','[')  
            // .Replace(')',']').Replace('#','_').Replace('/','_').Replace('\\','_');  

            string pid = GetCurrentProcessId().ToString();
            instanceName = instanceName + "[" + pid + "]";
            Console.WriteLine("Instance Name: {0}", instanceName);
            Console.WriteLine("---------------------------");
            return instanceName;
        }

  private void WritePerformanceCounters()
        {
            foreach (PerformanceCounter p in this.PerfCounters)
            {    
                string strName = p.CounterName + " = " + p.NextValue();    
            }    
        }

if amusing p.NextValue() then facing error that Additional information: Instance 'Dummy_Project[9544]' does not exist in the specified Category.

Answer 1

In a web application, always close the connection as soon as you're done with it. Tell all other developers on your team to do the same.

You need to wrap your calls in using statement (or try...catch if you wish to handle the exceptions), and it will close it and dispose it automatically.   That will not really close the connection. It will just return it to the connections pool, and next time you try to open a new connection (within a short time), you will actually be given a connection from the pool. This will significantly enhance the performance and allow your application to scale up better.