Correct SqlConnection declaration

Question

The problem is that I have used my SqlConnection as a public static connection, thinking this might be the problem causing form time to time an error :

*the connection is not open or the connection was already open

So is it ok to use one statement of SqlConnection in a static class?

So that I could Declare it only once, I know I could use connectionString in web.config

ConfigurationManager.ConnectionStrings["conn"].ConnectionString ...

but I like it to stay unrelated to web.config settings or servers name.

• ReEdit :

as it is realy two methods within same class theres also another class in that main class but this is not what's important rather than using same connection for all executions ! so you say that even though i re edited with right code of my helprer class this is wrong ?

public static class myDBhelper
{
 public static SqlConnection Conn = new SqlConnection ("server=(local);Initial Catalog=dbName;Integrated Security=True");

        public static int ExecSQLint(string TblintSQL)
        {
            int anIntValue=0;
            SqlCommand TblintCMD = new SqlCommand(TblintSQL, Conn);
            try
            {
                Conn.Open();
                anIntValue = Convert.ToInt32(TblintCMD.ExecuteScalar());
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception("No Can Do: " + ex.Message);
            }
            finally
            {
                Conn.Close();
            }
          return anIntValue;
        }



        public static string ExecSQLstring(string TblStrSQL)
        {
          string strValue="";
          SqlCommand TblStrCMD = new SqlCommand(TblStrSQL, Conn);
            try
            {
                Conn.Open();
                strValue = TblStrCMD.ExecuteScalar().ToString();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception("No Can Do: " + ex.Message);
            }
            finally
            {
                Conn.Close();
            }

            return strValue;
        }


}

The main issue I suspect is those two options :

SqlConnection Conn = new SqlConnection("Data Source=(local);Integrated Security=True;database=dbName")

in my DBhelper class I was using this declaration

SqlConnection Conn = new SqlConnection("server=(local);Initial Catalog=dbName;Integrated Security=True");

could that be unstable or error prone ?

p.s.: I am executing commands via try catch

            try
            {
                Conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception("No Can Do: " + ex.Message);
            }
            finally
            {
                Conn.Close();
            }

Is Using statement more appropriate? Although it is not my problem I was thinking... if I am already trying to do it 'by the book'...

Is any method here actually wrong among those ?

Answer 1

Keeping Connection as static is not a common way to use connection to database. It could lead to exception as you mentioned when application is working on web or multi-thread environment.

Image that thread 1 executing command 1 is the same connection with thread 2 executing command 2. Your ex: TblintCMD and TblStrCMD. When thread 1 finishs, it closed connection, meanwhile thread 2 is still executing command on close connection

Your two options are not the problem.

The best way is to use using keyword and create connection when needed:

using (var connection = new SqlConnection("yourConnectionString"))
{
    connection.Open();
    ...
}

using is similar with:

var connection =  new SqlConnection("connectionString");
try
{
    connection.Open();
    ....
}
finally
{
    connection.Close();
}

So, you don't need to know when to close Connection.

Behind the scene, ADO.NET uses connection pool to manage connections for you automatically, so you should not care much how many connections open.