vb.net and using SQLite

Question

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

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cons As New SQLite.SQLiteConnection
        cons.ConnectionString = "Data Source=C:\database.s3db; Version=3"
        cons.Open()

        Using tx = cons.BeginTransaction()

            Dim cmd As SQLite.SQLiteCommand
            cmd = cons.CreateCommand()

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

            tx.Commit()
            cmd.Dispose()
        End Using

        cons.Close()
End Sub

I don't know whether this is for best performance, but some points to improve. 1)Use paramertized queries, 2)Don't swallow exceptions(report to user or atleast log it) 3) wrap connection and command also in Using block — Sriram Sakthivel, Sep 28 '13 at 11:44
I can give example but that wouldn't be a answer for this specific question since you asked fastest performance. I'll link some questions for you. Check [this](http://stackoverflow.com/a/17553377/2530848) to proper use of DB calls. [Parameterized queries](http://stackoverflow.com/a/542542/2530848) is here. And I assume you know to handle exceptions :) — Sriram Sakthivel, Sep 28 '13 at 19:48
And one more thing, when you want to notify some user you've to use @XK8ER Like this. use(@). So that they'll be notified. — Sriram Sakthivel, Sep 28 '13 at 19:50
@SriramSakthivel im not really an expert on this but can you please show me how it would look with my code? — XK8ER, Sep 28 '13 at 22:32
@SriramSakthivel i've been trying for the past 3 hours to get this thing running like you say but no luck.. — XK8ER, Sep 28 '13 at 23:54

score 1 · Accepted Answer · answered Sep 29 '13 at 07:44

1

As I mentioned, proper way of doing this using parameterized queries, not swallowing the exception, and using statements wherever necessary would look something like this. Do note this is not the fastest way of doing.

Private Sub InsertRows()
    Using conn As New SqlConnection
        conn.Open()
        Using tx = conn.BeginTransaction()
            For i As Integer = 0 To 1000
                Using cmd = conn.CreateCommand() 'Proper using statements wherever necessary
                    Try
                        cmd.CommandText = "INSERT INTO table1 VALUES(@ColumnName)" 'Paramertized queries
                        cmd.Parameters.AddWithValue("@ColumnName", i)
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        logger.ErrorException(ex) 'Logging the exception or shoe messagebox
                    End Try
                End Using
            Next
        End Using
    End Using
End Sub

Ideally you shouldn't continue execution if there is an exception in a tight loop doing same task. If one fails there is a chance that everything can fail. So in that case you should remove the try/catch inside the loop and wrap it over the for loop.

answered Sep 29 '13 at 07:44

Sriram Sakthivel

72,067
7
111
189

your using SqlConnection and I am using SQLite.SQLiteConnection also when do I close connection and dispose and stuff? – XK8ER Sep 29 '13 at 08:22
Yes, change every Sql related things to SqlLite. Ex: `SqlCommand` to `SqlLiteCommand`. You don't need to close connection `Dispose` will take care of that. and `Dispose` is automatically called when control leaves `using block` – Sriram Sakthivel Sep 29 '13 at 08:29
I made those changes and ran the code and i dont get errors but the database file remains the same.. I added this befoe open. `conn.ConnectionString = "Data Source=C:\data.s3db; Version=3"` – XK8ER Sep 29 '13 at 08:35
Try adding the exact column name instead of @Columnname – Sriram Sakthivel Sep 29 '13 at 08:37
like this? `cmd.CommandText = "INSERT INTO table1 VALUES(@table1)" cmd.Parameters.AddWithValue("@table1", i)` – XK8ER Sep 29 '13 at 08:40
I tried using both, table1 and my_ids. check my database here [link](http://img4.imageshack.us/img4/9060/ipep.png) – XK8ER Sep 29 '13 at 08:51

vb.net and using SQLite

1 Answers1