首页 > 代码库 > SqlBulkCopy 快速插入数据

SqlBulkCopy 快速插入数据

[转]本文来自http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy(v=vs.80).aspx
此代码用于演示仅使用 SqlBulkCopy 的语法。如果源表和目标表都在同一个 SQL Server 实例中,则使用 Transact-SQL INSERT … SELECT 语句复制数据会更方便快捷。
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 = http://www.mamicode.com/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;";    }}

  

SqlBulkCopy 快速插入数据