Saturday, September 23, 2006

 

Simple VB.Net Sqlite Example

O/S: Windows
Language: VB.Net
Download code: SimpleSQLite.zip (6k)

As a first stab at some Sqlite development, I thought I'd see if I could write a simple VB.Net program to read some data back from a database. Having done a little research, I came across a useful ADO.Net Data Provider for SQLite. This allows you to write data access code in any .Net language that closely resembles that used with other traditional databases such as SQL Server.

Time for an example. First download the SQLite.Net ADO.NET Data Provider at http://adodotnetsqlite.sourceforge.net/downloads/ (I chose the SQLite.NET.0.21_x68_dll.zip (x86)). This includes the core SQLite library and the .Net wrapper so there is nothing else to download. Now we'll create our simple SQLite example program as described above.

  1. Open VB 2005

  2. Select File, New Project and choose a Windows Application as the project template. Name the project SimpleSQLite

  3. Extract the contents of the downloaded zip file into the bin/debug folder. This will add three DLL's (the .Net wrapper along with versions 2 and 3 of SQLite though you will only need v3)

  4. Back in VB.Net add a reference to the SQLite.NET.dll component

  5. Rename Form1 as frmSimple and save the project

  6. Add the following controls to the form
    ControlNameProperties
    LabelLabel1Text = Customers
    ListboxlstCustomers 
    ButtonbtnCreateText = Create Database
    ButtonbtnReadText = Read data

  7. Go to the code view for frmSimple and add the following above the class declaration
    Imports Finisar.SQLite

    This will import the .Net wrapper library. Then add a connection string constant inside the class:
    Const CONNECTION_STR As String = "Data Source=customers.db;Version=3;"


  8. Add the following function:

    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

    This will create a new SQLite database with a single table named customers. It will then add two records to the table.

  9. Add the following function

    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

    This will open the database and read all records from the customers table and display the customer names in the listbox.

  10. Add an event handler for each of the buttons that invoke the new functions

    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


  11. Run the project, then click the Create Database button. This should create a new database in the bin/debug folder named customers.db.

  12. Click the Read data button and you should see the names of the two customers added to the listbox.


As you can see, this simple example demonstrates that it is very easy to get up and running with SQLite, allowing standalone applications to be created that leverage the power of a SQL based database.
Download code: SimpleSQLite.zip (6k)

Tuesday, September 19, 2006

 

Welcome

Hi everyone. This is my welcome post to get this blog started. It is intended as a learning experience cum tutorial about SQLite Development. For the uninitiated SQLite is a lightweight (in size but not power) database engine that can be embedded in your applications.

In many circumstances it can be used in place of a more traditional database server such as SQL Server or MySQL. Certainly if you are thinking of developing an application using Microsoft Access as your DB then stop and think again. SQLite could be the engine for you.

My recommendation for now is have a look at the SQLite website to get a flavour of the features and power of SQLite. Coming soon will be series of posts designed to provide a start on writing applications that use SQLite. I am just beginning myself so as I learn I'll try to pass on my findings.

This page is powered by Blogger. Isn't yours?