ConnectionString = Null when i used Connection method more than 1 time

Question

i have a problem with my Connection String , i have an Execute method with string parameter to Receive queries ,

public class Create_Connection
{
 public static readonly string CONN_STRING =
 ConfigurationManager.ConnectionStrings["TaskConnectionString"].ConnectionString;
 public static readonly SqlConnection SqlConn = new SqlConnection(CONN_STRING);
 public static readonly SqlConnection CONN = new SqlConnection(CONN_STRING);

  public DataSet ExecuteSql(string sql)
  {
    SqlDataAdapter da;
    DataSet ds;

    if (CONN.State == ConnectionState.Open)
      CONN.Close();
    CONN.Open();
    da = new SqlDataAdapter(sql, CONN_STRING);
    ds = new DataSet();
    da.Fill(ds);
    CONN.Dispose();
    CONN.Close();
    return ds;
   }

 }

when i use it first time it's work will , but when the time of second query comes to use Execute method my program stop and give me this masseg : "The ConnectionString property has not been initialized" !! and the InnerException : " null " !!!

how that possible when it's work in the first time then change when the Conniction String is " Static readonly " !!

and Thanks in advance :) ..

Don't use static connections in ASP.NET. http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren — Tim Schmelter, Oct 24 '13 at 09:25
The reason you don't use static on ASP.NET is because the static property is for all users connecting to your server. so if one user clears the property ,tthis property will be clear to all users — Izikon, Oct 24 '13 at 09:26
why would you want those connection objects in the first place? Seems like you don't use them — evhen14, Oct 24 '13 at 10:47

Ilya · Accepted Answer · 2013-10-24T10:44:09.973

Don't use static SqlCOnnection.

 public class Create_Connection
    {
       public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["TaskConnectionString"].ConnectionString;
       public static readonly SqlConnection SqlConn = new SqlConnection(CONN_STRING);
     //public static readonly SqlConnection CONN = new SqlConnection(CONN_STRING);

        public DataSet ExecuteSql(string sql)
        {
            SqlDataAdapter da;
            DataSet ds;

            using (var CONN = new SqlConnection(CONN_STRING)) {

                //if (CONN.State == ConnectionState.Open)
                //    CONN.Close();
                CONN.Open();
                da = new SqlDataAdapter(sql, CONN_STRING);
                ds = new DataSet();
                da.Fill(ds);
                //CONN.Dispose();
                CONN.Close();
            }
            return ds;
        }
    }

And create variable when you use it. Like this

public class Create_Connection
    {
        public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["TaskConnectionString"].ConnectionString;
        public static readonly SqlConnection SqlConn = new SqlConnection(CONN_STRING);

        public DataSet ExecuteSql(string sql)
        {
            var ds = new DataSet();
            using (var CONN = new SqlConnection(CONN_STRING)) {
                CONN.Open();
                var da = new SqlDataAdapter(sql, CONN_STRING);
                da.Fill(ds);
                CONN.Close();
            }
            return ds;
        }
    }

ConnectionString = Null when i used Connection method more than 1 time

1 Answers1