asp.net存储过程将多对数据插入到DB中

I have a method that accepts int[] (userIDs) and an int (groupID) as parameters. Then it runs the stored procedure and insert the data into the DB

我有一个接受int []（userIDs）和int（groupID）作为参数的方法。然后它运行存储过程并将数据插入到DB中

For example:
if userIDs=[1,2,3] and groupID=4, 
then I want the following data to be inserted into the DB
userID    groupID
1            4
2            4
3            4

I have 2 solutions to this problem. The first one is to write a stored procedure that insert a single record into the DB. In the method(), I will loop through the int[] and call the stored procedures n times

我有2个解决这个问题的方法。第一个是编写一个将单个记录插入DB的存储过程。在方法（）中，我将遍历int []并调用存储过程n次

method()
for (int i =0; i< userID.length; i++){
    // call stored procedure to insert a single record
}

The second solution is to pass int[] and int as parameters to the stored procedures and do the looping in the stored procedure.

第二种解决方案是将int []和int作为参数传递给存储过程，并在存储过程中进行循环。

Which way is a better solution? ( if its the 2nd solution is better, can someone provide guidance on handling int[] in stored procedure )

哪种方式更好的解决方案？（如果它的第二个解决方案更好，有人可以提供有关在存储过程中处理int []的指导）

3 个解决方案

#1

Is there a good reason why you don't want to use an O/R mapper? Your example looks like server / code-behind and you can use Entity Framework (or other) to insert your new values. If you can't use them then I would use the for ( the 2nd in your posting) approach. But it's dangerous, because you are not within any transaction.

你不想使用O / R映射器有充分的理由吗？您的示例看起来像服务器/代码隐藏，您可以使用实体框架（或其他）来插入新值。如果您不能使用它们，那么我会使用for（发布中的第2个）方法。但这很危险，因为你不在任何交易中。

You can start your Entity Framework investigation here : http://www.asp.net/entity-framework. If you, for any reasons, are not able to use EF consider using a transcation scope for your sql commands ( see http://msdn.microsoft.com/en-us/library/777e5ebh.aspx for starting reading )

您可以在此处开始实体框架调查：http：//www.asp.net/entity-framework。如果您出于任何原因无法使用EF，请考虑使用sql命令的transcation范围（请参阅http://msdn.microsoft.com/en-us/library/777e5ebh.aspx以开始阅读）

#2

Geeeeeeeeeeeenerally speaking, code is faster for this stuff. You're bettter off iterating through your array, C# side, then calling a stored procedure to handle your specific record.

一般来说，代码对于这些东西更快。你最好在你的数组C＃端迭代，然后调用存储过程来处理你的特定记录。

Now of course specifics will change, and this certainly sounds best handled in one big shot. Convert your int array to a datatable and then you can have some real fun...

现在当然具体情况会发生变化，这听起来肯定是最好处理的。将你的int数组转换为数据表，然后你可以有一些真正的乐趣......

#3

If all you're doing is adding rows to a database, I don't see the need for a Stored Procedure (unless that's a requirement coming from a DBA or policy).

如果你所做的只是向数据库中添加行，我认为不需要存储过程（除非这是来自DBA或策略的要求）。

Just loop through your items and add the entries to the database using ADO.NET or Entity Framework.

只需遍历您的项目，并使用ADO.NET或Entity Framework将条目添加到数据库。

#1

#2