C#批量写入MySQL100w条数据

来源：互联网发布：python ipython 编辑：程序博客网时间：2024/05/20 02:25

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

上代码：

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

阅读全文

0 0