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
– Sriram Sakthivel, Commented Sep 28, 2013 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 to proper use of DB calls. Parameterized queries is here. And I assume you know to handle exceptions :) — Sriram Sakthivel
– Sriram Sakthivel, Commented Sep 28, 2013 at 19:48
And one more thing, when you want to notify some user you've to use @XK8ER Like this. use(@<username>). So that they'll be notified. — Sriram Sakthivel
– Sriram Sakthivel, Commented Sep 28, 2013 at 19:50

Sriram Sakthivel · Accepted Answer · 2013-09-29 07:44:56Z

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, 2013 at 7:44

Sriram Sakthivel

73.8k7 gold badges118 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

XK8ER Over a year ago

your using SqlConnection and I am using SQLite.SQLiteConnection also when do I close connection and dispose and stuff?

Sriram Sakthivel Over a year ago

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

XK8ER Over a year ago

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"

Sriram Sakthivel Over a year ago

Try adding the exact column name instead of @Columnname

XK8ER Over a year ago

like this? cmd.CommandText = "INSERT INTO table1 VALUES(@table1)" cmd.Parameters.AddWithValue("@table1", i)

|

Collectives™ on Stack Overflow

vb.net and using SQLite

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related