Click here to Skip to main content
15,749,203 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I would like to start by apologising for any mistake i could give using English as it is not my natural language.
A bit of Context:
I'm parsing a csv file. Each line in the file represents an object that is used afterwards to update a table in a SQL Server database . I've built the parser already and the process that goes through each line and converts it into an object. The several objects are stored in an array.

Here is the issue I would like you to help me with:
I need to go through each object in the array and hit an update against a table in the database. The update will be done in one table only. Each object contains the data to search for the record I need to update and the value to update to. Also I need to join 3 tables to get to the target table.
Should I go through each object in the array and for each object run an updated against the database, hitting the database as many times as the number of objects we have in the array(and it can be thousands of objects in the array) or, should I get a way to go only once to the database and run the update? For example create a new table in the database representing the objects and then insert each object as a row in that table and afterwards run let say a stored procedure that cycles through that table doing the necessary updates.

P.S I'm using Visual Studio 2015 and C#4.5

Many thanks.

What I have tried:

I did readings on MSDN(SQl server,C#,LINQ), codeproject, stackoverflow and c#corner.
Updated 20-Apr-16 7:00am

I recommend an SQL-batch-statement: Multiple statements, separated by semicolons, in a single SQL-command:
UPDATE myTable SET col1 = @p1 WHERE col2 = @p2;
UPDATE myTable SET col1 = @p3 WHERE col2 = @p4;
UPDATE myTable SET col1 = @p5 WHERE col2 = @p6;
/* etc */

-> A single database-call, no need for a temporary table or stored procedure. It doesn't get (much) faster than this.

If you only want to execute the whole batch if every single statement succeeds then compare the return value of .ExecuteNonQuery() to your record count and only commit the transaction if they're equal.
Share this answer
Depending on the version of SQL Server you're using, you might want to look at Table-Valued Parameters[^]. You pass the entire set of data as a single parameter, which you can then use as a table variable to perform the update.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900