c# mysql 的大量数据导入

项目需求， 本来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;
            }
        }
    }
}