How to reduce waiting time for open connection?

Question

I check connection is available with code:

 SqlConnection objConnection = new SqlConnection(string.Concat(connectionString));
        try
        {
            objConnection.Open(); // this line make wait time if connection not available 
            objConnection.Close();
            SqlConnection.ClearAllPools();
            return true;
        }
        catch
        {
            return false;
        }

When connection not available, It takes a long time to answer .how can reduce it?

If you're connecting over the TCP protocol you could try if the server is configured to respond to a simple ping and use that instead before you initiate a real TCP connection. I assume your client has a network connection going. — rene, Apr 24 '16 at 11:14

Mostafiz · Answer 1 · 2016-04-24T12:31:28.407

1

You can check sql connection this way, this won't take long

        SqlConnection objConnection = new SqlConnection(string.Concat(connectionString));
        try
        {
           objConnection.Open();
        }
        catch {}

        if (objConnection != null && objConnection.State == ConnectionState.Open)
        {
            try
            {
               objConnection.Close();
            }
            catch {}
            return true;
        }
        else if (objConnection != null && objConnection.State == ConnectionState.Closed)
        {
            try
            {
               objConnection.Close();
            }
            catch {}
            return false;
        }

edited Apr 24 '16 at 12:31

answered Apr 24 '16 at 11:21

Mostafiz

7,243
3
28
42

1

without open you have no choice to check, and if you try open it and fail then it will definitely take time, because it will try for reconnect then report you that it can't open – Mostafiz Apr 24 '16 at 11:43
how can reduce this time( it will try for reconnect)? Is it possible? – shahroz Apr 24 '16 at 11:46
see my latest update and go through this helpful explanation question http://stackoverflow.com/questions/17853371/most-efficient-way-to-test-sql-connection-string-availibility – Mostafiz Apr 24 '16 at 11:54
@MostafizurRahman, you better close this connection before returning. – Crowcoder Apr 24 '16 at 12:29

score 1 · Accepted Answer · edited Apr 25 '16 at 11:08

If you're connecting to your SQL Server you can try to Ping the server first. On my box a ping only takes 5 seconds to conclude that a server is not reachable. The simplest code that leverages that feature is shown in this snippet:

if(new System.Net.NetworkInformation.Ping().Send("Your servername here").Status != 
       System.Net.NetworkInformation.IPStatus.TimedOut)
 {
    // server reachable, try a real SQL Server connection now
    SqlConnection objConnection = new SqlConnection(connectionstring);
   try
   {
       objConnection.Open(); // this line make wait time if connection not available 
       objConnection.Close();
       // not sure why you would want this
       // only use if you want worse performance
       // SqlConnection.ClearAllPools();
      return true; 
   }
   catch
   {
       return false; 
   }
}
else 
{
    return false; // PING failed
}

System administrators might disable/block ICMP traffic so this option might not work for every server.

score 1 · Answer 3 · answered Apr 24 '16 at 12:31

Do not ClearAllPools! The connection pool is there specifically to make connecting more efficient. When you Open() a connection from the pool is used (if there is one). When you Close() it is returned to the pool but not destroyed, just waiting for someone to use it again.

How to reduce waiting time for open connection?

3 Answers3