INSERT with transaction and parameters?

Question

I am learning VB.Net and should work with an SQLite database using the open source System.Data.SQLite solution.

The examples I found in the HOWTO section are only in C #. Does anyone have a simple example in VB.Net that I could study to understand how to use transactions when INSERTing multiple parameters?

FWIW, here is the code I'm working on:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim SQLconnect As New SQLite.SQLiteConnection() Dim SQLcommand As SQLite.SQLiteCommand Dim SQLtransaction As SQLite.SQLiteTransaction SQLconnect.ConnectionString = "Data Source=test.sqlite;" SQLconnect.Open() SQLcommand = SQLconnect.CreateCommand SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, hash TEXT);" SQLcommand.ExecuteNonQuery() '================ INSERT starts here SQLtransaction = SQLconnect.BeginTransaction() Dim myparam As New SQLite.SQLiteParameter() SQLcommand.CommandText = "INSERT INTO [files] ([name],[hash]) VALUES(?,?)" SQLcommand.Parameters.Add(myparam) 'How to set all parameters? myparam.Value SQLcommand.ExecuteNonQuery() SQLtransaction.Commit() '================ INSERT ends here SQLcommand.CommandText = "SELECT id,name,hash FROM files" 'How to tell if at least one row? Dim SQLreader As SQLite.SQLiteDataReader = SQLcommand.ExecuteReader() While SQLreader.Read() ListBox1.Items.Add(SQLreader(1)) End While SQLcommand.Dispose() SQLconnect.Close() End Sub 

Thanks.

---

Edit: for those interested, here is the working code:

 SQLtransaction = SQLconnect.BeginTransaction() SQLcommand.CommandText = "INSERT INTO files (name,hash) VALUES(@name,@hash)" SQLcommand.Parameters.AddWithValue("@name", "myfile") SQLcommand.Parameters.AddWithValue("@hash", "123456789") SQLcommand.ExecuteNonQuery() SQLtransaction.Commit() 

Answer 1

The transaction approach should be the same (assuming the SQLite API supports transactions). As for several parameters, you need to declare an instance of the SqlParameter class for each parameter, and then add each of them to the request.

 Dim myparam As New SQLite.SQLiteParameter() myparam.Value = "Parameter 1 value" Dim myparam2 As New SQLite.SQLiteParameter() myparam2.Value = "Parameter 2 value" SQLcommand.Parameters.Add(myparam) SQLcommand.Parameters.Add(myparam2) 

Regarding your question "How to determine at least one row", the standard .NET SQLReader has the "HasRows" property. those.

 If SQLreader.HasRows Then While SQLreader.Read() ListBox1.Items.Add(SQLreader(1)) End While End If 

I also assume that the SQLlite driver should also be.

Sorry if this code is not clean VB, I did not touch it after about 5 years!