Click here to Skip to main content
15,886,799 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
private static string connectionString = @"User Id=****;Password=****;Host=****;Database=****;Persist Security Info=True";

        public MySqlConnection Connection = null;
        public MySqlTransaction Transaction = null;
        private MySqlCommand Command = null;

        public LinqDataContext(string p_ConnectionString = "")
        {
            if (string.IsNullOrWhiteSpace(p_ConnectionString) == false)
            {
                connectionString = p_ConnectionString;
            }
            Connection = new MySqlConnection(connectionString);
        }



C#
public int InsertPayee(System.Nullable<int> p_StoreID, System.Nullable<int> p_PayeeID, string p_PayeeName, string p_StreetAddress, string p_City, System.Nullable<int> p_StateID, string p_ZipCode, string p_PhoneNumber, string p_EmailAddress, string p_Notes, System.Nullable<int> p_CarrierID, System.Nullable<bool> p_IsActive, System.Nullable<int> p_RegisterID)
       {
           using (Connection)
           {
               if (Connection.State != ConnectionState.Open)
               {
                   Connection.Open();
               }

               Command = new MySqlCommand("InsertPayee", Connection);
               Command.CommandType = CommandType.StoredProcedure;
               if (Transaction != null)
               {
                   Command.Transaction = Transaction;
               }

               Command.Parameters.Add(new MySqlParameter("p_StoreID", p_StoreID));
               Command.Parameters.Add(new MySqlParameter("p_PayeeID", p_PayeeID));
               Command.Parameters.Add(new MySqlParameter("p_PayeeName", p_PayeeName));

               Command.Parameters.Add(new MySqlParameter("p_StreetAddress", p_StreetAddress));
               Command.Parameters.Add(new MySqlParameter("p_City", p_City));
               Command.Parameters.Add(new MySqlParameter("p_StateID", p_StateID));

               Command.Parameters.Add(new MySqlParameter("p_ZipCode", p_ZipCode));
               Command.Parameters.Add(new MySqlParameter("p_PhoneNumber", p_PhoneNumber));
               Command.Parameters.Add(new MySqlParameter("p_EmailAddress", p_EmailAddress));

               Command.Parameters.Add(new MySqlParameter("p_Notes", p_Notes));
               Command.Parameters.Add(new MySqlParameter("p_CarrierID", p_CarrierID));
               Command.Parameters.Add(new MySqlParameter("p_IsActive", p_IsActive));

               Command.Parameters.Add(new MySqlParameter("p_RegisterID", p_RegisterID));

               return Command.ExecuteNonQuery();
           }
       }
Posted

Your query is contained inside a using block. When execution leaves the using block, Connection.Dispose() is implicitly called, and Connection.Dispose() will call Connection.Close().

If you wish to keep the connection open, you will need to not use a using block and manually call Dispose() when you no longer require the connection.
 
Share this answer
 
It is because of using(Connection) statement. When you use 'using' statement on IDisposable, it calls 'Dispose' method of that object (in that case Connection) which results in closed connection.
If you don't use 'using' your connection remains in 'Open' state.
For more information:
http://msdn.microsoft.com/en-us//library/yh598w02.aspx[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900