CODEWITHSUNDEEP
sqlasp.netsql-serverdatabase-connectionwebmethod
WEAPI
WEAPI

Reputation: 101

Is it better to create Single or multiple SQL connection to execute same query multiple time?

I'm executing same command in every 2 seconds. I think following code creates multiple connections:

[System.Web.Services.WebMethod]
public static int getActivity()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString()))
    {
        connection.Open();
        using (var cmd = new SqlCommand("SELECT TOP 1 ValueX FROM TABLE WHERE ID= 2 AND EVENTID = 2 ORDER BY DATE DESC", connection))
        {
            var x = cmd.ExecuteScalar();
            int Result;

            if (x != null)
            {
                Result = int.Parse(x.ToString());
                Console.WriteLine("USER ACTIVITY : " + Result);
            }
            else
            {
                Result = -999;
            }
            connection.Close();
            return Result;
        }
    }
}

If I call this method several time Does following code make multi connection Or single connection ?

 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString()))

Can someone explain whether I need to modify this code or Is this good one ?

Thanks.

Upvotes: 3

Views: 1250

Answers (2)

apomene
apomene

Reputation: 14389

Your method is fine, you just don't need connection.Close() as described by Rahul. Using statement when dealing with SQL objects is good practice.

What you should keep in mind, is that ADO.NET connection pooling, takes care of handling new objects referring to the same connection string, thus minimizing the time needed to open a connection.

More about connection pooling can be found Here

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Since you are using the using statement clause so once you are done with the method the resources are freed and the connection is closed. So everytime when you call the same method a new connection will be made. When you are using the using clause then it is equivalent to the below code:

SqlConnection connection = null;
try
{
    connection = new SqlConnection(connectionString);
}
finally
{
   if(connection != null)
        ((IDisposable)connection).Dispose();
}

Also note that you dont need to explicitly call the connection.Close(); in your method as using statement will take care of it.

Upvotes: 3

Related Questions