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