The connection is not open: MySql BeginTransaction

Question

I am trying to commit to my database and I am receiving an exception stating that is not open when I try to Execute Query. Can anyone explain what I am doing wrong here? GetConnectionString(DbMap) definitely does return the correct string as I am using it to create tables prior to this code execution.

public static void InsertDataTable(DataTable dt)
{
   try
   {
      //Open the SQL Connection
      using (var dbConnection = new MySqlConnection(GetConnectionString(DbMap)))
            {
                dbConnection.Open();
                //Instantiate the Command
                using (var cmd = new MySqlCommand())
                {
                    //Create a new Transaction
                    using (var transaction = dbConnection.BeginTransaction())
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //var identifier = dt.Rows[i].Field<int>("Identifier");
                            var entry = dt.Rows[i].Field<uint>("Entry");
                            var name = dt.Rows[i].Field<string>("Name");
                            var zone = dt.Rows[i].Field<uint>("Zone");
                            var type = dt.Rows[i].Field<ObjectType>("Type");

                            //Add data value with Parameters.
                            cmd.Parameters.AddWithValue("@Entry", entry);
                            cmd.Parameters.AddWithValue("@Name", name);
                            cmd.Parameters.AddWithValue("@Type", type);

                            //Create command to execute the insertion of Data into desired Table
                            string dataTableName = "zone_" + zone;
                            cmd.CommandText = $"INSERT INTO [{dataTableName}] " +
                                              "([entry], [name], [type]) " +
                                              "VALUES (@Entry, @Name, @Type)";

                            cmd.ExecuteNonQuery();
                        } //for (int i = 0; i < dt.Rows.Count; i++)

                        //Commit the Transaction
                        transaction.Commit();
                    } //using (var transaction = dbConnection.BeginTransaction())
                } //using (var cmd = new MySqlCommand())

                //Close the Connection
                dbConnection.Close();
            }
     }
     catch (MySqlException ex)
     {
         Logger.Write("InsertDataTable | MySqlException: " + ex);
     }
     catch (Exception ex)
     {
         Logger.Write("InsertDataTable | Exception: " + ex);
     }
 }

You should create a new command for each row. `using (var cmd =` should be inside the loop. Also, and I don't use MySql often with C#, but I don't see any link between your command and the connection. Are you sure it shouldn't be `new MySqlCommand(dbConnection)`? — Rob, Apr 12 '16 at 00:45
new MySqlCommand(dbConnection) won't work as it needs command text which isn't formed until we are in the for loop. — , Apr 12 '16 at 01:21

sujith karivelil · Accepted Answer · 2016-04-12T01:36:28.127

The reason is that you have not assign the connection(or initialize the connection property of the command). So what you need to do is that:

//Code here
cmd.Connection=dbConnection;
cmd.CommandType = CommandType.Text;
cmd.Connection = dbConnection; 
//code here;

Few more things:

You need not to call .Close() to close the connection using will do it for you
As Rob Mentioned in the comment its better to use a new command for each iteration.
More over use Parameters.Add instead for AddWithValue;

See Example:

using (var transaction = dbConnection.BeginTransaction())
{
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        var entry = dt.Rows[i].Field<uint>("Entry");
        var name = dt.Rows[i].Field<string>("Name");
        var zone = dt.Rows[i].Field<uint>("Zone");
        var type = dt.Rows[i].Field<object>("Type");
        using (var cmd = new MySqlCommand())
        {
            cmd.Parameters.Add("@Entry", MySqlDbType.UInt32).Value = entry;
            cmd.Parameters.Add("@Name", MySqlDbType.VarString);
            cmd.Parameters.Add("@Type", MySqlDbType.Blob).Value=type; //choose the type correctly
            string dataTableName = "zone_" + zone;
            cmd.CommandText = @"INSERT INTO [{dataTableName}] " +
                              "([entry], [name], [type]) " +
                              "VALUES (@Entry, @Name, @Type)";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = dbConnection; 
            cmd.ExecuteNonQuery();
        }
    }                      
    transaction.Commit();
}

When I use with Add instead of AddWithValue I get a deprecation warning from VS2015 as it is telling me to use AddWithValue See http://stackoverflow.com/questions/13580993/mysqlcommand-command-parameters-add-is-obsolete — , Apr 12 '16 at 01:35

The connection is not open: MySql BeginTransaction

1 Answers1