*SQL lite*

Back to the start


Overview (SQLlite)
BeginTransaction
Close (Connection)
CreateSQLTable
EndTransaction
New1 (Connection)
Open (Connection)
Value (Connection)
CommandText
ExecuteNonQuery
ExecuteReader
ExecuteTable
New1 (SQLlite Command Object)
Close (SQLlite Command Object)
FieldCount
GetValue (SQLlite Command Object)
IsDBNull
New1 (SQLlite DataReader Object)
Value (SQLlite DataReader Object)

Overview (SQLlite) Top

The SQL library 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: http://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:
http://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: http://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 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 (Connection) Top

Closes the database connection.
Syntax: Close


CreateSQLTable 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 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 (Connection) Top

Initializes a Connection object.
Syntax: New1



Open (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 (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)



CommandText Top

Gets or sets the command string.
Syntax: CommandText

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



ExecuteNonQuery 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 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 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


New1 (SQLlite Command Object) Top

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

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


Close (SQLlite Command Object) Top

Closes the current DataReader access.
Syntax: Close


You must use Close before trying to execute another command.


FieldCount Top

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


GetValue (SQLlite Command Object) 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 Top

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


New1 (SQLlite DataReader Object) Top

Initializes a DataReader object.
Syntax: New1


Value (SQLlite DataReader Object) Top

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