-
Feb 4th, 2025, 06:15 AM
#1
Thread Starter
Fanatic Member
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.
-
Feb 4th, 2025, 06:32 AM
#2
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
-
Feb 6th, 2025, 06:34 AM
#3
Thread Starter
Fanatic Member
Re: Need help with sqlite transactions in C#
 Originally Posted by Zvoni
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
-
Feb 6th, 2025, 06:38 AM
#4
Re: Need help with sqlite transactions in C#
 Originally Posted by IliaPreston
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
-
Feb 6th, 2025, 09:29 AM
#5
Re: Need help with sqlite transactions in C#
When you use async methods, you should await them:
C# Code:
using var transaction = await conn.BeginTransactionAsync();
-
Thread Starter
Fanatic Member
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|