senguang145
森广
2019-04-09 19:05

C# 操作mysql大批量插入语句,一次几万条,可以同时跳过指定的重复字段继续插入。

5
  • c#
  • mysql

我现在用的是这个语句很卡

执行SQL语句

            DataTable dt = (dSkinGridList1.DataSource as DataTable);
            this.label1.Visible = true;
            pidJiazai.Visible = true;
            List<string> SQLStringLis = new List<string>();
            for (int i = 0; i < dt.Rows.Count - 1; i++)
            {
                SQLStringLis.Add("insert ignore into shopsList values('" + dt.Rows[i][0].ToString() + "','','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','0','0','0','" + DateTime.Now.ToString() + "')");
            }

插入执行

        public static bool insertPai(List<string> SQLStringList)
        {
            mysqlClass sqlL = new mysqlClass();
            using (MySqlConnection conn = sqlL.getmysqlcon())
            {
                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].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                        //后来加上的
                        if (n > 0 && (n % 500 == 0 || n == SQLStringList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    return true;
                    //tx.Commit();//原来一次性提交
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

请教更好的,办法,速度更快。这个语句现在插入3000条就卡主了。在线等。谢谢!

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答