多次批量复制操作

注：抄至msdn

      可以使用 SqlBulkCopy 类的单个实例执行多次批量复制操作。 如果在两次复制之间更改了操作参数（例如目标表的名称），

必须先更新这些参数，然后再进行对任何 WriteToServer方法的后续调用，如下例中所示。 除非显式更改，

否则，所有属性值都将与给定实例的上一次批量复制操作相同。

代码如下：

using System.Data.SqlClient;class Program{    static void Main()    {        string connectionString = GetConnectionString();        // Open a connection to the AdventureWorks database.        using (SqlConnection connection =                   new SqlConnection(connectionString))        {            connection.Open();            // Empty the destination tables.             SqlCommand deleteHeader = new SqlCommand(                "DELETE FROM dbo.BulkCopyDemoOrderHeader;",                connection);            deleteHeader.ExecuteNonQuery();            SqlCommand deleteDetail = new SqlCommand(                "DELETE FROM dbo.BulkCopyDemoOrderDetail;",                connection);            deleteDetail.ExecuteNonQuery();            // Perform an initial count on the destination            //  table with matching columns.             SqlCommand countRowHeader = new SqlCommand(                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;",                connection);            long countStartHeader = System.Convert.ToInt32(                countRowHeader.ExecuteScalar());            Console.WriteLine(                "Starting row count for Header table = {0}",                countStartHeader);            // Perform an initial count on the destination            // table with different column positions.             SqlCommand countRowDetail = new SqlCommand(                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;",                connection);            long countStartDetail = System.Convert.ToInt32(                countRowDetail.ExecuteScalar());            Console.WriteLine(                "Starting row count for Detail table = {0}",                countStartDetail);            // Get data from the source table as a SqlDataReader.            // The Sales.SalesOrderHeader and Sales.SalesOrderDetail            // tables are quite large and could easily cause a timeout            // if all data from the tables is added to the destination.             // To keep the example simple and quick, a parameter is              // used to select only orders for a particular account             // as the source for the bulk insert.             SqlCommand headerData = new SqlCommand(                "SELECT [SalesOrderID], [OrderDate], " +                "[AccountNumber] FROM [Sales].[SalesOrderHeader] " +                "WHERE [AccountNumber] = @accountNumber;",                connection);            SqlParameter parameterAccount = new SqlParameter();            parameterAccount.ParameterName = "@accountNumber";            parameterAccount.SqlDbType = SqlDbType.NVarChar;            parameterAccount.Direction = ParameterDirection.Input;            parameterAccount.Value = "10-4020-000034";            headerData.Parameters.Add(parameterAccount);            SqlDataReader readerHeader = headerData.ExecuteReader();            // Get the Detail data in a separate connection.            using (SqlConnection connection2 = new SqlConnection(connectionString))            {                connection2.Open();                SqlCommand sourceDetailData = new SqlCommand(                    "SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID], " +                    "[OrderQty], [ProductID], [UnitPrice] FROM [Sales].[SalesOrderDetail] " +                    "INNER JOIN [Sales].[SalesOrderHeader] ON [Sales].[SalesOrderDetail]." +                    "[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] " +                    "WHERE [AccountNumber] = @accountNumber;", connection2);                SqlParameter accountDetail = new SqlParameter();                accountDetail.ParameterName = "@accountNumber";                accountDetail.SqlDbType = SqlDbType.NVarChar;                accountDetail.Direction = ParameterDirection.Input;                accountDetail.Value = "10-4020-000034";                sourceDetailData.Parameters.Add(accountDetail);                SqlDataReader readerDetail = sourceDetailData.ExecuteReader();                // Create the SqlBulkCopy object.                 using (SqlBulkCopy bulkCopy =                           new SqlBulkCopy(connectionString))                {                    bulkCopy.DestinationTableName =                        "dbo.BulkCopyDemoOrderHeader";                    // Guarantee that columns are mapped correctly by                    // defining the column mappings for the order.                    bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");                    bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");                    bulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber");                    // Write readerHeader to the destination.                    try                    {                        bulkCopy.WriteToServer(readerHeader);                    }                    catch (Exception ex)                    {                        Console.WriteLine(ex.Message);                    }                    finally                    {                        readerHeader.Close();                    }                    // Set up the order details destination.                     bulkCopy.DestinationTableName ="dbo.BulkCopyDemoOrderDetail";                    // Clear the ColumnMappingCollection.                    bulkCopy.ColumnMappings.Clear();                    // Add order detail column mappings.                    bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");                    bulkCopy.ColumnMappings.Add("SalesOrderDetailID", "SalesOrderDetailID");                    bulkCopy.ColumnMappings.Add("OrderQty", "OrderQty");                    bulkCopy.ColumnMappings.Add("ProductID", "ProductID");                    bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice");                    // Write readerDetail to the destination.                    try                    {                        bulkCopy.WriteToServer(readerDetail);                    }                    catch (Exception ex)                    {                        Console.WriteLine(ex.Message);                    }                    finally                    {                        readerDetail.Close();                    }                }                // Perform a final count on the destination                // tables to see how many rows were added.                 long countEndHeader = System.Convert.ToInt32(                    countRowHeader.ExecuteScalar());                Console.WriteLine("{0} rows were added to the Header table.",                    countEndHeader - countStartHeader);                long countEndDetail = System.Convert.ToInt32(                    countRowDetail.ExecuteScalar());                Console.WriteLine("{0} rows were added to the Detail table.",                    countEndDetail - countStartDetail);                Console.WriteLine("Press Enter to finish.");                Console.ReadLine();            }        }    }    private static string GetConnectionString()        // To avoid storing the connection string in your code,         // you can retrieve it from a configuration file.     {        return "Data Source=(local); " +            " Integrated Security=true;" +            "Initial Catalog=AdventureWorks;";    }}