0

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

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

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.

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

Improve this question

3 Answers 3

Reset to default
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.

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 )

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks. sorry I am pretty new to asp-net. can you briefly explain wat's O/R and server/code behind?
I added some links for O/R in my response. Code-Behind means that you execute code on the server ( like events from your controls).
0

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.

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

Improve this answer

3 Comments

Generally speaking, that s wrong. If you exec n times you need n network calls and n execution which pretty much is always slower than one network call and one execution with multiple inserts.
Fair enough, but usually we're not just dumping data straight into a database; usually we're doing something to that data before it goes to SQL, and that's when SQL doesn't play as well as handling it in code. Hence 'general' and hence why this particular question would in fact be best done on SQL-side
I completely agree with you that some operations (string split, loops, pretty much everything that deals with single records instead of sets) is easier (and more sane) to code and maintain in C#...
0

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

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.