c# - 将批量数据行传递给存储过程

Question

我正在通过 C# 读取电子表格文件...我的电子表格有 1000 多行。我需要将每一行数据发送到存储过程以执行一些数据库端逻辑和更新记录。我需要一些帮助来一次发送所有这 1000 行数据以节省时间。将所有这 1000 行数据分组需要什么技术。

Answer 1

假设您使用的是 SQL Server 2008 或更高版本，您有几个选择。所有这些选项都在 2010 年新奥尔良 Tech-Ed 和 video of the session is available online 上进行了详细介绍。 .以下是所提供选项的摘要。

选项 #1 - 批量插入(未真正涵盖在 video 中)

如果您只需要将数据“转储”到表中并且除了将数据存入数据库外不需要对数据做太多操作，这是一个很好的选择。 ADO.NET 也支持使用 SqlBulkCopy目的。我也写了一个lightweight wrapper you can find on CodePlex使使用 SQL Server 和 ADO.NET 更容易

选项 #2 - 传递分隔列表

获取所有数据并构建一个大字符串并将整个字符串传递到存储过程中。这出奇地快，但带来了很多负担。您必须有一个拆分函数来获取数据并获得最佳性能，您需要使用 SQL-CLR 进行拆分，如果您不拥有数据库，这可能会成为阻碍。

选项 #3 - 作为 XML 传递

这与选项 #2 几乎相同，因为您再次将一个巨大的字符串传递给一个参数。这也具有合理的性能，但也有许多与选项 #2 相同的包袱，但没有拆分功能，因为 Sql Server 知道如何解析 XML。

选项 #4 - 使用表值函数 (SQL Server 2008)

这个选项非常酷，可以提供最佳性能。首先在 SQL Server 中创建一个“表”类型的值类型，然后创建一个将该值类型作为参数的存储过程。在 C# 中，您现在可以创建一个 SqlCommand 并添加一个类型为 SqlDbType.Structured 的参数。

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spTVP";
var p = cmd.Parameters.Add("@Values", SqlDbType.Structured);
p.TypeName = "Test.OrderTableType";
p.Value = dataTable;
cmd.Execute…;

当执行存储过程时，存储过程的表变量中的所有数据都可用。它可以像任何其他表变量一样使用，因此移动数据非常简单。

选项 #5 - 使用流表值函数 (SQL Server 2008)

比选项 #4 多一点工作，因为您必须设置一个迭代器，但您会从中获得一些疯狂的性能，因为您不必在将所有数据传递到存储过程之前将其加载到客户端上。 .NET 运行时实际上会将数据流式传输到数据库中，存储过程的实现是相同的。

class MyStreamingTvp : IEnumerable<SqlDataRecord> { …
}
…
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spTVP";
var p = cmd.Parameters.Add("@Values", SqlDbType.Structured);
p.TypeName = "Test.OrderTableType";
p.Value = new MyStreamingTvp(…);
cmd.Execute…;

所有这些选项都在 video 中以非常详细和幽默的方式进行了介绍。我一开始就提到了。这是今年 Tech-Ed 上我最喜欢的类(class)之一。