*SQL lite*

Back to the start
Back to the libraries overview


Overview (SQLlite, Connection)
BeginTransaction (SQLlite, Connection)
Close (SQLlite, Connection)
CreateSQLTable (SQLlite, Connection)
EndTransaction (SQLlite, Connection)
New1 (SQLlite, Connection)
Open (SQLlite, Connection)
Value (SQLlite, Connection)
BytesToBLOB (SQLlite, Command)
CommandText (SQLlite, Command)
ExecuteNonQuery (SQLlite, Command)
ExecuteReader (SQLlite, Command
ExecuteTable (SQLlite, Command)
FileToBLOB (SQLlite, Command)
New1 (SQLlite, Command)
Close (SQLlite, DataReader)
FieldCount (SQLlite, DataReader)
GetBytes (SQLlite, DataReader)
GetImage (SQLlite, DataReader)
GetValue (SQLlite, DataReader)
IsDBNull (SQLlite, DataReader)
New1 (SQLlite, DataReader)
ReadNextRow (SQLlite, DataReader)
Value (SQLlite, DataReader)

Overview (SQLlite, Connection) Top

The SQL library (version1.2) is based on the popular open source SQL engine, SQLite 3.
With SQLite the database is stored in a single file.
SQLite site: www.sqlite.org
In the above link you can find many explanations about the SQLite engine and syntax.
This implementation is based on the open source .Net library named System.Data.SQLite.
System.Data.SQLite site: sqlite.phxsoftware.com
The SQL library requires that the .Net Compact Framework 2.0 will be installed on the device (.Net Framework 2.0 on the desktop).
There are instructions about upgrading the frameworks here: b4ppcforum.geotrail.no/viewtopic.php?f=6&t=698


Using this library you can use larger databases and with much more options than using only the Table control.
There are built-in methods for showing the queried results in a Table control and for saving the data in a Table control inside an SQLite database.
This help manual do not cover the SQLite syntax.
The syntax is covered here: www.sqlite.org/lang.html


The SQL library includes three types of objects:
- Connection - Used to establish a connection to a database file. Also includes the CreateSQLTable which copies the data from a Table control into the database.
- Command - Used to execute SQL commands.
- DataReader - Allows forward only access to the result of an executed command. The reading is done row after row.


Note that when you distribute an application that uses this library you should copy System.Data.SQLite.DLL as well (there is one for the device and one for the desktop).
These files are packed with this library as well.
A simple example of using this library along with Microsoft's Northwind database sample (converted to SQLite) can be downloaded here: www.basic4ppc.com/Downloads

Sub Globals

End Sub

Sub App_Start
Form1.Show
Con.New1
Reader.New1
Tree.New1("Form2", 5, 5, Form2.Width - 10, Form2.Height - 10)
Node.New1
Cmd.New1("",con.Value)
Con.Open("Data Source = " & AppPath & "\Northwind.sl3") 'Opens a connection with the database.
AddEvent("btnExecute",Click,"mnuExecute_Click") 'The button and the menu use the same sub.
End Sub

'Executes the SQL command
Sub mnuExecute_Click
cmd.CommandText = txtCommand.Text
'Executes the user query and fills the table with the result.
cmd.ExecuteTable("table1",500) 'Limits the number of rows to 500 (change to 0 for unlimited)
End Sub

Sub mnuShowScheme_Click
Form2.Show
End Sub

Sub mnuRefresh_Click 'Fills the TreeView with the tables and columns data
for i = 0 to Tree.Count - 1
Tree.RemoveNodeAt(0)
next
Con.BeginTransaction 'Starts a block of I/O with the database.
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'" 'Finds all the tables in this database
Reader.Value = cmd.ExecuteReader
Do while reader.ReadNextRow = True
Tree.AddNewNode(reader.GetValue(0))
loop
Reader.Close
for i = 0 to Tree.Count - 1
Node.Value = Tree.GetNode(i)
cmd.CommandText = "PRAGMA table_info ('" & Node.Text & "')" 'Special SQLite command to find the table's metadata.
reader.Value = cmd.ExecuteReader
Do while Reader.ReadNextRow = True
Node.AddNewNode(Reader.GetValue(1) & " : " & Reader.GetValue(2))
Loop
Reader.Close
next
Con.EndTransaction
End Sub

Sub Form2_Show
if Tree.Count = 0 then mnuRefresh_Click
End Sub

Sub Form1_Close
Con.Close
End Sub


BeginTransaction (SQLlite, Connection) Top

Each time you execute a command on the database a transaction is created automatically if it wasn't opened earlier.
When you are executing several commands one after another it is much faster to start with BeginTransaction and finish with EndTransaction.
That way, only one transaction will be created for the entire block.
Syntax: BeginTransaction

Example:
Con.BeginTransaction 'Starts a block of I/O with the database.
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'" 'Finds all the tables in this database
Reader.Value = cmd.ExecuteReader
Do while reader.ReadNextRow = True
Tree.AddNewNode(reader.GetValue(0))
loop
Reader.Close
for i = 0 to Tree.Count - 1
Node.Value = Tree.GetNode(i)
cmd.CommandText = "PRAGMA table_info ('" & Node.Text & "')" 'Special SQLite command to find the table's metadata.
reader.Value = cmd.ExecuteReader
Do while Reader.ReadNextRow = True
Node.AddNewNode(Reader.GetValue(1) & " : " & Reader.GetValue(2))
Loop
Reader.Close
next
Con.EndTransaction


Close (SQLlite, Connection) Top

Closes the database connection.
Syntax: Close


CreateSQLTable (SQLlite, Connection) Top

Creates a new table in the database from the data stored in a Table control.
Syntax: CreateSQLTable (Table As DataGrid, SQLTableName As String)
Table - The name of the Table control.
SQLTableName - The name of the newly created table (must be a new table).

Example:
Con.CreateSQLTable ("Table1","customers")


EndTransaction (SQLlite, Connection) Top

Each time you execute a command on the database a transaction is created automatically if it wasn't opened earlier.
When you are executing several commands one after another it is much faster to start with BeginTransaction and finish with EndTransaction.
That way, only one transaction will be created for the entire block.
Syntax: EndTransaction

Example:
Con.BeginTransaction 'Starts a block of I/O with the database.
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'" 'Finds all the tables in this database
Reader.Value = cmd.ExecuteReader
Do while reader.ReadNextRow = True
Tree.AddNewNode(reader.GetValue(0))
loop
Reader.Close
for i = 0 to Tree.Count - 1
Node.Value = Tree.GetNode(i)
cmd.CommandText = "PRAGMA table_info ('" & Node.Text & "')" 'Special SQLite command to find the table's metadata.
reader.Value = cmd.ExecuteReader
Do while Reader.ReadNextRow = True
Node.AddNewNode(Reader.GetValue(1) & " : " & Reader.GetValue(2))
Loop
Reader.Close
next
Con.EndTransaction


New1 (SQLlite, Connection) Top

Initializes a Connection object.
Syntax: New1


Open (SQLlite, Connection) Top

Opens a connection to the specified database file.
The file will be created if it do not exist.
Syntax: Open (ConnectionString As String)
ConnectionString - Must be of the following syntax: Data Source = yourfile.

Example: 'Opens the database which is located in the same folder of the application.
Con.Open("Data Source = " & AppPath & "\Northwind.sl3")


Value (SQLlite, Connection) Top

Gets a reference to a Connection object.
It is used to create a new Command object.
Syntax: Value

Example:
Cmd.New1("",con.Value)


BytesToBLOB (SQLlite, Command) Top

Converts an array of bytes to BLOB type before saving it in a database.
Syntax: BytesToBLOB (Data As Byte() ) As String

Example:
cmd.CommandText = "INSERT INTO data VALUES(" & cmd.BytesToBLOB (data()) & ")"


CommandText (SQLlite, Command) Top

Gets or sets the command string.
Syntax: CommandText

Example:
Cmd.CommandText = "CREATE TABLE products (ProductsID REAL, Name TEXT)"
Cmd.ExecuteNonQuery


ExecuteNonQuery (SQLlite, Command) Top

Executes a command and returns the number of rows affected by it.
Syntax: ExecuteNonQuery

Example:
Cmd.CommandText = "CREATE TABLE products (ProductsID REAL, Name TEXT)"
Cmd.ExecuteNonQuery


ExecuteReader (SQLlite, Command Top)

Executes the command and returns a DataReader which you can use to read the results.
Syntax: ExecuteReader

Example:
Cmd.CommandText = "SELECT * FROM products"
Reader.Value = Cmd.ExecuteReader


ExecuteTable (SQLlite, Command) Top

Executes the command and fills the table control with the result.
Syntax: ExecuteTable (Table As DataGrid, Maximum As Int32)
Table - The name of the table control. The contents of the table will be cleared before the operation.
Maximum - Maximum number of rows to return. Setting this value to 0 will return all the rows available.

Example: (taken from the SQL example)
'Executes the SQL command
Sub mnuExecute_Click
cmd.CommandText = txtCommand.Text
'Executes the user query and fills the table with the result.
cmd.ExecuteTable("table1",500) 'Limits the number of rows to 500 (change to 0 for unlimited)
End Sub


FileToBLOB (SQLlite, Command) Top

Loads the data of the specified file and converts it to BLOB type before saving it in a database.
Syntax: FileToBLOB (File As String) As String

Example:
'con is a Connection object, cmd is a Command object and reader is a DataReader object.
Sub Globals

End Sub

Sub App_Start
Form1.Show
con.New1
reader.New1
con.Open("Data Source = " & AppPath & "\1.db3") 'Opens the database.
cmd.New1("CREATE TABLE IF NOT EXISTS pictures (name TEXT, image BLOB)",con.Value)
cmd.ExecuteNonQuery

'Save the image in the database (change the image name to an existing image file).
cmd.CommandText = "INSERT INTO pictures values('smiley.gif'," & cmd.FileToBLOB(AppPath & "\smiley.gif") & ")"
cmd.ExecuteNonQuery

'Load the image from the database.
cmd.CommandText = "SELECT image FROM pictures"
reader.Value = cmd.ExecuteReader
reader.ReadNextRow
Form1.Image = reader.GetImage(0)
End Sub


New1 (SQLlite, Command) Top

Initializes a Command object.
Syntax: New1 (CommandText As String, Connection As SQLiteConnection)

Example:
Cmd.New1 ("",Con.Value)


Close (SQLlite, DataReader) Top

Closes the current DataReader access.
Syntax: Close

You must use Close before trying to execute another command.


FieldCount (SQLlite, DataReader) Top

Returns the number of fields (columns) returned by the last execute.
Syntax: FieldCount


GetBytes (SQLlite, DataReader) Top

Returns an array of bytes from a BLOB data.
Syntax: GetBytes (Index As Int32) As Byte()
Index - The column index.

Example:
reader.ReadNextRow
data() = reader.GetBytes(0)


GetImage (SQLlite, DataReader) Top

Returns an image from an image saved in the database.
Syntax: GetImage (Index As Int32) As Bitmap
Index - The column number.

Example:
'con is a Connection object, cmd is a Command object and reader is a DataReader object.
Sub Globals

End Sub

Sub App_Start
Form1.Show
con.New1
reader.New1
con.Open("Data Source = " & AppPath & "\1.db3") 'Opens the database.
cmd.New1("CREATE TABLE IF NOT EXISTS pictures (name TEXT, image BLOB)",con.Value)
cmd.ExecuteNonQuery

'Save the image in the database (change the image name to an existing image file).
cmd.CommandText = "INSERT INTO pictures values('smiley.gif'," & cmd.FileToBLOB(AppPath & "\smiley.gif") & ")"
cmd.ExecuteNonQuery

'Load the image from the database.
cmd.CommandText = "SELECT image FROM pictures"
reader.Value = cmd.ExecuteReader
reader.ReadNextRow
Form1.Image = reader.GetImage(0)
End Sub


GetValue (SQLlite, DataReader) Top

Returns the value of the specified field in the current row.
Syntax: GetValue (Index As Int32) As String

Example:
Cmd.CommandText = "SELECT * FROM Products"
Reader.Value = Cmd.ExecuteReader
Do While Reader.ReadNextRow = TRUE
ListBox1.Add(Reader.GetValue(0))
Loop
Reader.Close


IsDBNull (SQLlite, DataReader) Top

Checks whether the field is null (nothing).
Syntax: IsDBNull (Index As Int32) As Boolean


New1 (SQLlite, DataReader) Top

Initializes a DataReader object.
Syntax: New1


ReadNextRow (SQLlite, DataReader) Top

Reads the next row.
Returns true if it was successful.
Syntax: ReadNextRow

Example:
Cmd.CommandText = "SELECT * FROM Products"
Reader.Value = Cmd.ExecuteReader
Do While Reader.ReadNextRow = TRUE
ListBox1.Add(Reader.GetValue(0))
Loop
Reader.Close


Value (SQLlite, DataReader) Top

Gets or sets a reference to a DataReader object.
Syntax: Value