我的工作，获取了一组数据库中的数据的.NET组件，执行在该组数据的一些业务逻辑，然后通过一个存储过程，看起来像 spUpdateOrderDetailDiscountedItem 。

对于小型数据集，这是没有问题的，但是当我有一个非常大的数据集，需要368存储过程的迭代调用在更新数据库中的记录，我才意识到我有一个问题。一位资深的开发看了看我的存储过程code和说，这看起来不错，但现在我想探索一种更好的方法来发送批处理的数据到数据库中。

我必须在批量更新数据库哪些选项？这可能与存储的特效？我有什么其他选择？

我不会有安装一个完整的ORM的选择，但是任何的建议是AP preciated。

其他背景信息：的

我们的当前的数据访问模型是建立在5年前，所有调用数据库目前获得通过模块化/静态函数中包含的ExecQuery 名称和 GetDataTable 。我不能确定我的需要的保持这种模型中，但我不得不提供一个很好的理由，我们目前的DAL的去外面去数据库。

另外值得一提的，我是相当新的，当涉及到CRUD操作和数据库。我更preFER播放/工作在code中的.NET的一面，但数据已存储在某个地方，对不对？

存储过程的内容：

  ALTER PROCEDURE [DBO]。[spUpdateOrderDetailDiscountedItem]
     - 添加的参数存储过程在这里
    @OrderDetailID十进制= 0，
    @Discount钱= 0，
    @ExtPrice钱= 0，
    @LineDiscountTypeID INT = 0，
    @OrdersID十进制= 0，
    @QuantityDiscounted钱= 0，
    @UpdateOrderHeader INT = 0，
    @促销code VARCHAR（6）=''，
    @TotalDiscount钱= 0

如
开始
     -  SET NOCOUNT ON加入到prevent额外的结果集
     - 与SELECT语句的干扰。
    SET NOCOUNT ON;

     - 插入过程语句这里
    更新的OrderDetail
    设置折扣= @Discount，ExtPrice = @ExtPrice，LineDiscountTypeID = @LineDiscountTypeID，LineDiscountPercent = @QuantityDiscounted
    从使用的OrderDetail（NOLOCK）
    其中OrderDetailID = @OrderDetailID

    如果@UpdateOrderHeader = -1
      开始
         - 这code应该得到c为执行此查询的最后一次$ C $，但只有。
        EXEC spUpdateOrdersHeaderForSkuGroupSource code @OrdersID，7，0，@促销code，@TotalDiscount
      结束
 

解决方案

我已经看到了使用的方便，另一种方式是建立一个SQL语句组成sql_execs调用带有字符串参数中的存储过程的。不知道这是建议与否，而是从.NET的角度来看，你只填充1的SqlCommand和呼叫的ExecuteNonQuery一次...

请注意，如果你选择了这个，那么请，请使用的StringBuilder ！ ： - ）

更新：我更preFER克里斯活泼的回答，不知道表值参数，直到现在......不幸的是，OP是用2005

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.

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?

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

Additional Background Info:

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.

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?

Stored Proc contents:

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

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...

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

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