Updating database in Transaction from List

Question

I have a database table which has 2 columns and I have a List where T has 2 properties.

How can I create a transaction so that I can effectively say:

UPDATE MyTable 
SET ColumnA = List<T> 1st property 
WHERE ColumnB = List<T> 2nd Property

A transaction may not necessarily be needed if one update will get executed but if I have 100 items in my List what will be required to get this working?

score 6 · Answer 1 · edited Aug 11 '14 at 15:43

6

For those using PetaPoco, I believe this is the correct way to do it:

var db = new PetaPoco.Database("MyDB");

try
{
    db.BeginTransaction();
    foreach (var item in NewData)
    {
        db.Execute("UPDATE MyTable SET ColA= @0 WHERE ColB = @1",item.PropertyA, item.PropertyB);
    }
    db.CompleteTransaction();

}
catch (Exception ex)
{
    db.AbortTransaction();
}

edited Aug 11 '14 at 15:43

Claudio Redi

67,454
15
130
155

answered Oct 12 '11 at 15:28

Jon

38,814
81
233
382

score 1 · Accepted Answer · answered Oct 12 '11 at 14:03

1

You need to execute these update statements in a loop, looping over your list

using(var connection = new SqlConnection(connectionString)) {
    connection.Open();
    using(var transaction = connection.BeginTransaction("Transaction")) {
        foreach(var item in list) {
            using(var command = connection.CreateCommand()) {
                command.Transaction = transaction;
                command.CommandText = // set the command text using item
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

answered Oct 12 '11 at 14:03

jason

236,483
35
423
525

I thought that might be the case – Jon Oct 12 '11 at 14:05
1

+1, but you can (and probably should) be adding the values using `command.Parameters.AddWithValue`. – Justin Morgan - On strike Oct 12 '11 at 14:10

score 0 · Answer 3 · answered Oct 12 '11 at 13:57

0

Unless that is multiple updates (i.e. it is multiple executions of an update statement) you do not need a transaction.

Perhaps you should give more details on the code actually executing...

answered Oct 12 '11 at 13:57

Mitch Wheat

295,962
43
465
541

Good point, a transaction may not be required. However I still need to know how to write the SQL/C# to get this working – Jon Oct 12 '11 at 14:02

Updating database in Transaction from List

3 Answers3