CODEWITHSUNDEEP
c#sqlite
Keirathi
Keirathi

Reputation: 397

SQLite Transaction throwing exception that I can't figure out

    public void test2(String[] users)
    {
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        using (var myTransaction = myDB.BeginTransaction())
        {
            using (cmd = myDB.CreateCommand())
            {
                cmd.Transaction = myTransaction;
                cmd.CommandText = "UPDATE myTable SET counter = counter + 1 WHERE user = @user";
                cmd.Parameters.AddWithValue("@user", "");
                foreach (string person in users)
                {
                    cmd.Parameters["@user"].Value = person;
                    cmd.ExecuteNonQuery();
                } 
            }
            myTransaction.Commit();
            stopwatch.Stop();
            Console.WriteLine("Updating existing users took: " + stopwatch.Elapsed);
        }
    }

I'm having a beast of a time figuring this one out. I've looked at tons of c# transaction examples, and my code looks pretty similar to all of them except for having the foreach loop. But I've seen multiple ExeCuteNonQuery's in a single transaction, so I have no idea what the problem is. Any help?

Edit: Guess I should explain the problem :P

Getting:

"System.ObjectDisposedException: Cannot access a disposed object. Object name: 'SQLiteCommand'."

On the cmd.Parameters["@user"].Value = person; line.

Upvotes: 1

Views: 1228

Answers (2)

mlehmk
mlehmk

Reputation: 106

It looks like the variable cmd is a field. This would explain why the command could be disposed during the foreach loop. If test2 is ran while another test is ran in parallel then the other test can dispose whatever is in the cmd field. To solve this issue remove cmd from the class and turn it into a local variable for the method. Example, using(var cmd = myDB.CreateCommand())

Upvotes: 1

McGarnagle
McGarnagle

Reputation: 102753

My guess is that the command is getting disposed after it executes. Try putting the foreach loop outside:

foreach (string person in users)
{
    using (cmd = myDB.CreateCommand())
    {
        cmd.Transaction = myTransaction;
        cmd.CommandText = "UPDATE myTable SET counter = counter + 1 WHERE user = @user";
        cmd.Parameters.AddWithValue("@user", person);
        cmd.ExecuteNonQuery();
    } 
}

Upvotes: 4

Related Questions