SQL

<html><head> <title>Overview</title> <meta name="generator" content="HelpMaker"> <meta name="keywords" content="Overview"> <link rel="stylesheet" type="text/css" href="hm.css"></head> <body bgcolor="#FFFCEA"> <table width="100%" border="0" cellspacing="0" cellpadding="2" bgcolor="#C0C0C0"> <tr> <td align="left"> <div align="left"><font face="Arial" color="#010101" size="4"><span style=" font-size:14pt; font-family:Arial; color:#010101">Overview</span></font></div> </td> <td align="right"> <font face="Arial" size="2">  <a href="overview.htm">Top</a> <a href="begintransaction.htm">Next</a> </font> </td> </tr></table> <hr><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">The SQL library is based on the popular open source SQL engine, SQLite 3.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">With SQLite the database is stored in a single file.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">SQLite site: </span></font><a href="http://www.sqlite.org" target="_blank"><font face="Times New Roman" color="#0000ff"><span style=" font-size:12pt; font-family:Times New Roman; color:#0000ff"><u>www.sqlite.org</u></span></font></a></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">In the above link you can find many explanations about the SQLite engine and syntax.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">This implementation is based on the open source .Net library named System.Data.SQLite.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">System.Data.SQLite site: </span></font><a href="http://sqlite.phxsoftware.com/" target="_blank"><font face="Times New Roman" color="#0000ff"><span style=" font-size:12pt; font-family:Times New Roman; color:#0000ff"><u>http://sqlite.phxsoftware.com/</u></span></font></a></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">The SQL library requires that the .Net Compact Framework 2.0 will be installed on the device.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000"><br></span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">Using this library you can use larger databases and with much more options than using only the Table control.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">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.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">This help manual do not cover the SQLite syntax. </span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">The syntax is covered here: </span></font><a href="http://www.sqlite.org/lang.html" target="_blank"><font face="Times New Roman" color="#0000ff"><span style=" font-size:12pt; font-family:Times New Roman; color:#0000ff"><u>http://www.sqlite.org/lang.html</u></span></font></a></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000"><br></span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">The SQL library includes three types of objects:</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">- 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.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">- Command - Used to execute SQL commands. </span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">- DataReader - Allows forward only access to the result of an executed command. The reading is done row after row.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000"><br></span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000"><b>Change: </b>Note that when you distribute an application that uses this library you should copy the distribute the following files as well:</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">Desktop:</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">System.Data.SQLite.DLL (desktop version)</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">Device:</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">System.Data.SQLite.DLL (device version)</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">SQLite.Interop.060.dll</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000"><br></span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">These files are located under Libraries\NativeSQL folder.</span></font></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000">A simple example of using this library along with Microsoft's Northwind database sample (converted to SQLite) can be downloaded here: </span></font><a href="http://www.basic4ppc.com/Downloads.html" target="_blank"><font face="Times New Roman" color="#0000ff"><span style=" font-size:12pt; font-family:Times New Roman; color:#0000ff"><u>www.basic4ppc.com/Downloads</u></span></font></a></div><div align="left"><font face="Times New Roman"><span style=" font-size:12pt; font-family:Times New Roman; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Sub </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">Globals</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">End Sub</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Sub </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">App_Start</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Form1.Show</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Con.New1</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Reader.New1</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Tree.New1("Form2", 5, 5, Form2.Width - 10, Form2.Height - 10)</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Node.New1</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Cmd.New1("",con.Value)</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Con.Open("Data Source = " & </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">AppPath</span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"> & "\Northwind.sl3") </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Opens a connection with the database.</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">AddEvent</span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">("btnExecute",Click,"mnuExecute_Click") </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'The button and the menu use the same sub.</span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">End Sub</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Executes the SQL command</span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Sub </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">mnuExecute_Click</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      cmd.CommandText = txtCommand.Text</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Executes the user query and fills the table with the result.</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      cmd.ExecuteTable("table1",500) </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Limits the number of rows to 500 (change to 0 for unlimited)</span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">End Sub</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Sub </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">mnuShowScheme_Click</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Form2.Show</span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">End Sub</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Sub </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">mnuRefresh_Click </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Fills the TreeView with the tables and columns data</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">for </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">i = 0 </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">to </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">Tree.Count - 1</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            Tree.RemoveNodeAt(0)</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">next</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Con.BeginTransaction </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Starts a block of I/O with the database.</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      cmd.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'" </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Finds all the tables in this database</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Reader.Value = cmd.ExecuteReader</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Do while </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">reader.ReadNextRow = True </span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            Tree.AddNewNode(reader.GetValue(0))</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">loop</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Reader.Close</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">for </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">i = 0 </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">to </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">Tree.Count - 1</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            Node.Value = Tree.GetNode(i)</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            cmd.CommandText = "PRAGMA table_info ('" & Node.Text & "')" </span></font><font face="Courier New" color="#008000" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#008000">'Special SQLite command to find the table's metadata.</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            reader.Value = cmd.ExecuteReader</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Do while </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">Reader.ReadNextRow = True</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">                  Node.AddNewNode(Reader.GetValue(1) & " : " & Reader.GetValue(2))</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Loop</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">            Reader.Close</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">next</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Con.EndTransaction</span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">End Sub</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Sub </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">Form2_Show</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">if </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">Tree.Count = 0 </span></font><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">then </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">mnuRefresh_Click</span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">End Sub</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000"><br></span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">Sub </span></font><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">Form1_Close</span></font></div><div align="left"><font face="Courier New" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#000000">      Con.Close</span></font></div><div align="left"><font face="Courier New" color="#0000ff" size="2"><span style=" font-size:10pt; font-family:Courier New; color:#0000ff">End Sub</span></font></div> </body></html>