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.

Have you tried simply `new SqlConnection("... connection string ...");` ? — Lasse V. Karlsen, Dec 29 '16 at 11:12
Also, pinpoint the exact place where you get the exception as changing the connection string property of a SqlConnection object is allowed. — Lasse V. Karlsen, Dec 29 '16 at 11:14
And the code is unnecessary convoluted. You first construct a new SqlConnection object, then you enter a try/catch where you check if the variable is null, the one you just put an object reference into, so it can't be. Then you check if the connection is open, but because it was just constructed it is going to be closed. Then you set the connection string and open it. Can you please simplify the code and pinpoint the exact spot where you get your exception? — Lasse V. Karlsen, Dec 29 '16 at 11:15
@LasseV.Karlsen His code is working fine for one or two query I also checked that.It is throwing exception if he is trying to execute with multiple query. I have tried to give the answer but he is telling this also gives exception. Please check my answer too and let us know what is problem in my answer that is not working for him. — Rajput, Dec 29 '16 at 11:50
What is not working is that he's doing this in a multithreaded application. He should not use the same connection object across multiple threads. — Lasse V. Karlsen, Dec 29 '16 at 11:52
He need to remove code reusibility logic from his code and create a new sqlConnection instance in every thread and close it. Right? — Rajput, Dec 29 '16 at 11:58
For update, i tried with each time new connection object for query execution. And how to separate query execution for no of threads? Is it possible? If yes then how? — Nishit Jani, Dec 29 '16 at 12:17

score 1 · Answer 1 · answered Dec 28 '16 at 12:00

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)
        {
        }

May be not work as there is no major change in code compare to mine. Although will update u after check — Nishit Jani, Dec 28 '16 at 13:06

Rajput · Answer 2 · 2016-12-29T11:07:32.603

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;
        }
    }        
}

edited Dec 29 '16 at 11:07

answered Dec 28 '16 at 13:37

Rajput

2,597
16
29

Write cnOpen(); in second if block after NewConn.Close(); and check if it is Ok for now !! – Rajput Dec 29 '16 at 05:43
Now i get "The ConnectionString property has not been initialized." error. So i modified as below, if (string.IsNullOrEmpty(NewConn.ConnectionString)) NewConn.ConnectionString = Connection string here"; After that i got below erros, 1) Not allowed to change the 'ConnectionString' property. The connection's current state is connecting. 2) SelectCommand.Connection property has not been initialized. 3) ExecuteReader requires an open and available Connection. The connection's current state is open. Please help me out – Nishit Jani Dec 29 '16 at 05:44
Updated my post please check it and let me know. – Rajput Dec 29 '16 at 11:08
Not working. Tried below code as well but no luck. using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand(strQuery, connection); command.CommandType = CommandType.Text; command.CommandTimeout = TimeOut; executeReader = command.ExecuteNonQuery(); } Used try catch here but in comment space issue so not mentioned. – Nishit Jani Dec 29 '16 at 11:18
you can not use connection.open() first always use it just before excecuteReader only. And tell me what was error in my updated code. – Rajput Dec 29 '16 at 11:20
I get error exact at .ExecuteNonQuery(). If yo can, please help me out of this error. – Nishit Jani Dec 29 '16 at 11:22
do you have team viewer – Rajput Dec 29 '16 at 11:23
Yes i have but as per policy i can't share it. Also i clearly mentioned each and every detail. If you want any more detail please ask – Nishit Jani Dec 29 '16 at 11:41
ohh but i ran code in my system and it worked fine. thats why i asked for to connect to teamviewer but let me check once again – Rajput Dec 29 '16 at 11:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131794/discussion-between-rajput-and-nishit-jani). – Rajput Dec 29 '16 at 11:45

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

2 Answers2