c# - 连接未打开 : MySql BeginTransaction

Question

我正在尝试提交到我的数据库，但在我尝试执行查询时收到一个异常，指出该数据库未打开。谁能解释我在这里做错了什么？ GetConnectionString(DbMap) 肯定会返回正确的字符串，因为我在执行此代码之前使用它来创建表。

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

Answer 1

原因是您没有分配连接(或初始化命令的连接属性)。所以你需要做的是:

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

还有一些事情:

• 您不需要调用 .Close() 来关闭连接使用会为您完成
• 正如 Rob 在评论中提到的，最好为每次迭代使用新命令。
• 过度使用 Parameters.Add instead for AddWithValue ;

参见示例:

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