A comprehensive tutorial about SQL in Basic4ppc is available here (external link):
The SQL library is based on the popular open source SQL engine, SQLite 3.
With SQLite the database is stored in a single file.
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.
The SQL library requires that the .Net Compact Framework 2.0 will be installed on the
device.
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 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.
Change: Note that when you distribute an application that uses this library you should
copy the distribute the following files as well:
Desktop:
System.Data.SQLite.DLL
Device:
System.Data.SQLite.Device.DLL
SQLite.Interop.060.dll
These files are located under Libraries\NativeSQL folder.
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