Saturday, September 23, 2006
Simple VB.Net Sqlite Example
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.
- Open VB 2005
- Select File, New Project and choose a Windows Application as the project template. Name the project SimpleSQLite
- 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)
- Back in VB.Net add a reference to the SQLite.NET.dll component
- Rename Form1 as frmSimple and save the project
- Add the following controls to the form
Control Name Properties Label Label1 Text = Customers Listbox lstCustomers Button btnCreate Text = Create Database Button btnRead Text = Read data - 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;" - 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. - 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. - 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 - Run the project, then click the Create Database button. This should create a new database in the bin/debug folder named customers.db.
- 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.
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.
