Mysql 批量插入数据的方法

时间：2022-03-14 04:04

使用的方式是 MySqlBulkLoader

方法如下:

1. 转化datatable 为文件

2. 使用MySqlBulkLoader 进行数据的加载

代码：

public static void CreateCSVfile(DataTable dtable, string strFilePath) { StreamWriter sw = new StreamWriter(strFilePath, false); int icolcount = dtable.Columns.Count; foreach (DataRow drow in dtable.Rows) { for (int i = 0; i < icolcount; i++) { if (!Convert.IsDBNull(drow[i])) { sw.Write(drow[i].ToString()); } if (i < icolcount - 1) { sw.Write(","); } } sw.Write(sw.NewLine); } sw.Close(); sw.Dispose(); } private void ImportMySQL() { DataTable orderDetail = new DataTable("ItemDetail"); DataColumn c = new DataColumn(); // always orderDetail.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("value", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("length", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("breadth", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("total", Type.GetType("System.Decimal"))); orderDetail.Columns["total"].Expression = "value/(length*breadth)"; //Adding dummy entries DataRow dr = orderDetail.NewRow(); dr["ID"] = 1; dr["value"] = 50; dr["length"] = 5; dr["breadth"] = 8; orderDetail.Rows.Add(dr); dr = orderDetail.NewRow(); dr["ID"] = 2; dr["value"] = 60; dr["length"] = 15; dr["breadth"] = 18; orderDetail.Rows.Add(dr); //Adding dummy entries string connectMySQL = "Server=localhost;Database=test;Uid=username;Pwd=password;"; string strFile = "/TempFolder/MySQL" + DateTime.Now.Ticks.ToString() + ".csv"; //Create directory if not exist... Make sure directory has required rights.. if (!Directory.Exists(Server.MapPath("~/TempFolder/"))) Directory.CreateDirectory(Server.MapPath("~/TempFolder/")); //If file does not exist then create it and right data into it.. if (!File.Exists(Server.MapPath(strFile))) { FileStream fs = new FileStream(Server.MapPath(strFile), FileMode.Create, FileAccess.Write); fs.Close(); fs.Dispose(); } //Generate csv file from where data read CreateCSVfile(orderDetail, Server.MapPath(strFile)); using (MySqlConnection cn1 = new MySqlConnection(connectMySQL)) { cn1.Open(); MySqlBulkLoader bcp1 = new MySqlBulkLoader(cn1); bcp1.TableName = "productorder"; //Create ProductOrder table into MYSQL database... bcp1.FieldTerminator = ","; bcp1.LineTerminator = "\r\n"; bcp1.FileName = Server.MapPath(strFile); bcp1.NumberOfLinesToSkip = 0; bcp1.Load(); //Once data write into db then delete file.. try { File.Delete(Server.MapPath(strFile)); } catch (Exception ex) { string str = ex.Message; } } }