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