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