CODEWITHSUNDEEP
c#sql-servermultithreadingstaticado.net
PedrameFaraji
PedrameFaraji

Reputation: 111

Using a common SQL Server connection's between all thread and using at same time

I need every thread to connect to one database and execute some query and maybe some thread in one time can execute the query.

I create a static class for connecting to SQL Server database with ADO.NET:

public static class ADOFire
{
    static System.Data.SqlClient.SqlConnection Cnn = new System.Data.SqlClient.SqlConnection();

    public static string CnnString { get; set; }

    public static void CreateConnection()
    {
        if (Cnn.State == ConnectionState.Open)
            return;

        Cnn.ConnectionString = CnnString = ConfigurationManager.ConnectionStrings["CnnString"].ToString();

        if (Cnn.State != System.Data.ConnectionState.Open) 
            Cnn.Open();
    }

    public static System.Data.DataTable GetTable(System.Data.SqlClient.SqlCommand Com, System.Data.SqlClient.SqlDataAdapter Ada, string ComText)
    {
        CreateConnection();
        Com.Connection = Cnn;
        Ada.SelectCommand = Com;

        try
        {
            System.Data.DataTable T = new System.Data.DataTable();
            Com.CommandText = ComText;
            Ada.Fill(T);
            return T;
        }
        catch { return null; }
    }
}

And in here in each thread I call static function like this:

System.Data.SqlClient.SqlCommand Com = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataAdapter Ada = new System.Data.SqlClient.SqlDataAdapter();
Datatable dt =  ADOFire.GetTable(Com, Ada, "Some Query 'select * from x'");

Based on this link, doesn't make much difference between open a new connection or use from existing connection

Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call

My questions are:

  1. Can a connection serve different threads at the same time? (one connection for all)

  2. Isn't the problem of data clutter due to the static function?

Upvotes: 0

Views: 288

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062855

  1. No, ADO.NET does not two threads accessing the same.conection at the same time
  2. Yes, but it isn't the statix method that is a problem - it is the static connection field that is a huge problem

What you should do is have the static method create (new) and return the connection to the caller, and remove the field completely. Typical usage:

using (var conn = CreateConnection()) {
   //... Use it!
}

I also have grave concerns about: - why you're passing in a command and command text and adapter - the lack of parameters; suggests a huge security problem (SQL injection) - the use of data table (which is almost never the most appropriate tool)

Upvotes: 1

Related Questions