Results 1 to 7 of 7

Thread: Need help with sqlite transactions in C#

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2010
    Posts
    817

    Need help with sqlite transactions in C#

    I am trying to learn more about sqLite and C#
    I have written a little code that inserts records into an sqLite database.
    Code:
                    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:
    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.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,896

    Re: Need help with sqlite transactions in C#

    Huh?

    You sure?
    BeginTransactionAsync(IsolationLevel, CancellationToken)

    https://learn.microsoft.com/en-us/do...c?view=net-9.0
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2010
    Posts
    817

    Re: Need help with sqlite transactions in C#

    Quote Originally Posted by Zvoni View Post
    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:
    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:
    Code:
                    using var transaction = conn.BeginTransaction();
    to this:
    Code:
                    using var transaction = conn.BeginTransactionAsync();
    Here is the new code:
    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

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,896

    Re: Need help with sqlite transactions in C#

    Quote Originally Posted by IliaPreston View Post
    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?
    Last edited by Zvoni; Feb 6th, 2025 at 06:44 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    Fanatic Member
    Join Date
    Jun 2019
    Posts
    567

    Re: Need help with sqlite transactions in C#

    When you use async methods, you should await them:
    C# Code:
    1. using var transaction = await conn.BeginTransactionAsync();

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2010
    Posts
    817

    Re: Need help with sqlite transactions in C#

    Thanks a lot for everybody's help.

    I added the await.
    In other words I changed this:
    Code:
                    using var transaction = conn.BeginTransactionAsync();
    To this:
    Code:
                    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:
    Code:
            private void btnInsStudentsTxnalAsync_Click(object sender, EventArgs e)
    To this:
    Code:
            private async void btnInsStudentsTxnalAsync_Click(object sender, EventArgs e)
    And made no other changes.

    Here is the new code:
    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:
    Code:
    using var cmd = new SQLiteCommand(sql, conn, transaction);
    Please help.
    Thanks.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,924

    Re: Need help with sqlite transactions in C#

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width