使用SqlBulkCopy类加载其他源数据到SQL表

 MSDN摘要:Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。
protected void btnBulkCopy_Click(object sender, EventArgs e)
    {
        String ShajarConString =ConfigurationManager.ConnectionStrings["DSN_Shajar"].ConnectionString;
        String NorthWindConString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
        SqlConnection ShajarCon = new SqlConnection(ShajarConString);
        SqlConnection NorthwindCon = new SqlConnection(NorthWindConString);
        string sql1 = " SELECT ID, First_Name, Last_Name, " +
                                " 'Shajar' as Source FROM MailingList_Temp ";
        SqlCommand ShajarCom = new SqlCommand(sql1,ShajarCon);
        SqlDataReader ShajarReader;
        ShajarCon.Open();

        SqlBulkCopy NorthWindBulkOp= new SqlBulkCopy(NorthWindConString, SqlBulkCopyOptions.UseInternalTransaction);

        NorthWindBulkOp.DestinationTableName = "Employees";

        NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID");
        NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName");
        NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName");
       
        SqlBulkCopyColumnMapping JobTitleColMap= new SqlBulkCopyColumnMapping("Source1", "Title");
        NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap);
        NorthWindBulkOp.BulkCopyTimeout = 500000000;

        NorthWindBulkOp.SqlRowsCopied +=
            new SqlRowsCopiedEventHandler(OnRowsCopied);

        NorthWindBulkOp.NotifyAfter = 1000;

        ShajarReader = ShajarCom.ExecuteReader();

        try
        {
            NorthWindBulkOp.WriteToServer(ShajarReader);
        }
        catch (Exception ex)
        {
            lblResult.Text = ex.Message;
        }
        finally
        {
            ShajarReader.Close();
        }
    }

    private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
    {
        lblCounter.Text += args.RowsCopied.ToString() + " rows are copied<Br>";
    }

使用SqlBulkCopy类进行数据插入其原理是采用了SQL Server的BCP协议进行数据的批量复制。这里我们先要建好一个DataTable（最好是通过DataAdapter来灌数据得到，因为这样出来的DataTable就已经有跟数据表相同的列定义，可以免去之后Mapping Column的步骤），把要插入的数据加进这个DataTable中，然后用SqlBulkCopy的实例来插入到数据库中。经过测试，SqlBulkCopy方法比直接用Sql语句插入数据的效率高出将近25倍。

 

　　另外批量导入SQL、MYSQL等数据是同样的for循环，使用拼出来的sql或者使用参数的方式传递或者使用事务等不同方式的传递效率都不同。如果不使用SqlBulkCopy的方式的话，我测试下来做快递是用一次事务来操作为最快。因为10000次的循环如果是每次提交，那么都有链接和停止数据库的操作，或者说他包含了1000次的小事务处理。如果外面就一个事务的话效率肯定会高。