SQLite in VB.net - Page 2
       by Austin Andrews aka Templarian  |  3 May 2008

In the previous page, we started looking at some simple database operations you can do using SQLite. In this page, let's continue doing more of the same.

Inserting a Record (Update/Delete)
Just like in all SQL, if you know how to insert a record, you can change the SQL query to update and delete a record. Create another button called btn_insert and add the fallowing 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 'Insert Record into Foo SQLcommand.CommandText = "INSERT INTO foo (title, description) VALUES ('This is a title', 'This is a Description')" 'Update Last Created Record in Foo SQLcommand.CommandText = "UPDATE foo SET title = 'New Title', description = 'New Description' WHERE id = last_insert_rowid()" 'Delete Last Created Record from Foo SQLcommand.CommandText = "DELETE FROM foo WHERE id = last_insert_rowid()" SQLcommand.ExecuteNonQuery() SQLcommand.Dispose() SQLconnect.Close() End If

Reading Records into a List
What’s the use of putting all this data into a database if you cannot show it off? First create a button called “btn_readrecord”, and a listbox called “lst_records”, place the code below into the button:

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 SQLcommand.CommandText = "SELECT * FROM foo" Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader() lst_records.Items.Clear() While SQLreader.Read() lst_records.Items.Add(String.Format("ID = {0}, Title = {1}, Description = {1}", SQLreader(0), SQLreader(1), SQLreader(2))) 'Optionally use the variable names to identify columns 'De lst_records.Items.Add(String.Format("ID = {0}, Title = {1}, Description = {1}", SQLreader("id"), SQLreader("title"), SQLreader("description"))) End While SQLcommand.Dispose() SQLconnect.Close() End If

Reading Tables into a Drop Down
Most projects require multiple tables to store all the relational information. And most of the times the tables are used to organize categories, so a way to find these tables is needed as shown with the code following code example.

But, first, create a button called btn_tables, and a Combo Box called cmb_tables. Once you have done that, then 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 Dim SchemaTable = SQLconnect.GetSchema(SQLiteMetaDataCollectionNames.Tables) For int As Integer = 0 To SchemaTable.Rows.Count - 1 If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "table" Then cmb_tables.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString()) End If Next SQLcommand.Dispose() SQLconnect.Close() End If

Writing/Reading a BLOB Image into the Table
I have left actually writing and reading an image out of the examples thus far because it’s optional and easier to explain out of context. With that said, it is hard enough that one would be mad if I left it out. If you are interested to see this included into the other source, it is a highly recommended to download the source example I've provided at the end of this tutorial that is based on the sample code you see here.

For this, simply create a button called btn_insertimage, a button called btn_updateimage, a button called btn_viewimage, and a picture box called pic_viewer. This example creates a database with a table called foo with 2 columns and image BLOB. In the table, one record is inserted and viewable. The image is always entered into where id is equal to 1.

The code is:

Imports System.Data.SQLite Imports System.IO Public Class sqlitetutorial 'Image BLOB Functions Private Function BlobToImage(ByVal blob) Dim mStream As New System.IO.MemoryStream Dim pData() As Byte = DirectCast(blob, Byte()) mStream.Write(pData, 0, Convert.ToInt32(pData.Length)) Dim bm As Bitmap = New Bitmap(mStream, False) mStream.Dispose() Return bm End Function Public Overloads Function ImageToBlob(ByVal id As String, ByVal filePath As String) Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read) Dim br As BinaryReader = New BinaryReader(fs) Dim bm() As Byte = br.ReadBytes(fs.Length) br.Close() fs.Close() 'Create Parm Dim photo() As Byte = bm Dim SQLparm As New SQLiteParameter("@image", photo) SQLparm.DbType = DbType.Binary SQLparm.Value = photo Return SQLparm End Function 'NOT USED IN THE SOURCE. Public Overloads Function ImageToBlob(ByVal id As String, ByVal image As Image) Dim ms As New MemoryStream() image.Save(ms, System.Drawing.Imaging.ImageFormat.Png) 'Create Parm Dim photo() As Byte = ms.ToArray() Dim SQLparm As New SQLiteParameter("@image", photo) SQLparm.DbType = DbType.Binary SQLparm.Value = photo Return SQLparm End Function Private Sub btn_insertimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_insertimage.Click Dim f As New SaveFileDialog f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*" Dim d As New OpenFileDialog d.Filter = "Image (*.png)|*.png|All Files|*.*" If f.ShowDialog() = DialogResult.OK And d.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() 'Insert image, DO NOT single quote @image SQLcommand.CommandText = "INSERT INTO foo (image) VALUES(@image)" 'Define @image SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName)) SQLcommand.ExecuteNonQuery() SQLcommand.Dispose() SQLconnect.Close() End If End Sub Private Sub btn_viewimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_viewimage.Click Dim f As New OpenFileDialog f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|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 SQLcommand.CommandText = "SELECT image FROM foo WHERE id = '1'" Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader() While SQLreader.Read() pic_viewimage.Image = BlobToImage(SQLreader("image")) End While SQLcommand.Dispose() SQLconnect.Close() End If End Sub Private Sub btn_updateimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_updateimage.Click Dim f As New OpenFileDialog f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*" Dim d As New OpenFileDialog d.Filter = "Image (*.png)|*.png|All Files|*.*" If f.ShowDialog() = DialogResult.OK And d.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 'Update image, DO NOT single quote @image SQLcommand.CommandText = "UPDATE foo SET image = @image WHERE id = '1'" 'Define @image SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName)) SQLcommand.ExecuteNonQuery() SQLcommand.Dispose() SQLconnect.Close() End If End Sub End Class

Summary
Hopefully with all these code examples and short explanations, you should be better prepared for your future projects that use SQLite.

Download the source for this project. The source example is a fully working database editor for a simple database with an id, title, description, and image fields:

All of which are stored in the databases. It is highly recommended to read through the source of the example. If you have any questions, please post on the forums.

Austin Andrews aka Templarian

1 | 2