I have a program where I open a SqlConnection, load up a list of objects, modify a value on each object, then update the rows in the SQL Server database. Because the modification requires string parsing I wasn't able to do with with purely T-SQL.

Right now I am looping through the list of objects, and running a SQL update in each iteration. This seems inefficient and I'm wondering if there is a more efficient way to do it using LINQ

The list is called UsageRecords. The value I'm updating is MthlyConsumption.

Here is my code:

foreach (var item in UsageRecords)
{
    string UpdateQuery = @"UPDATE tbl810CTImport
                           SET MthlyConsumption = " + item.MthlyConsumption +
                           "WHERE ID = " + item.Id;
    SqlCommand update = new SqlCommand(UpdateQuery, sourceConnection);
    update.ExecuteNonQuery();
}

Try this instead:

string UpdateQuery = @"UPDATE tbl810CTImport SET MthlyConsumption = @consumption WHERE ID = @itemId";
var update = new SqlCommand(UpdateQuery, sourceConnection);
update.Parameters.Add("@consumption", SqlDbType.Int); // Specify the correct types here
update.Parameters.Add("@itemId", SqlDbType.Int); // Specify the correct types here
foreach (var item in UsageRecords)
{
    update.Parameters[0].Value = item.MthlyConsumption;
    update.Parameters[1].Value = item.Id;
    update.ExecuteNonQuery();
}

It should be faster because:

• You don't have to create the command each time.
• You don't create a new string each time (concatenation)
• The query is not parsed at every iteration (Just changes the parameters values).
• And it will cache the execution plan. (Thanks to @JohnCarpenter from the comment)