将C#表格数据插入SQL Server表格

Question

有没有办法使用存储过程并将System.Data.DataTable作为参数传递来批量插入C#中的数据表到SQL Server表中？

该表不会有固定数量的记录。

- D_D

---

哪个版本的SQL Server？2008？ - Florian Reischl

Answer 1

是的，有一种方法：

        DataTable dataTable = null; // your data needs to be here
        try
        {
            ConnectionStringSettings mConString = ConfigurationManager.ConnectionStrings["SiteSqlServer"];

            // Optional truncating old table
            using (SqlConnection connection = new SqlConnection(mConString.ConnectionString))
            {
                connection.Open();
                // Delete old entries
                SqlCommand truncate = new SqlCommand("TRUNCATE TABLE MYTABLE", connection);
                truncate.ExecuteNonQuery();
            }

            SqlBulkCopy bulkCopy = new SqlBulkCopy(mConString.ConnectionString, SqlBulkCopyOptions.TableLock)
                                          {
                                              DestinationTableName = "dbo.MYTABLE",
                                              BatchSize = 100000,
                                              BulkCopyTimeout = 360
                                          };
            bulkCopy.WriteToServer(dataTable);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }

请尝试使用BatchSize进行实验-对我来说，100000是很好的值-不应该超过这个大小-否则速度可能会降低。BatchSize并不限制您的数据-它只是将发送到SQL服务器的每个“数据包”的大小。

SiteSQLServer应该在您的app.config或web.config中。如果没有，请在此更改参数。

请将MYTABLE更改为您的表名。

- Andreas Rehm

---

Andread，这很好，但如果你有一个固执的DBA不给予Bulk Insert权限...那怎么办？ - MethodMan

这样是行不通的。但这不是问题所在...批量插入应该是启用的，以便使用此功能，而且这是默认设置。 - Andreas Rehm

@MethodMan 你可以将 DataTable 作为 存储过程参数（用户定义的表类型）进行传递。但是你会失去 SqlBulkCopy 的 EnableStreaming 特性优势。 - Muflix

Answer 2

在SQL Server 2008之前的版本中，将整个DataTable推送到SQL Server的唯一方法是使用SqlBulkCopy。您需要将所有数据推送到（可能是临时的）暂存表中，并调用过程以处理暂存数据。

在SQL Server 2008中，引入了表值用户定义类型。它们提供了一些很好的新选项来处理数据集。

请查看MSDN：

• SQL Server 2008中的表值参数

还可以查看我的两篇博客文章：

• 表值参数
• 表值参数-性能比较

- Florian Reischl

---

当然，速度会慢一些（但不会太慢），但更加强大，因为不需要分阶段表。 - Florian Reischl

你好Florian，我试图运行类似于以下内容的代码，但它无法正常工作...USE AdventureWorks; GO/* 创建用户定义的表类型 */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO我在我的SQL Server中有AdventureWorks数据库，我不应该能够创建新类型吗？但是我收到了以下错误... Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'AS'.谢谢 - D_D

请尝试运行命令："PRINT @@VERSION;"。它返回的是 SQL Server 2008 还是其他版本？如果是 2008，请检查数据库属性的兼容性模式版本是否为“10”。 - Florian Reischl

好的，我们正在使用2005年版本...所以我将不得不使用另一种解决方案，对吧？SQLBulkCopy... - D_D

所以，SqlBulkCopy是最好的选择。 - Florian Reischl

暂存表（包含导入数据的表）是很有用的（无论是通过sqlbulkcopy还是过程上传），因为当导入（后续转换和处理）失败时，您可能希望查看原始数据。 - Muflix