CODEWITHSUNDEEP
c#database-connection
shahroz
shahroz

Reputation: 369

How to reduce waiting time for open connection?

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?

Upvotes: 0

Views: 1491

Answers (3)

rene
rene

Reputation: 42414

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.

Upvotes: 1

Crowcoder
Crowcoder

Reputation: 11514

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.

Upvotes: 1

Mostafiz
Mostafiz

Reputation: 7352

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;
        }

Upvotes: 1

Related Questions