项目需求, 本来Oracle用得好好的,突然要转成mysql,我也是醉了。
有个导入的问题,查了半天终于得到解决。Oracle中有 OracleBulkCopy,SQL当然也有个SqlBulkCopy .这里有介绍就不说 http://www.cnblogs.com/armyfai/p/3903956.html
可是MySqlBulkCopy并没有这个,网上找了很久,说什么Mysql5.6后就会支持,扯蛋吧,反正我没找到。找到的麻烦留意鄙视我一下,记得附带链接。
后来我就直接找找MySql.Data.MySqlClient这个命名空间有些啥东东,感觉Mysq不会这么傻bb不会出跟Oracle,mssql相对应的方法啊,不然大数据导入的时候怎么搞。终于找到了一个 MySqlBulkLoader,怎么看着跟SqlBulkCopy 有点像。百度一番终于得到答案。
不废话了,直接上代码,应该很容易懂吧
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using System.IO;
public partial class BulkCopy : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ImportMySQL();
}
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()
{
string conn = "Database=apd;Data Source=127.0.0.1;User Id=root;Password=123456;pooling=false;CharSet=utf8;port=3306";
MySqlHelper dbhelp = new MySqlHelper(conn);
DataTable dt = null;
MySql.Data.MySqlClient.MySqlParameter p = new MySql.Data.MySqlClient.MySqlParameter();
//这个dt反正就是自己从数据库查出来,或者是自行组装的,就不多说
dt = dbhelp.ExecuteDataTable("select org_code,org_name from DIM_MANAGE_ORG_V", p); ;
//Adding dummy entries
string connectMySQL = "Server=127.0.0.1;Database=apd;Uid=root;Pwd=123456;";
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(dt, 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;
}
}
}
}