vb.net and using SQLite

Hello,
I just want to make sure that this is the right code for working with SQLite for fastest performance..

someone suggested me I should Use paramertized queries and to wrap connection and command also in Using block.. but how do I do that?

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim cons As New SQLite.SQLiteConnection
            Dim cmd As New SQLite.SQLiteCommand

            cons.ConnectionString = "Data Source=C:\data.s3db; Version=3"
            cons.Open()
            cmd = cons.CreateCommand()

            Using tx = cons.BeginTransaction()

                For i As Integer = 0 To 1000
                        cmd.CommandText = "INSERT INTO table1 VALUES('" & i & "')"
                        cmd.ExecuteNonQuery()
                Next

                tx.Commit()
            End Using

            cmd.Dispose()
            cons.Close()
    End Sub

Open in new window

LVL 1
XK8ERAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jonnidipCommented:
I personally never suggest to use parameterized queries, just because the fact that any SQL engine do pre-compile on Stored Procedures.
This is why I always use SP, rather than queries from clients.
Using SPs can be also good when you need to change your engine (ie from SQLite to SQL Server, or Oracle, or MySQL). You will only need to eventually change the query itself in the SPs, but the SPs signatures will remain the same.

Anyway, about your question, the "Using" on Connection and Command is done this way:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
   Using cons As New SQLite.SQLiteConnection("Data Source=C:\data.s3db; Version=3")
        Using cmd As New SQLite.SQLiteCommand("", cons)
                'cons.ConnectionString = "Data Source=C:\data.s3db; Version=3"
                cons.Open()
                'cmd = cons.CreateCommand()
    
                Using tx = cons.BeginTransaction()
    
                    For i As Integer = 0 To 1000
                            cmd.CommandText = "INSERT INTO table1 VALUES('" & i & "')"
                            cmd.ExecuteNonQuery()
                    Next
    
                    tx.Commit()
                
                End Using
        End Using
   End Using
End Sub

Open in new window

Regards.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.