Mysql 批量插入数据的方法

浏览数：17 / 时间：2015年06月12日

使用的方式是 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;
    }
    }
}