多线程的SqlBulkCopy批量导入、事务和SqlBulkCopy使用的数据集中自定义映射字段的注意事项 [转自360doc]

class Program   
   {   
       static volatile bool result;   
       static void Main(string[] args)   
       {   
          DataSet ds = ExportDataSet();   

          //使用2个线程模拟并发操作   
          Thread t = new Thread(delegate() { result = Insert(ds); Console.WriteLine(result ? "导入成功" : "导入失败"); });   
          t.Start();   
          Thread t1 = new Thread(delegate() { result = Insert(ds); Console.WriteLine(result ? "导入成功" : "导入失败"); });   
          t1.Start();   
          Console.ReadLine();   
       }   

       /// <summary>   
       /// 获取数据DataSet   
       /// </summary>   
       /// <returns></returns>   

       static private DataSet ExportDataSet()   
       {   

           //局域网的某服务器模拟数据库远程连接   
           SqlConnection RemoteConn = new SqlConnection("Data Source=192.168.0.183;Initial Catalog=Northwind;User ID=sa;Password=sa");   



           using (   

            /*目标表与源表结构并不相同，目标表只包含OrderID、CustomerID、EmployeeID、ShipCountry这四个字段。注意这里字段是区分大小写的，不然SqlBulkCopy的WriteToServer方法会报运行时异常：“给定的 ColumnMapping 与源或目标中的任意列均不匹配”的处理方法。这个地方浪费了我1个小时才发现*/ 
            SqlDataAdapter oda = new SqlDataAdapter("SELECT [OrderID], [CustomerID], [EmployeeID], [ShipCountry] FROM [Northwind].[dbo].[Orders]", RemoteConn))   

           //如果目标表与源表结构完全一致，则用下面语句即可，msdn的例子也只是这样   

            //SqlDataAdapter oda = new SqlDataAdapter("SELECT * FROM [Ednoland].[dbo].[Score]", RemoteConn))   
           {   
               DataSet ds = new DataSet();   

               oda.Fill(ds, "Orders");//给定表名   

                 return ds;   

           }   

       }   

       /// <summary>   
       /// 将DataSet导入远程数据库(未来放在WebService中)   
       /// </summary>   
       /// <param name="ds"></param>   
       /// <returns></returns>   
       public static bool Insert(DataSet ds)   
       {   


           using (SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=sa"))   
           {   
               sqlconn.Open();   
               SqlTransaction sqlbulkTransaction = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);   



               using (SqlBulkCopy sbc = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.KeepIdentity, sqlbulkTransaction))   
               {   
                   sbc.BatchSize = 20000;//20000行每连接   
                   sbc.BulkCopyTimeout = 50;//50秒超时   
                   if (ds.Tables == null || ds.Tables.Count == 0)   
                       return false;   

                   if (ds.Tables.Count == 1)   
                   {   


                       return BulkInsert(sbc, ds.Tables[0], sqlbulkTransaction); ;   

                   }   
                   else 
                   {   
                       bool res = true;   
                       foreach (DataTable dt in ds.Tables)   
                       {   

                           res = BulkInsert(sbc, dt, sqlbulkTransaction);   

                       }   
                       return res;   
                   }   

               }   
           }   

       }   

       private static bool BulkInsert(SqlBulkCopy sbc, DataTable dt, SqlTransaction sqlbulkTransaction)   
       {   
           
           bool res = true;   
                      try 
           {   

             //将DataTable表名作为待导入库中的目标表名   
               sbc.DestinationTableName = dt.TableName;   



              //将数据集合和目标服务器库表中的字段对应   
               for (int i = 0; i < dt.Columns.Count; i++)   
               {   
                   //sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);   
                     sbc.ColumnMappings.Add(i,i);//可以避免字段大小写不一致造成无法映射的问题   
               }   

               sbc.WriteToServer(dt);   

               //提交事务   
               sqlbulkTransaction.Commit();   
               res = true;   
           }   
           catch (SqlException ex)   
           {   
               res = false;   
               sqlbulkTransaction.Rollback();   
           }   

           return res;   
       }   
   } 

 调试结果：

导入成功

导入失败

由于设置SqlBulkCopyOptions.KeepIdentity，保证了源数据与目标数据的标识列同步，再次导入相同数据会引发数据库异常，所以第二次执行插入方法会输出“导入失败”。这样可以避免并发的重复导入。

SqlBulkCopyOptions详见：http://msdn.microsoft.com/zh-cn/vbasic/system.data.sqlclient.sqlbulkcopyoptions.aspx

 

 

protected void Button1_Click(object sender, EventArgs e)
        {

            DateTime beginTime = DateTime.Now;
            Response.Write("开始时间：" + beginTime.ToString("yyyy年MM月dd日：HH:mm:ss:fff"));

            //构造一个Datatable存储将要批量导入的数据
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(string));
            dt.Columns.Add("name", typeof(string));

            // 见识下SqlBulkCopy强悍之处，来个十万条数数据试验
            int i;
            for (i = 0; i < 100000; i++)
            {
                DataRow dr = dt.NewRow();
                dr["name"] = i.ToString();
                dt.Rows.Add(dr);
            }

            string str = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString.ToString();
            //声明数据库连接
            SqlConnection conn = new SqlConnection(str);

            conn.Open();
            //声明SqlBulkCopy ,using释放非托管资源
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
            {
                //一次批量的插入的数据量
                sqlBC.BatchSize = 1000;
                //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                sqlBC.BulkCopyTimeout = 60;

                //設定 NotifyAfter 属性，以便在每插入10000 条数据时，呼叫相应事件。  
                sqlBC.NotifyAfter = 10000;
                sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                //设置要批量写入的表
                sqlBC.DestinationTableName = "dbo.text";

                //自定义的datatable和数据库的字段进行对应
                sqlBC.ColumnMappings.Add("id", "tel");
                sqlBC.ColumnMappings.Add("name", "neirong");

                //批量写入
                sqlBC.WriteToServer(dt);
            }
            conn.Dispose();
            Response.Write("<br/>");



            DateTime endTime = DateTime.Now;
            Response.Write("结束时间：" + endTime.ToString("yyyy年MM月dd日：HH:mm:ss:fff"));
            TimeSpan useTime = endTime - beginTime;//使用时间
            Response.Write("<br/>插入时间：" + useTime.TotalSeconds.ToString() + "秒");

        }
        //响应时事件
        void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Response.Write("<br/> OK! ");
        } 

 

///##################MySqlBulkLoader

MySQL Connector/Net features a bulk loader class that wraps the MySQL statement LOAD DATA INFILE. This gives MySQL Connector/Net the ability to load a data file from a local or remote host to the server. The class concerned is MySqlBulkLoader. This class has various methods, the main one being load to cause the specified file to be loaded to the server. Various parameters can be set to control how the data file is processed. This is achieved through setting various properties of the class. For example, the field separator used, such as comma or tab, can be specified, along with the record terminator, such as newline.

The following code shows a simple example of using the MySqlBulkLoader class. First an empty table needs to be created, in this case in the test database:

CREATE TABLE Career (
       Name VARCHAR(100) NOT NULL,
       Age INTEGER,
       Profession VARCHAR(200)
);

A simple tab-delimited data file is also created (it could use any other field delimiter such as comma):

Table Career in Test Database
Name Age Profession

Tony 47 Technical Writer
Ana 43 Nurse
Fred 21 IT Specialist
Simon 45 Hairy Biker

 

Note that with this test file the first three lines will need to be ignored, as they do not contain table data. This can be achieved using the NumberOfLinesToSkip property. This file can then be loaded and used to populate the Career table in the test database:

using System;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            string connStr = "server=localhost;user=root;database=test;port=3306;password=******;";
            MySqlConnection conn = new MySqlConnection(connStr);

            MySqlBulkLoader bl = new MySqlBulkLoader(conn);
            bl.TableName = "Career";
            bl.FieldTerminator = "\t";
            bl.LineTerminator = "\n";
            bl.FileName = "c:/career_data.txt";
            bl.NumberOfLinesToSkip = 3;

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                // Upload data from file
                int count = bl.Load();
                Console.WriteLine(count + " lines uploaded.");

                string sql = "SELECT Name, Age, Profession FROM Career";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0] + " -- " + rdr[1] + " -- " + rdr[2]);
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
        }
    }
}