SqlBulkCopy 类

来源：互联网发布：浙江中医院事件知乎编辑：程序博客网时间：2024/06/05 08:32

使用此类可以

使您可以用其他源的数据有效批量加载 SQL Server 表。

命名空间:System.Data.SqlClient
程序集:System.Data（在 system.data.dll 中）

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

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用IDataReader 实例读取数据。

实例

下面的控制台应用程序演示如何使用 SqlBulkCopy 类加载数据。在此示例中，使用 SqlDataReader 将数据从 SQL Server 2005 AdventureWorks 数据库中的Production.Product 表复制到同一数据库中的相似表。

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;";    }}

0 0