什么是触发存储过程 368 次来更新数据库的好选择? [英] What's a good alternative to firing a stored procedure 368 times to update the database?

问题描述

我正在开发一个 .NET 组件，该组件从数据库中获取一组数据，对该组数据执行一些业务逻辑，然后通过类似于 spUpdateOrderDetailDiscountedItem.

I'm working on a .NET component that gets a set of data from the database, performs some business logic on that set of data, and then updates single records in the database via a stored procedure that looks something like spUpdateOrderDetailDiscountedItem.

对于小数据集，这不是问题，但是当我有一个非常大的数据集需要 368 个存储过程调用的迭代来更新数据库中的记录时，我意识到我遇到了问题.一位高级开发人员查看了我存储的 proc 代码并说它看起来不错，但现在我想探索一种将批量"数据发送到数据库的更好方法.

For small sets of data, this isn't a problem, but when I had a very large set of data that required an iteration of 368 stored proc calls to update the records in the database, I realized I had a problem. A senior dev looked at my stored proc code and said it looked fine, but now I'd like to explore a better method for sending "batch" data to the database.

我有哪些选项可以批量更新数据库?这可能与存储过程吗?我还有什么其他选择?

What options do I have for updating the database in batch? Is this possible with stored procs? What other options do I have?

我不会选择安装成熟的 ORM，但我们不胜感激.

I won't have the option of installing a full-fledged ORM, but any advice is appreciated.

其他背景信息:

我们当前的数据访问模型是在 5 年前构建的，目前对数据库的所有调用都是通过模块化/静态函数执行的，名称类似于 ExecQuery 和 GetDataTable.我不确定我是否必须留在该模型中，但我必须提供一个很好的理由，让我们脱离当前的 DAL 进入数据库.

Our current data access model was built 5 years ago and all calls to the db currently get executed via modular/static functions with names like ExecQuery and GetDataTable. I'm not certain that I'm required to stay within that model, but I'd have to provide a very good justification for going outside of our current DAL to get to the DB.

另外值得注意的是，我对 CRUD 操作和数据库还很陌生.我更喜欢在代码的 .NET 方面玩/工作，但数据必须存储在某个地方，对吗?

Also worth noting, I'm fairly new when it comes to CRUD operations and the database. I much prefer to play/work in the .NET side of code, but the data has to be stored somewhere, right?

存储过程内容:

ALTER PROCEDURE [dbo].[spUpdateOrderDetailDiscountedItem] 
    -- Add the parameters for the stored procedure here
    @OrderDetailID decimal = 0,
    @Discount money = 0,
    @ExtPrice money = 0,
    @LineDiscountTypeID int = 0,
    @OrdersID decimal = 0,
    @QuantityDiscounted money = 0,
    @UpdateOrderHeader int = 0,
    @PromoCode varchar(6) = '',
    @TotalDiscount money = 0

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    Update OrderDetail
    Set Discount = @Discount, ExtPrice = @ExtPrice, LineDiscountTypeID = @LineDiscountTypeID, LineDiscountPercent = @QuantityDiscounted
    From OrderDetail with (nolock) 
    Where OrderDetailID = @OrderDetailID

    if @UpdateOrderHeader = -1
      Begin
        --This code should get code the last time this query is executed, but only then.
        exec spUpdateOrdersHeaderForSkuGroupSourceCode @OrdersID, 7, 0, @PromoCode, @TotalDiscount
      End

推荐答案

我在使用中看到的一种简单且替代的方法是构建一个 SQL 语句，该语句由 sql_execs 组成，这些语句使用字符串中的参数调用 sproc.不确定是否建议这样做，但从 .NET 的角度来看，您只填充一个 SqlCommand 并调用一次 ExecuteNonQuery...

An easy and alternative way I've seen in use is to build a SQL statement consisting of sql_execs calling the sproc with the parameters in the string. Not sure if this is advised or not, but from the .NET perspective, you are only populating one SqlCommand and calling ExecuteNonQuery once...

请注意，如果您选择此选项，请使用 StringBuilder！:-)

Note if you choose this then please, please use the StringBuilder! :-)

更新:我更喜欢 Chris Lively 的回答，直到现在才知道表值参数......不幸的是 OP 使用的是 2005.

Update: I much prefer Chris Lively's answer, didn't know about table-valued parameters until now... unfortunately the OP is using 2005.

这篇关于什么是触发存储过程 368 次来更新数据库的好选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！