SQLite in VB.net - Page 1

SQLite is a very flexible relational database that allows users to store rows of data for their programs. Because it is highly used, database format wrappers are found in almost all languages for it. This tutorial will focus on the basics of installing and using the most popular Visual Basic .NET wrapper System.Data.SQLite - created and maintained by Robert Simpson. If you are familiar with ADO.NET you will find this very similar. Don't worry if you aren't though, for it it is quite simple to use.

Installation of System.Data.SQLite
For this step you will need any version of Microsoft Visual Studio 2005/2008. The tutorial is written with Visual Studio 2008 in mind, but all steps are the same on either version.
Please follow the link below to download System.Data.SQLite off the official site:
http://sqlite.phxsoftware.com/

Note

Make sure to download the latest non-Alpha version, and you will want to download the “SQLite-X.X.XX.X-setup.exe” as this is the quickest method of installation.

Click next through the installation wizard till you get to this screen:

For those using VS 2008 Express Design-Time support does not work, so do not install it. This is not a glitch, it is disabled by Microsoft in the Express edition of 2008, but does work in the 2005 Express Edition. This is not used in the tutorial so it will not matter either way if you have it installed it.

Installing the SQLite Design-Time Support is optional for those that need to test queries. It is rather easy to use and understand, and I’m sure you won’t need it after this tutorial or if you know the basics of SQL already.

Create a VB.NET New Project
Let’s start up Visual Studio 2005/2008 and create a New Visual Basic » Windows Form Application. I created this with .NET 2.0 as it is the lowest version of .NET that is supported.

Creating a Database
First understand that a database is a completely empty file when it’s first created, and if the database isn’t found where you open the connection it will create a new file.
First create a button and name it btn_createdb and double click the button to bring up the code viewer.

The code that you will be placing into the button basically opens up the Windows save dialog so that you can simply save your database where you want:

Private Sub btn_createdb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_createdb.Click 'Save Dialog Box Dim f As New SaveFileDialog f.Filter = "SQLite 3 (*.db3)|*.db3|All Files|" f.ShowDialog() 'Create Database Dim SQLconnect As New SQLite.SQLiteConnection() 'Database Doesn't Exist so Created at Path SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";" SQLconnect.Open() SQLconnect.Close() End Sub

When saving a database, the standard extension is .db3, but if you are using this for back up purposes, you may choose your own extension.

Remember that in a real example, you will usually never create an empty database.

Creating a Table
The first thing to understand is your basic data types that you have to work with. SQLite, being a very compact database, has 4 main (not counting NULL) such data types. This makes it really simple to know which type your data should be in.

• INTEGER
  The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
• REAL
  The value is a floating point value, stored as an 8-byte IEEE floating point number.
• TEXT
  The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
• BLOB
  The value is a blob of data, stored exactly as it was input.

(Source: http://www.sqlite.org/datatype3.html)

What you will do is add some code to create a table. Before delving into the code, let's take a look at what your table will look like first:

Now that you know what we will be creating, let's add it. First start by creating a button called btn_createtable. Double click the button to be taken to the code view where you will be inside this button's event handler.

Copy and paste the following code:

Dim f As New OpenFileDialog f.Filter = "SQLite 3 (*.db3)|*.db3|All Files|*.*" If f.ShowDialog() = DialogResult.OK Then Dim SQLconnect As New SQLite.SQLiteConnection() Dim SQLcommand As SQLiteCommand SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";" SQLconnect.Open() SQLcommand = SQLconnect.CreateCommand 'SQL query to Create Table SQLcommand.CommandText = "CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description TEXT, image BLOB);" SQLcommand.ExecuteNonQuery() SQLcommand.Dispose() SQLconnect.Close() End If

You created a table called foo, and if you were to visualize it, it looks just like the image of the table I showed you earlier. Ok, let's take a small break and continue on the next page.

Onwards to the next page!

1 | 2