Need help with sqlite transactions in C#

[IliaPreston]

I am trying to learn more about sqLite and C#
I have written a little code that inserts records into an sqLite database.

                for (i = 0; i < ProcFileLines.Length; i++)
                {
                            ................................
                            cmd.CommandText = "insert into T1 (   ......    ) values (     .......     )";
                            cmd.ExecuteNonQuery();

                            RecAdded++;
                 }

It works fine, but it is very slow.
It takes about 7 minutes to insert 4200 records.

I remember from VB6 that the solution was to rewrite the above code in terms of an sql transaction.
I have a sample VB6 code:

   sql = "insert  into  T1 ( .......... ) values ( ......... ) "
   
   Set Cmd = Cnn.CreateCommand(sql)
   Cnn.Synchronous = False
   Cnn.BeginTrans
   
   For i = 0 To LN - 1
      ................................

      Cmd.SetText 1, FName(i)
      Cmd.SetText 2, LName(i)
      Cmd.SetText 3, .....
      Cmd.SetText 4, ......
      
      Cmd.Execute
      
      If i Mod 2000 = 0 Then
         Cnn.CommitTrans
         Cnn.BeginTrans
      End If
   Next i
   
   Cnn.CommitTrans
   Cnn.Synchronous = True

The above VB6 code works and is super fast.

I am not sure how literally I can translate the above VB6 code into C#.
For example, at the very first step, in C# the connection object does not have a Synchronous property.

Can you please provide a bare minimum C# equivalent of the above VB6 code?

Thanks.

[Zvoni]

Huh?

You sure?
BeginTransactionAsync(IsolationLevel, CancellationToken)

https://learn.microsoft.com/en-us/do...c?view=net-9.0

[IliaPreston]

Huh?

You sure?
BeginTransactionAsync(IsolationLevel, CancellationToken)

https://learn.microsoft.com/en-us/do...c?view=net-9.0

Thanks a lot for your help.

Before doing the Async transaction, I decided to first do a Sync transaction.
I wrote the following code:

        private void btnInsStudentsTxnal_Click(object sender, EventArgs e)
        {
            int i;
            int sn;
            string sql;
            try
            {
                btnInsStudentsTxnal.Enabled = false;
                txtDetails.Text = "Process started. (" + DateTime.Now + ") Please wait ...";

                using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
                conn.Open();

                using var transaction = conn.BeginTransaction();
                try
                {
                    sql = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  (@fname, @lname, @stdnum)";

                    using var cmd = new SQLiteCommand(sql, conn, transaction);

                    for (i = 0; i < 10000; i++)
                    {
                        sn = 1000 + i;
                        cmd.Parameters.AddWithValue("fname", "John_" + i);
                        cmd.Parameters.AddWithValue("lname", "Davis_" + i);
                        cmd.Parameters.AddWithValue("stdnum", sn);

                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();

                    txtDetails.Text = txtDetails.Text + "\r\n\r\nProcess completed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                }
                catch (Exception e1)
                {
                    transaction.Rollback();
                    txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                    txtDetails.Text = txtDetails.Text + string.Format("Sqlite Exception caught: {0}", e1) + "\r\n\r\n\r\n" + string.Format("SqLite Exception caught: {0}", e1.Message);
                    txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                    return;
                }
            }
            catch (Exception e2)
            {
                txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                txtDetails.Text = txtDetails.Text + string.Format("Exception caught: {0}", e2) + "\r\n\r\n\r\n" + string.Format("Exception caught: {0}", e2.Message);
                txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                btnInsStudentsTxnal.Enabled = true;
                return;
            }
        }

And it works perfectly and also is super fast.


Then, I copy the above code and just change the transaction to Async, that is, I change this:

                using var transaction = conn.BeginTransaction();

to this:

                using var transaction = conn.BeginTransactionAsync();

Here is the new code:

        private void btnInsStudentsTxnalAsync_Click(object sender, EventArgs e)
        {
            int i;
            int sn;
            string sql;
            try
            {
                btnInsStudentsTxnal.Enabled = false;
                txtDetails.Text = "Process started. (" + DateTime.Now + ") Please wait ...";

                using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
                conn.Open();

                using var transaction = conn.BeginTransactionAsync();
                try
                {
                    sql = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  (@fname, @lname, @stdnum)";

                    using var cmd = new SQLiteCommand(sql, conn, transaction);

                    for (i = 0; i < 10000; i++)
                    {
                        sn = 1000 + i;
                        cmd.Parameters.AddWithValue("fname", "John_" + i);
                        cmd.Parameters.AddWithValue("lname", "Davis_" + i);
                        cmd.Parameters.AddWithValue("stdnum", sn);

                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();

                    txtDetails.Text = txtDetails.Text + "\r\n\r\nProcess completed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                }
                catch (Exception e1)
                {
                    transaction.Rollback();
                    txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                    txtDetails.Text = txtDetails.Text + string.Format("Sqlite Exception caught: {0}", e1) + "\r\n\r\n\r\n" + string.Format("SqLite Exception caught: {0}", e1.Message);
                    txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                    return;
                }
            }
            catch (Exception e2)
            {
                txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                txtDetails.Text = txtDetails.Text + string.Format("Exception caught: {0}", e2) + "\r\n\r\n\r\n" + string.Format("Exception caught: {0}", e2.Message);
                txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                btnInsStudentsTxnal.Enabled = true;
                return;
            }
        }

But, this gives me a lot of errors:

Severity Code Description Project File Line Suppression State
Error (active) CS1674 'ValueTask<DbTransaction>': type used in a using statement must be implicitly convertible to 'System.IDisposable'. Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 993
Error (active) CS1503 Argument 3: cannot convert from 'System.Threading.Tasks.ValueTask<System.Data.Common.DbTransaction>' to 'System.Data.SQLite.SQLiteTransaction' Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 999
Error (active) CS1061 'ValueTask<DbTransaction>' does not contain a definition for 'Commit' and no accessible extension method 'Commit' accepting a first argument of type 'ValueTask<DbTransaction>' could be found (are you missing a using directive or an assembly reference?) Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 1010
Error (active) CS1061 'ValueTask<DbTransaction>' does not contain a definition for 'Rollback' and no accessible extension method 'Rollback' accepting a first argument of type 'ValueTask<DbTransaction>' could be found (are you missing a using directive or an assembly reference?) Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 1017
Error (active) CS1674 'ValueTask<DbTransaction>': type used in a using statement must be implicitly convertible to 'System.IDisposable'. Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 993
Error (active) CS1503 Argument 3: cannot convert from 'System.Threading.Tasks.ValueTask<System.Data.Common.DbTransaction>' to 'System.Data.SQLite.SQLiteTransaction' Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 999
Error (active) CS1061 'ValueTask<DbTransaction>' does not contain a definition for 'Commit' and no accessible extension method 'Commit' accepting a first argument of type 'ValueTask<DbTransaction>' could be found (are you missing a using directive or an assembly reference?) Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 1010
Error (active) CS1061 'ValueTask<DbTransaction>' does not contain a definition for 'Rollback' and no accessible extension method 'Rollback' accepting a first argument of type 'ValueTask<DbTransaction>' could be found (are you missing a using directive or an assembly reference?) Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 1017

I don't understand this.
Can you please help?

Thanks a lot

[Zvoni]

Thanks a lot for your help. But, this gives me a lot of errors: I don't understand this. Can you please help? Thanks a lot

Not really, since i don't "speak" a single word of C# (But i can read some of it)
My answer was just researching your initial "complain" of there being no async transaction in C#

But reading your Error-Message, i think SQLiteTransaction is not compatible with the Return-Type of conn.BeginTransactionAsync,
which quite frankly baffles me, since SQLiteTransaction inherits from
public class SqliteTransaction : System.Data.Common.DbTransaction

https://learn.microsoft.com/en-us/do...a-sqlite-8.0.0


But if you have something working (and it's fast), so why change it?

[peterst]

When you use async methods, you should await them:

using var transaction = await conn.BeginTransactionAsync();

[IliaPreston]

Thanks a lot for everybody's help.

I added the await.
In other words I changed this:

                using var transaction = conn.BeginTransactionAsync();

To this:

                using var transaction = await conn.BeginTransactionAsync();

Then it gave me a number of errors including this one:

Error (active) CS4033 The 'await' operator can only be used within an async method. Consider marking this method with the 'async' modifier and changing its return type to 'Task'.

So, I made the method async. In other words, I changed this:

        private void btnInsStudentsTxnalAsync_Click(object sender, EventArgs e)

To this:

        private async void btnInsStudentsTxnalAsync_Click(object sender, EventArgs e)

And made no other changes.

Here is the new code:

        private async void btnInsStudentsTxnalAsync_Click(object sender, EventArgs e)
        {
            int i;
            int sn;
            string sql;
            try
            {
                btnInsStudentsTxnal.Enabled = false;
                txtDetails.Text = "Process started. (" + DateTime.Now + ") Please wait ...";

                using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
                conn.Open();

                using var transaction = await conn.BeginTransactionAsync();
                //using var transaction = conn.BeginTransaction();
                try
                {
                    sql = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  (@fname, @lname, @stdnum)";

                    using var cmd = new SQLiteCommand(sql, conn, transaction);

                    for (i = 0; i < 10000; i++)
                    {
                        sn = 1000 + i;
                        cmd.Parameters.AddWithValue("fname", "John_" + i);
                        cmd.Parameters.AddWithValue("lname", "Davis_" + i);
                        cmd.Parameters.AddWithValue("stdnum", sn);

                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();

                    txtDetails.Text = txtDetails.Text + "\r\n\r\nProcess completed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                }
                catch (Exception e1)
                {
                    transaction.Rollback();
                    txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                    txtDetails.Text = txtDetails.Text + string.Format("Sqlite Exception caught: {0}", e1) + "\r\n\r\n\r\n" + string.Format("SqLite Exception caught: {0}", e1.Message);
                    txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                    return;
                }
            }
            catch (Exception e2)
            {
                txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                txtDetails.Text = txtDetails.Text + string.Format("Exception caught: {0}", e2) + "\r\n\r\n\r\n" + string.Format("Exception caught: {0}", e2.Message);
                txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                btnInsStudentsTxnal.Enabled = true;
                return;
            }
        }

But, it gives me several errors:

Severity Code Description Project File Line Suppression State
Error (active) CS1503 Argument 3: cannot convert from 'System.Data.Common.DbTransaction' to 'System.Data.SQLite.SQLiteTransaction' Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 999
Error (active) CS1503 Argument 3: cannot convert from 'System.Data.Common.DbTransaction' to 'System.Data.SQLite.SQLiteTransaction' Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 999

The error occurs on line 999, which is this line:

using var cmd = new SQLiteCommand(sql, conn, transaction);

Please help.
Thanks.

[jmcilhinney]

The error message seems pretty clear. That constructor expects an argument of type System.Data.SQLite.SQLiteTransaction and you're passing it a System.Data.Common.DbTransaction reference. Maybe don't do that. If you actually take the time to look at and think about what's going on, it seems likely that BeginTransactionAsync is creating an object of the type you want, even if the return type is a base class of that. Given that DbTransaction is an abstract class, the object obviously must be a more specific type, so cast as that type. This is really just elementary stuff but you assuming that it's more complicated because it involves an async method. That method is irrelevant to this issue.

[IliaPreston]

The error message seems pretty clear. That constructor expects an argument of type System.Data.SQLite.SQLiteTransaction and you're passing it a System.Data.Common.DbTransaction reference. Maybe don't do that. If you actually take the time to look at and think about what's going on, it seems likely that BeginTransactionAsync is creating an object of the type you want, even if the return type is a base class of that. Given that DbTransaction is an abstract class, the object obviously must be a more specific type, so cast as that type. This is really just elementary stuff but you assuming that it's more complicated because it involves an async method. That method is irrelevant to this issue.

Thanks for your help.

I changed this:

                    using var cmd = new SQLiteCommand(sql, conn, transaction);

To this:

                    using var cmd = new SQLiteCommand(sql, conn, (System.Data.SQLite.SQLiteTransaction)transaction);

And it solved the problem.
It worked without a hitch.

Thanks again for your help

[IliaPreston]

Thanks again to everybody who helped with this.

Another problem is that if I want to break down the bulk inserts into a number of smaller bulk inserts, it gives me error messages.

I add this code to the inside of the loop:

                        if (i % 100 == 0)
                        {
                            transaction.Commit();
                            using var cmd = new SQLiteCommand(sql, conn, transaction);
                        }

Here is the full new code:

        private void btnInsStudentsTxnal_Click(object sender, EventArgs e)
        {
            int i;
            int sn;
            string sql;
            try
            {
                btnInsStudentsTxnal.Enabled = false;
                txtDetails.Text = "Process started. (" + DateTime.Now + ") Please wait ...";

                using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
                conn.Open();

                using var transaction = conn.BeginTransaction();
                try
                {
                    sql = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  (@fname, @lname, @stdnum)";

                    using var cmd = new SQLiteCommand(sql, conn, transaction);

                    for (i = 0; i < 10000; i++)
                    {
                        sn = 1000 + i;
                        cmd.Parameters.AddWithValue("fname", "John_" + i);
                        cmd.Parameters.AddWithValue("lname", "Davis_" + i);
                        cmd.Parameters.AddWithValue("stdnum", sn);

                        cmd.ExecuteNonQuery();

                        if (i % 100 == 0)
                        {
                            transaction.Commit();
                            using var cmd = new SQLiteCommand(sql, conn, transaction);
                        }
                    }
                    transaction.Commit();

                    txtDetails.Text = txtDetails.Text + "\r\n\r\nProcess completed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                }
                catch (Exception e1)
                {
                    transaction.Rollback();
                    txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                    txtDetails.Text = txtDetails.Text + string.Format("Sqlite Exception caught: {0}", e1) + "\r\n\r\n\r\n" + string.Format("SqLite Exception caught: {0}", e1.Message);
                    txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                    btnInsStudentsTxnal.Enabled = true;
                    return;
                }
            }
            catch (Exception e2)
            {
                txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                txtDetails.Text = txtDetails.Text + string.Format("Exception caught: {0}", e2) + "\r\n\r\n\r\n" + string.Format("Exception caught: {0}", e2.Message);
                txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                btnInsStudentsTxnal.Enabled = true;
                return;
            }
        }

It gives me this error:

Severity Code Description Project File Line Suppression State
Error (active) CS0136 A local or parameter named 'cmd' cannot be declared in this scope because that name is used in an enclosing local scope to define a local or parameter Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 958

OK. I understand that. It is because of the "var" that tries to declare cmd a second time within the same scope, and that is not right.
So, I change that to this:

                        if (i % 100 == 0)
                        {
                            transaction.Commit();
                            cmd = new SQLiteCommand(sql, conn, transaction);
                        }

And it gives me this error:

Severity Code Description Project File Line Suppression State
Error (active) CS1656 Cannot assign to 'cmd' because it is a 'using variable' Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 958

OK. So, it should be "using" all the time.
So, I change that to this:

                        if (i % 100 == 0)
                        {
                            transaction.Commit();
                            using cmd = new SQLiteCommand(sql, conn, transaction);
                        }

But, it gives me this error:

Severity Code Description Project File Line Suppression State
Error (active) CS1001 Identifier expected Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 958
Error (active) CS0118 'cmd' is a variable but is used like a type Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 958
Error (active) CS1001 Identifier expected Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 958

Well! I am running out of options. What else can I do to make it work?

Please note that this is NOT async.
It is sync.

Please help.
Thanks a lot.

[jmcilhinney]

It shouldn't be "using all the time". The whole point of a 'using' statement is to create an object that will be disposed at the end of the block in which the variable is declared. You can't assign to such a variable because that would mean that the object that was created and should be disposed would no longer be accessible. Your 'using' statement inside the 'if' block is pointless because it's inside the 'if' block, so the object you create will be disposed at the end of the 'if' block. Off the top of my head, I'd suggest that you nest two 'for' loops, then you can create a new command with a 'using' statement at the beginning of each outer loop and then commit at the end of each outer loop.

[IliaPreston]

It shouldn't be "using all the time". The whole point of a 'using' statement is to create an object that will be disposed at the end of the block in which the variable is declared. You can't assign to such a variable because that would mean that the object that was created and should be disposed would no longer be accessible. Your 'using' statement inside the 'if' block is pointless because it's inside the 'if' block, so the object you create will be disposed at the end of the 'if' block. Off the top of my head, I'd suggest that you nest two 'for' loops, then you can create a new command with a 'using' statement at the beginning of each outer loop and then commit at the end of each outer loop.

Thanks a lot for your help.

According to your advice, I wrote wrote two loops (one nested inside the other):

                i = 0;
                sql = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  (@fname, @lname, @stdnum)";

                for (j = 0; j < ChunkCnt; j++)
                {
                    using var transaction = conn.BeginTransaction();
                    using var cmd = new SQLiteCommand(sql, conn, transaction);
                    try
                    {
                        k = 0;

                        while ((k < RecPerChunk) && (i < TotRecCnt))
                        {
                            sn = 1000 + i;
                            cmd.Parameters.AddWithValue("fname", "John_" + i);
                            cmd.Parameters.AddWithValue("lname", "Davis_" + i);
                            cmd.Parameters.AddWithValue("stdnum", sn);

                            cmd.ExecuteNonQuery();

                            i++;
                            k++;
                        }
                        transaction.Commit();
                    }
                    catch (Exception e1)
                    {
                        transaction.Rollback();
                        txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                        txtDetails.Text = txtDetails.Text + string.Format("Sqlite Exception caught: {0}", e1) + "\r\n\r\n\r\n" + string.Format("SqLite Exception caught: {0}", e1.Message);
                        txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                        btnInsStudentsTxnal.Enabled = true;
                        btnInsStudentsTxnal.Focus();
                        return;
                    }
                }

And it works perfectly.
There is no issue with that.

However, there is something that I don't understand.
If you take a look at the red lines in the above code snippet, those lines are actually variable declarations.
I copy them in here:

                for (j = 0; j < ChunkCnt; j++)
                {
                    using var transaction = conn.BeginTransaction();
                    using var cmd = new SQLiteCommand(sql, conn, transaction);
                ............
                ............
                ............
                {

In the above code, I am actually declaring each one of those two variables INSIDE a loop.
In other words, I am declaring each one of those two variables repetitiously !!!!!!

And yet the code works !!!!!!
And it works PERFECTLY !!!!!!

It shouldn't work.
It shouldn't even compile.
And yet it compiles, AND runs, And runs perfectly correctly !!!!!!

I really don't understand that.

A variable is declared inside a loop, and the loop goes through hundreds of times.
It means that that variable is declared hundreds of times with the same name in the same scope.
This should lead to a compile error.
How come it doesn't?

Can you please shed some light on this?

Thanks again for your help.

[jmcilhinney]

It absolutely should not lead to compiler errors. A loop is a block. A variable declared inside a block ceases to exist at the end of that block. Each iteration of the loop is a separate instance of that block. Each iteration, the variable is declared, used and then falls out of scope.

[IliaPreston]

It absolutely should not lead to compiler errors. A loop is a block. A variable declared inside a block ceases to exist at the end of that block. Each iteration of the loop is a separate instance of that block. Each iteration, the variable is declared, used and then falls out of scope.

Thanks a lot for your help.
I just ran a small test and it shows that everything you said above is absolutely correct.

1- The following test declares a variable m inside the loop and it compiles and runs perfectly.

This shows that your claim is right:

        private void btnTest012_Click(object sender, EventArgs e)
        {   //      This procedure tests the possibility of declaring a variable inside a loop
            //      Also testing whether the variable declared inside a loop exists outside the loop or not
            long s = 0;
            int i;

            for (i = 0; i < 10; i++)
            {
                long m;                 // This variable is declared inside a loop
                m = i % 3;

                s = s + m;
            }

            MessageBox.Show("The sum is: " + s);
        }

2-

A loop is a block. A variable declared inside a block ceases to exist at the end of that block.

I add one other line (shown in red) to the above code:

        private void btnTest012_Click(object sender, EventArgs e)
        {   //      This procedure tests the possibility of declaring a variable inside a loop
            //      Also testing whether the variable declared inside a loop exists outside the loop or not
            long s = 0;
            int i;

            for (i = 0; i < 10; i++)
            {
                long m;                 // This variable is declared inside a loop
                m = i % 3;

                s = s + m;
            }

            MessageBox.Show("The sum is: " + s);
            MessageBox.Show("The value of m is: " + m);
        }

And it gives me this compile error:

Severity Code Description Project File Line Suppression State
Error (active) CS0103 The name 'm' does not exist in the current context Test002SqLite D:\Dev\VCS\Tests\Test002SqLite\Test002SqLite\Form1.cs 1093

This also confirms that your claim is correct.

Thanks again for your help.

[IliaPreston]

I am hereby posting the code for the test that I ran for sync and async transactional sqLite queries done all in one shot or done chunk-wise.
It may be helpful to somebody who may one day have the same problem and may search and come across this thread.

Firs-off, I have this general-purpose method (added to a shared project):

        public static long ChunkCntFromTotRecCntAndRecPerChunk(long TotRecCnt, long RecPerChunk)
        {
            long ChunkCnt;

            ChunkCnt = TotRecCnt / RecPerChunk;

            if ((TotRecCnt % RecPerChunk) != 0)
            {
                ChunkCnt++;
            }

            return ChunkCnt;
        }

Then, here is the code for sync transaction:

        private void btnInsStudentsTxnal_Click(object sender, EventArgs e)
        {
            long i;
            long j;
            long k;
            long TotRecCnt;
            long RecPerChunk;
            long ChunkCnt;
            long sn;
            string sql;
            try
            {
                btnInsStudentsTxnal.Enabled = false;
                txtDetails.Text = "Process started. (" + DateTime.Now + ") Please wait ...";

                using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
                conn.Open();

                TotRecCnt = 100;
                RecPerChunk = 57;

                ChunkCnt = ChunkCntFromTotRecCntAndRecPerChunk(TotRecCnt, RecPerChunk);

                i = 0;
                sql = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  (@fname, @lname, @stdnum)";

                for (j = 0; j < ChunkCnt; j++)
                {
                    using var transaction = conn.BeginTransaction();
                    using var cmd = new SQLiteCommand(sql, conn, transaction);
                    try
                    {
                        k = 0;

                        while ((k < RecPerChunk) && (i < TotRecCnt))
                        {
                            sn = 1000 + i;
                            cmd.Parameters.AddWithValue("fname", "John_" + i);
                            cmd.Parameters.AddWithValue("lname", "Davis_" + i);
                            cmd.Parameters.AddWithValue("stdnum", sn);

                            cmd.ExecuteNonQuery();

                            i++;
                            k++;
                        }
                        transaction.Commit();
                    }
                    catch (Exception e1)
                    {
                        transaction.Rollback();
                        txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                        txtDetails.Text = txtDetails.Text + string.Format("Sqlite Exception caught: {0}", e1) + "\r\n\r\n\r\n" + string.Format("SqLite Exception caught: {0}", e1.Message);
                        txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                        btnInsStudentsTxnal.Enabled = true;
                        btnInsStudentsTxnal.Focus();
                        return;
                    }
                }

                txtDetails.Text = txtDetails.Text + "\r\n\r\nProcess completed. (" + DateTime.Now + ")";
                btnInsStudentsTxnal.Enabled = true;
                btnInsStudentsTxnal.Focus();
            }
            catch (Exception e2)
            {
                txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                txtDetails.Text = txtDetails.Text + string.Format("Exception caught: {0}", e2) + "\r\n\r\n\r\n" + string.Format("Exception caught: {0}", e2.Message);
                txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                btnInsStudentsTxnal.Enabled = true;
                btnInsStudentsTxnal.Focus();
                return;
            }
        }

And, here is the code for async transaction:

        private async void btnInsStudentsTxnalAsync_Click(object sender, EventArgs e)
        {
            long i;
            long j;
            long k;
            long TotRecCnt;
            long RecPerChunk;
            long ChunkCnt;
            long sn;
            string sql;
            try
            {
                btnInsStudentsTxnalAsync.Enabled = false;
                txtDetails.Text = "Process started. (" + DateTime.Now + ") Please wait ...";


                using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
                conn.Open();

                TotRecCnt = 100;
                RecPerChunk = 57;

                ChunkCnt = ChunkCntFromTotRecCntAndRecPerChunk(TotRecCnt, RecPerChunk);

                i = 0;
                sql = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  (@fname, @lname, @stdnum)";

                for (j = 0; j < ChunkCnt; j++)
                {
                    using var transaction = await conn.BeginTransactionAsync();
                    using var cmd = new SQLiteCommand(sql, conn, (System.Data.SQLite.SQLiteTransaction)transaction);
                    try
                    {
                        k = 0;

                        while ((k < RecPerChunk) && (i < TotRecCnt))
                        {
                            sn = 1000 + i;
                            cmd.Parameters.AddWithValue("fname", "John_" + i);
                            cmd.Parameters.AddWithValue("lname", "Davis_" + i);
                            cmd.Parameters.AddWithValue("stdnum", sn);

                            cmd.ExecuteNonQuery();

                            i++;
                            k++;
                        }
                        transaction.Commit();
                    }
                    catch (Exception e1)
                    {
                        transaction.Rollback();
                        txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                        txtDetails.Text = txtDetails.Text + string.Format("Sqlite Exception caught: {0}", e1) + "\r\n\r\n\r\n" + string.Format("SqLite Exception caught: {0}", e1.Message);
                        txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                        btnInsStudentsTxnalAsync.Enabled = true;
                        btnInsStudentsTxnalAsync.Focus();
                        return;
                    }
                }


                txtDetails.Text = txtDetails.Text + "\r\n\r\nProcess completed. (" + DateTime.Now + ")";
                btnInsStudentsTxnalAsync.Enabled = true;
                btnInsStudentsTxnalAsync.Focus();
            }
            catch (Exception e2)
            {
                txtDetails.Text = txtDetails.Text + "\r\n\r\n";
                txtDetails.Text = txtDetails.Text + string.Format("Exception caught: {0}", e2) + "\r\n\r\n\r\n" + string.Format("Exception caught: {0}", e2.Message);
                txtDetails.Text = txtDetails.Text + "\r\nProcess failed. (" + DateTime.Now + ")";
                btnInsStudentsTxnalAsync.Enabled = true;
                btnInsStudentsTxnalAsync.Focus();
                return;
            }
        }

Thanks everybody who helped and advised me on this thread.
Cheers.

[IliaPreston]

As you see in the above code snippets that I provided in post #14, the process is chunk-wise. (If the chunk size is equal or greater than the total record count then it will be done in one shot).

Now, one ambiguity in here is that what is the best chunk size?

For example, if I try to insert one million records, should I insert all of them in one shot (one sqLite transaction)

or should I insert 100,000 records at a time (100,000 records by each transaction)?
or should I insert 10,000 records at a time (10,000 records by each transaction)?
etc.

Or what if I try to insert a billion records?
Or what if I try to insert a trillion records?
All in one shot or chunk-wise, and if the answer is chunk-wise then how many records at a time?

Also, will the answer to the above question be different if the process is sync versus async>

Please advise.
Thanks

[Zvoni]

As you see in the above code snippets that I provided in post #14, the process is chunk-wise. (If the chunk size is equal or greater than the total record count then it will be done in one shot).

Now, one ambiguity in here is that what is the best chunk size?

For example, if I try to insert one million records, should I insert all of them in one shot (one sqLite transaction)

or should I insert 100,000 records at a time (100,000 records by each transaction)?
or should I insert 10,000 records at a time (10,000 records by each transaction)?
etc.

Or what if I try to insert a billion records?
Or what if I try to insert a trillion records?
All in one shot or chunk-wise, and if the answer is chunk-wise then how many records at a time?

Also, will the answer to the above question be different if the process is sync versus async>

Please advise.
Thanks

As always in life: it depends.
Since it's sqlite, i gather it's single-user, local storage.

I would kind of calculate, how many Bytes a single INSERT is (kind of an average size of an INSERT).
Depending on this, it's a simple calculation, how many bytes are 100K records and so on.
I would be wary to fire off a Transaction, which involves 100MB (or more) in one shot.

It's a bit of try and error.

What might give you an indication about "chunk"-size might be the page-size of the DB itself, but i have no idea if this even influences your issue

Found this: https://stackoverflow.com/questions/...tion-in-sqlite
This might also shed some light: https://www.sqlite.org/limits.html

I also saw mention of "What kind of Harddrive" is the sqlite-DB stored on?
Is it SSD, or one of the "classics" (say 7200 rpm)

[IliaPreston]

As always in life: it depends.
Since it's sqlite, i gather it's single-user, local storage.

I would kind of calculate, how many Bytes a single INSERT is (kind of an average size of an INSERT).
Depending on this, it's a simple calculation, how many bytes are 100K records and so on.
I would be wary to fire off a Transaction, which involves 100MB (or more) in one shot.

It's a bit of try and error.

What might give you an indication about "chunk"-size might be the page-size of the DB itself, but i have no idea if this even influences your issue

Found this: https://stackoverflow.com/questions/...tion-in-sqlite
This might also shed some light: https://www.sqlite.org/limits.html

I also saw mention of "What kind of Harddrive" is the sqlite-DB stored on?
Is it SSD, or one of the "classics" (say 7200 rpm)

Thanks a lot for your help.
I read those links that you provided.

Given the fact that it is the number of bytes that matters and not the number of records, I can calculate the average byte-size of a record, and then talk only in terms of bytes.
This way, the question is how many bytes should be inserted in each transaction?

If I know the limit in terms of bytes, then I can divide that by the average byte-size of a record to find out the number of records to insert in each transaction.

So, the question is how many bytes should be inserted in each transaction?

Also, you said:

I also saw mention of "What kind of Harddrive" is the sqlite-DB stored on?
Is it SSD, or one of the "classics" (say 7200 rpm)

Currently, I am just learning C# and sqLite.
It is not a real project, so, I cannot say if it is an SSD drive or an electromechanical drive (one of the "classics" (say 7200 rpm)).
What would the difference be in terms of the issue that I am discussing in this thread?
Will it make any difference?

Please advise.
Thanks again.

[jmcilhinney]

So, the question is how many bytes should be inserted in each transaction?

There is no single answer to that question. It depends on your specific scenario. Fewer chunks will generally faster because there's an overhead with each chunk, but you want to use smaller chunks as the risk of one failing gets larger, e.g. because of a flaky network. There's also the possibility that, past a particular threshold, larger chunks get slower because of the need for caching or the like. You need to decide what you want and then test it to see if it works and if anything else works better.

[IliaPreston]

There is no single answer to that question. It depends on your specific scenario. Fewer chunks will generally faster because there's an overhead with each chunk, but you want to use smaller chunks as the risk of one failing gets larger, e.g. because of a flaky network. There's also the possibility that, past a particular threshold, larger chunks get slower because of the need for caching or the like. You need to decide what you want and then test it to see if it works and if anything else works better.

Thanks a lot for your help

[IliaPreston]

I am trying to understand the difference between sync and async.
I ran a google search and found this:
https://www.sqlite.org/asyncvfs.html

Now, I kind of understand the difference between sync and async.
However, that article says:

Hence, this module is no longer supported. The source code continues to exist in the SQLite source tree, but it is not a part of any standard build and is no longer maintained. This documentation is retained for historical reference.

Not sure what exactly it is talking about.
Does it mean that sqLite no longer supports async transactions any more?

Please advise.
Thanks

[Zvoni]

I am trying to understand the difference between sync and async.
I ran a google search and found this:
https://www.sqlite.org/asyncvfs.html

Now, I kind of understand the difference between sync and async.
However, that article says:

Not sure what exactly it is talking about.
Does it mean that sqLite no longer supports async transactions any more?

Please advise.
Thanks

It means, that the "off-the-shelf" sqlite-dll's are NOT compiled with that option enabled (since it's deprecated)
No idea about OleDB-Provider, .NET-Assembly or whatever you use.

After reading it, i'd even think about NOT using explicit async-Transaction, but to spin up its own thread, throw your long-timed transaction at the thread, and let it do its job.
The thread might even fire off a "notification" when it's done

Just brainstorming here