Overview
 Top Next

A comprehensive tutorial about SQL in Basic4ppc is available here (external link):
http://www.basic4ppc.com/tutorials/SQL/TOC.htm

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.

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.

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