Hello, I just started yesterday with SQLite in .NET. Great product so far, I have to say. I'm coming from a MySQL / PHP background. I was hoping someone help answer some questions about the following code? My questions are:
- for the first code (inserting lots of rows), you see in the for loop where I set the parameter values? I never specified what type of variable this is. For one I give it an integer (i) and for the other I give it a string (i.ToString). Is that okay or is it better to specify the types first? I was looking at the Parameters.Add() method and it has several optional overloads for specifying type and size. Most examples I see online ignore those, but I'm sure they exist for some reason...
- for the second code (inserting a single row), does using a transaction in this case still have any benefit? I've seen lots of examples where transactions are not used.
Also, a general question. Please see the INSERT statement. I have single quotes around @info because this is a string value. This is what I have always done in MySQL, but I have yet to see people do it in SQLite. Should I remove the single quotes? If so, please explain why.
Thank you
... and onto the code ...
' ******* insert lots of rows *******
Using cmd As SQLiteCommand = conn.CreateCommand
'prepare base statement
cmd.CommandText = "INSERT INTO `invoices` (accountid, info) VALUES (@accountid, '@info')"
'add param
Dim paramAccountId As New SQLiteParameter("accountid")
cmd.Parameters.Add(paramAccountId)
'add param
Dim paramInfo As New SQLiteParameter("info")
cmd.Parameters.Add(paramInfo)
'loop & execute
Using trans As SQLiteTransaction = conn.BeginTransaction
For i As Integer = 0 To 99999
paramAccountId.Value = i
paramInfo.Value = i.ToString
cmd.ExecuteNonQuery()
Next
trans.Commit()
End Using
End Using
' ******* insert single row *******
Using cmd As SQLiteCommand = conn.CreateCommand
'prepare base statement
cmd.CommandText = "INSERT INTO `invoices` (accountid, info) VALUES (@accountid, '@info')"
'add params
cmd.Parameters.AddWithValue("@accountid", 5)
cmd.Parameters.AddWithValue("@info", "blahbahaah")
'execute
Using trans As SQLiteTransaction = conn.BeginTransaction
cmd.ExecuteNonQuery()
trans.Commit()
End Using
End Using