Overview

Next

---

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).

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