C# 批量插入Mysql zt

C# 批量插入Mysql

     string connectString = "server=localhost;user=root;database=dbtest;port=3306;password=;charset=utf8";

 

       public static void batchSql(List<DataObj> list)
        {
            List<string> sqlList = new List<string>();
            List<MySqlParameter[]> paraList = new List<MySqlParameter[]>();

            foreach (DataObj oh in list)
            {
                MySqlParameter[] parameters = new MySqlParameter[19];

                parameters[0] = new MySqlParameter("@param1", MySqlDbType.VarChar, 50);
                parameters[0].Value = oh.param1;
                parameters[1] = new MySqlParameter("@param2", MySqlDbType.VarChar, 50);
                parameters[1].Value = oh.param2;

                sqlList.Add(sqlInsert);
                paraList.Add(parameters);
            }

            ExecuteTransaction(sqlList, paraList);

        }     

 

       /**
         * 執行事務
         * */
        public static void ExecuteTransaction(List<string> sqlList, List<MySqlParameter[]> paraList)
        {
            //Debug.WriteLine(sql);
            using (MySqlConnection connection = new MySqlConnection(connectString))
            {
                MySqlCommand cmd = new MySqlCommand();
                MySqlTransaction transaction = null;
                cmd.Connection = connection;
                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();
                    cmd.Transaction = transaction;

                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        cmd.CommandText = sqlList[i];
                        if (paraList != null && paraList[i] != null)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(paraList[i]);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();

                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {

                    }
                    throw e;
                }
                finally {
                    if(connection != null){
                        connection.Close();
                    }
                }

            }
        }

 

其中DataObj類定義如下：

 

class DataObj{

  string param1;

  string param2;

}