SQLite Development

Imports Finisar.SQLite

Const CONNECTION_STR As String = "Data Source=customers.db;Version=3;"

Private Sub CreateDatabase()
'===================================================
' Create a new database and populate it with some data
'===================================================

'Declare the main SQLite data access objects
Dim objConn As SQLiteConnection
Dim objCommand As SQLiteCommand

Try
'Create a new database connection
'Note - use New=True to create a new database
objConn = New SQLiteConnection(CONNECTION_STR & "New=True;")

'Open the connection
objConn.Open()

'Create a new SQL command
objCommand = objConn.CreateCommand()

'Setup and execute the command SQL to create a new table
objCommand.CommandText = "CREATE TABLE customer (id integer primary key, name varchar(100));"
objCommand.ExecuteNonQuery()

'Insert a couple of records into the table
objCommand.CommandText = "INSERT INTO customer (id, name) VALUES (1, 'John Smith');"
objCommand.ExecuteNonQuery()
objCommand.CommandText = "INSERT INTO customer (id, name) VALUES (2, 'Jane Jones');"
objCommand.ExecuteNonQuery()

Finally
'Cleanup and close the connection
If Not IsNothing(objConn) Then
objConn.Close()
End If
End Try

End Sub

Private Sub ReadData()
'===================================================
' Read data from the customers table, populating the
' customers list box
'===================================================

'Declare the main SQLite data access objects
Dim objConn As SQLiteConnection
Dim objCommand As SQLiteCommand
Dim objReader As SQLiteDataReader

Try
'Create a new database connection
objConn = New SQLiteConnection(CONNECTION_STR)

'Open the connection
objConn.Open()

'Create a new SQL command to read all records from the customer table
objCommand = objConn.CreateCommand()
objCommand.CommandText = "SELECT * FROM customer"

'Execute the command returning a reader object
objReader = objCommand.ExecuteReader()

'Iterate through the rows in the reader, adding each name found
'to the listbox on the form
lstCustomers.Items.Clear()
While (objReader.Read())
lstCustomers.Items.Add(objReader("name"))
End While

Catch ex As Exception
MessageBox.Show("An error has occurred: " & ex.Message)

Finally
'Cleanup and close the connection
If Not IsNothing(objConn) Then
objConn.Close()
End If
End Try

End Sub

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

Try
    'Create and populate a new SQLite database
CreateDatabase()
MessageBox.Show("Database created successfully")

Catch ex As Exception
MessageBox.Show("An error occurred creating the database: " & ex.Message)

End Try

End Sub

Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click

Try
'Read customer records from the SQLite DB and populate
'the customers listbox on screen.
ReadData()

Catch ex As Exception
MessageBox.Show("An error occurred reading from the database: " & ex.Message)

End Try

End Sub