SQLite Transaction throwing exception that I can't figure out

Question

    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.

score 4 · Accepted Answer · answered Aug 31 '13 at 04:23

4

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();
    } 
}

answered Aug 31 '13 at 04:23

McGarnagle

96,448
30
213
255

I don't really want to create a new command (and all of the data associated with it) each loop through my users array, particularly as the array can be thousands of users at a time, and my goal is to use transactions to bring my write time down on DB operations. Without transactions, any write query is currently taking something like 60-70 ms. When I'm dealing with thousands of users at a time, that just doesn't work. Trying to optimize. – Keirathi Aug 31 '13 at 04:28
@Keirathi I believe you're looking for multi-row inserts instead, or perhaps your terminology is off. McGarnagle's answer still uses transactions for all the inserts. If you want to make sure only one command gets sent to the server, check out http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database – Matthew Aug 31 '13 at 04:34
Sorry, I should have tested his solution before I replied. I just got scared because I read in another post that it would be "better" to only create the SQLiteCommand once, only bind the parameters once, only set the CommandText once, etc. I was worried that 10000+ of those operations would slow me down, but 10000 operations still only took .8s, which is well within reasonable operation. Marking as solved. – Keirathi Aug 31 '13 at 04:43
I am quite baffled here. Why would @Keirathi's original code throw this error unless something (the `ExecuteNonQuery()`?) is automatically disposing the object? And if it is doing so, doesn't this violate the standard contracts for ADO/Dispose? – fostandy May 17 '16 at 06:57

score 1 · Answer 2 · answered May 17 '16 at 08:07

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

SQLite Transaction throwing exception that I can't figure out

2 Answers2