单次批量复制操作

来源：互联网发布：.net跨平台 java 编辑：程序博客网时间：2024/06/05 08:48

注：此文从msdn抄过来的。

执行批量复制操作的一般步骤如下所示：

连接到源服务器上并获取要复制的数据。如果可以从 IDataReader 或 DataTable 对象检索数据，则这些数据还可能来自其他源。
连接到目标服务器（除非您希望 SqlBulkCopy 为您建立连接）。
创建一个 SqlBulkCopy 对象，设置任何必要的属性。
设置 DestinationTableName 属性以指示执行批量插入操作的目标表。
调用一个 WriteToServer 方法。
可以选择更新属性并根据需要再次调用 WriteToServer。
调用 Close，或将批量复制操作包装在 Using 语句。

代码如下：

using System.Data.SqlClient;class Program{    static void Main()    {        string connectionString = GetConnectionString();        // Open a sourceConnection to the AdventureWorks database.        using (SqlConnection sourceConnection =                   new SqlConnection(connectionString))        {            sourceConnection.Open();            // Perform an initial count on the destination table.            SqlCommand commandRowCount = new SqlCommand(                "SELECT COUNT(*) FROM " +                "dbo.BulkCopyDemoMatchingColumns;",                sourceConnection);            long countStart = System.Convert.ToInt32(                commandRowCount.ExecuteScalar());            Console.WriteLine("Starting row count = {0}", countStart);            // Get data from the source table as a SqlDataReader.            SqlCommand commandSourceData = new SqlCommand(                "SELECT ProductID, Name, " +                "ProductNumber " +                "FROM Production.Product;", sourceConnection);            SqlDataReader reader =                commandSourceData.ExecuteReader();            // Open the destination connection. In the real world you would             // not use SqlBulkCopy to move data from one table to the other             // in the same database. This is for demonstration purposes only.            using (SqlConnection destinationConnection =                       new SqlConnection(connectionString))            {                destinationConnection.Open();                // Set up the bulk copy object.                 // Note that the column positions in the source                // data reader match the column positions in                 // the destination table so there is no need to                // map columns.                using (SqlBulkCopy bulkCopy =                           new SqlBulkCopy(destinationConnection))                {                    bulkCopy.DestinationTableName =                        "dbo.BulkCopyDemoMatchingColumns";                    try                    {                        // Write from the source to the destination.                        bulkCopy.WriteToServer(reader);                    }                    catch (Exception ex)                    {                        Console.WriteLine(ex.Message);                    }                    finally                    {                        // Close the SqlDataReader. The SqlBulkCopy                        // object is automatically closed at the end                        // of the using block.                        reader.Close();                    }                }                // Perform a final count on the destination                 // table to see how many rows were added.                long countEnd = System.Convert.ToInt32(                    commandRowCount.ExecuteScalar());                Console.WriteLine("Ending row count = {0}", countEnd);                Console.WriteLine("{0} rows were added.", countEnd - countStart);                Console.WriteLine("Press Enter to finish.");                Console.ReadLine();            }        }    }    private static string GetConnectionString()        // To avoid storing the sourceConnection string in your code,         // you can retrieve it from a configuration file.     {        return "Data Source=(local); " +            " Integrated Security=true;" +            "Initial Catalog=AdventureWorks;";    }}