CODEWITHSUNDEEP
c#asp.netmultithreadingsqlconnectionsqlclient
Furkan Gözükara
Furkan Gözükara

Reputation: 23800

Having single SQLConnection across entire asp.net website or having invidiual connections for per DB query

Ok my question requires pretty much expertise on this stuff

I want you to compare these 2 classes. First one opens a new connection for each query while the other one keeps 1 connection open and for other thousands of concurrent queries uses same connection

Now which one is better why ?

What are the cons and pros ?

Asp.net , .net 4.5 website , c#

using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;

public static class DbConnection
{
    public static string srConnectionString = "server=localhost;database=my_DB;uid=sa;pwd=my_PW;";

    public static DataSet db_Select_Query(string strQuery)
    {
        DataSet dSet = new DataSet();
        if (strQuery.Length < 5)
            return dSet;
        try
        {
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                connection.Open();
                using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))
                {
                    DA.Fill(dSet);
                }
            }
            return dSet;
        }catch(Exception){}

        return dSet;
    }
}

And here the single connection across all threads , across entire website

using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;

public static class DbConnection
{
    private static string srConnectionString = "server=localhost;database=my_DB;uid=sa;pwd=my_PW;";

    private static SqlConnection connection = new SqlConnection(srConnectionString);

    static DbConnection()
    {
        connection.Open();
    }

    public static DataSet db_Select_Query(string strQuery)
    {
        DataSet dSet = new DataSet();
        if (strQuery.Length < 5)
            return dSet;
        try
        {
            using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))
            {
                DA.Fill(dSet);
            }

            return dSet;
        }
        catch (Exception) { }

        return dSet;
    }
}

Upvotes: 1

Views: 2150

Answers (1)

Adrian Sanguineti
Adrian Sanguineti

Reputation: 2495

I think what you need to have a read up on is SQL Server Connection Pooling (ADO.NET).

Out of the two options you have given, option 1 is the better as you are disposing the connection as soon as you are done with it. Combine this with Connection Pooling, which is usually enabled in ADO.NET by default, the disposed connection will not actually be closed but returned to the pool so that it can be used again by another request. Therefore you will find that option 1 will far out perform option 2 when it comes to a large amount of concurrent requests.

Upvotes: 1

Related Questions