While more query executing, i got "Not allowed to change the 'ConnectionString' property."

Question

class CommonConnection
{
    public class dStructure
    {
        public static string ConnectionString = "";
    }

    public SqlConnection Conn;
    #region "Connection Procedures"
    public string ConnectionString
    {
        get
        {
            string sConn = string.Empty;
            sConn = @"Server=ServerName;Initial Catalog=Database;User ID=userid;Password=password;";
            dStructure.ConnectionString = sConn;
            return dStructure.ConnectionString;

        }
    }

    public void cnOpen()
    {
        try
        {
            if (Conn == null)
            {
                Conn = new System.Data.SqlClient.SqlConnection();
            }
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
            Conn.ConnectionString = ConnectionString;
            Conn.Open();
        }
        catch (SqlException e)
        {
            SqlConnection.ClearAllPools();
            throw e;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public void cnClose()
    {
        try
        {
            if ((Conn != null))
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            Conn = null;
        }
    }
    #endregion

    public int ExecuteQuery(string strQuery, Int16 TimeOut = 30)
    {
        int RecordsAffected;
        SqlCommand cmd;
        try
        {
            cnOpen();
            cmd = new SqlCommand(strQuery, Conn);
            cmd.CommandTimeout = TimeOut;
            RecordsAffected = cmd.ExecuteNonQuery();

            return RecordsAffected;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cnClose();
            cmd = null;
        }
    }        
}

// Tried another option like below,

    public int ExecuteQuery(string strQuery, short TimeOut = 10)
    {
        SqlConnection NewConn = new SqlConnection();
        try
        {
            if (NewConn == null)
            {
                NewConn = new System.Data.SqlClient.SqlConnection();
            }
            if (NewConn.State == ConnectionState.Open)
            {
                NewConn.Close();
            }
            NewConn.ConnectionString = "Server=ServerName;Initial Catalog=Database;User ID=userid;Password=password;";
            NewConn.Open();

            return new SqlCommand(strQuery, NewConn)
            {
                CommandTimeout = ((int)TimeOut)
            }.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            NewConn.Close();
        }
    }

But still getting same issue. Its desktop application, multi threaded. But while more query load on this, i'm getting Not allowed to change the 'ConnectionString' property. The connection's current state is open. Note that not every time i get this issue, only when more query are executing.

// Update 2 As per suggested in another question, i tried with below code but problem remains same.

    public int ExecuteQuery(string strQuery, short TimeOut = 10)
    {
        int executeReader = 0;
        try
        {
            using (SqlConnection connection = new SqlConnection(@"Server=Server;Initial Catalog=DB;User ID=id;Password=Password;"))
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(strQuery, connection);
                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = TimeOut;
                    executeReader = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return executeReader;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

As suggested there, using command used default IDisposable so no need to close connection.

Answer 1

In both of your code DEMO here is catch.. lets check this.

public void cnOpen()
        {
            try
            {
                if (Conn == null)
                {
                    Conn = new System.Data.SqlClient.SqlConnection();
                }
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                Conn.ConnectionString = ConnectionString;
                Conn.Open();
            }`

Suppose if conn == null then it will go inside block and create new connection and everything is fine. but what if condition would be false, then it won't create new sqlConnection instance and it will move to second if condition

 if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }

Since you told when more query is executing, so it also may happen that state of connection would be anything except open like connecting, fetching, broken, etc so this if condition will be false if any of them happen except ConnectionState.Open and your existing connection will not closed and further it will go to next line where it will encounter

Conn.ConnectionString = ConnectionString;

If your connection is not closed then it will try to change existing connection's (SqlConnection instance) connection string. Which can't be change if instance is not disposed.So it will throw exception.

EDIT Try to do something like this and delete Conn.open() from this block of code.

    if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
    if (Conn == null)
                {
                    Conn = new System.Data.SqlClient.SqlConnection();
                    Conn.ConnectionString = ConnectionString;


                }

and one more thing which you need to update in public int ExecuteQuery(string strQuery, Int16 TimeOut = 30) in method. put this line Conn.Open(); after cmd.CommandTimeout = TimeOut;

public int ExecuteQuery(string strQuery, Int16 TimeOut = 30)
    {
        int RecordsAffected;
        SqlCommand cmd;
        try
        {   
            cnOpen(); 
            cmd = new SqlCommand(strQuery, Conn);
            cmd.CommandTimeout = TimeOut;
            Conn.Open();    //Add this here
            RecordsAffected = cmd.ExecuteNonQuery();

            return RecordsAffected;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cnClose();
            cmd = null;
        }
    }        
}

Answer 2

try this

Class:

 public class CommonConnection
{
    String constr = System.Configuration.ConfigurationManager.ConnectionStrings["myconectionstring"].ConnectionString;
    public CommonConnection()
    {

        //
        // TODO: Add constructor logic here
        //
    }
    //Insert,Update,Delete....
    public int ExecuteNonQuery1(string str)
    {
        //String constr = System.Configuration.ConfigurationManager.ConnectionStrings["CommonConnection"].ConnectionString;

        SqlConnection con = new SqlConnection(constr);

        SqlCommand cmd = new SqlCommand(str, con);


        int result = 0;

        try
        {

            con.Open();
            result = cmd.ExecuteNonQuery();


            con.Close();
        }
        catch (Exception ex)
        {
            result = -1;
            try
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            catch (Exception ex2)
            {
                // ErrHandler.WriteError(ex2.ToString());
            }
            // ErrHandler.WriteError(ex.ToString());
        }

        return result;

    }
}

ASPX.CS:

        SortedList s1 = new SortedList();
        s1.Add("@mode", "Update");
        s1.Add("@cid", ViewState["CategoryId"]);
        int a = sp.ExecuteNonQuerySP1("SP_Name", s1);
        if (a > 0)
        {
        }