C#批量写入MySQL100w条数据

在项目中遇到需要进行对大数据量的表进行查询，但是不在现场，取不到现场数据。所以需要自己来造数据，同时也可以测试不同引擎，不同索引对插入大数据量的操作耗时的影响。

上代码：

using COMS_Bridge;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 数据库
{
    public partial class Form1 : Form
    {
        List<string> SQLStringList = new List<string>();//存放sql语句

        List<int> ChList = new List<int>();
        MysqlPersistance mm = new MysqlPersistance();
        ParamAlarmParam pp = new ParamAlarmParam();
        public delegate void DBhander();

        public Form1()
        {
            InitializeComponent();

        }
        public class ButtonEx : Button
        {
            public ButtonEx()
            {
                SetStyle(ControlStyles.Selectable, false);
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {   BasicDataParam bp = new BasicDataParam();
            BasicData bd = new BasicData();   
            DBhander hander = new DBhander(wei);
            hander.BeginInvoke(new AsyncCallback(callbackp),null);//winform用按钮触发，所以只能用异步操作了，callbackp是回调函数，没加具体方法

        }

        public void wei()
        {
            MysqlPersistance ggg = new MysqlPersistance();
            ggg.deleteTB("basicdata2");
            List<string> paramalarmList = new List<string>();
            DateTime starttime = DateTime.Now;
            for (int i = 1; i <= 1000000; i++)
            {         
                string sql = string.Format("insert into basicdata2  (camID,traficVolume,traficUp,traficDown,carID,detectTime,locateRoad,direction,speed,carHeadway,carSpaceHeadway)values({0},{1},{2},{3},{4},'{5}',{6},{7},{8},{9},{10});select @@Identity as id;",
                                                                             i,
                                                                             i,
                                                                             i,
                                                                             i,
                                                                             i,
                                                                             DateTime.Now,
                                                                             1,
                                                                             1,
                                                                             45,
                                                                             3,
                                                                             3
                                                                                );//数据随意写，满足业务需求就好
                paramalarmList.Add(sql);
                if (i % 2000 == 0)//两千行插入一次，测试过500行的，效果差不多，如果每行数据较多那么就要限制到500行了
                {
                    MysqlHelper.Default.ExecuteNoQueryTranBigDate(paramalarmList);
                    paramalarmList.Clear();
                }
            }
            DateTime endttime = DateTime.Now;
            TimeSpan dif = endttime - starttime;
            MessageBox.Show(dif.ToString());//提醒操作完毕，显示消耗时间



        }
        public void callbackp(IAsyncResult result)//回调函数
        { }
        private void Form1_Load(object sender, EventArgs e)
        {
            //skinEngine1.SkinFile = Application.StartupPath + @"\EmeraldColor1.ssk";
            skinEngine1.SkinFile = Application.StartupPath + @"\MidsummerColor1.ssk";//这是德玛西亚的皮肤，不用搭理
            //skinEngine1.SkinFile = Application.StartupPath + @"\Page.ssk";
	}
}

 public bool ExecuteNoQueryTranBigDate(List<String> SQLStringList)
        {
            makeParam();
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            //PrepareCommand(cmd, conn, tx, strsql, null);
                            cmd.ExecuteNonQuery();
                        }
                        
                        if (n > 0 && (n % 500 == 0 || n == SQLStringList.Count - 1))//如果上面限制了2000，那么这儿就不用限制了。两种方法
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    //tx.Commit();//原来一次性提交  
                    return true;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

 void makeParam()
        {
            connectionString = string.Format("Data Source={0};User ID={1};Password={2};DataBase={3};Charset={4};",
                                                        dbIpAddr, userName, passwd, dbName, charset);
        }//把数据库信息直接赋给这五个参数，生成connectionString 

结果总消耗花时间为：00:04：0924