Unity - Local DB - C# - Sqlite

The Class

Code (csharp):

2. import          System.Data;  // we import our  data class
3. import          Mono.Data.SqliteClient; // we import our sqlite client
5. class dbAccess {
6.     // variables for basic query access
7.     private var connection : String;
8.     private var dbcon : IDbConnection;
9.     private var dbcmd : IDbCommand;
10.     private var reader : IDataReader;
11.    
12.     function OpenDB(p : String){
13.     connection = "URI=file:" + p; // we set the connection to our database
14.     dbcon = new SqliteConnection(connection);
15.     dbcon.Open();
16.     }
17.    
18.     function BasicQuery(q : String, r : boolean){ // run a baic Sqlite query
19.         dbcmd = dbcon.CreateCommand(); // create empty command
20.         dbcmd.CommandText = q; // fill the command
21.         reader = dbcmd.ExecuteReader(); // execute command which returns a reader
22.         if(r){ // if we want to return the reader
23.         return reader; // return the reader
24.         }
25.     }
26.    
27.     function CreateTable(name : String, col : Array, colType : Array){ // Create a table, name, column array, column type array
28.         var query : String;
29.         query  = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0];
30.         for(var i=1; i<col.length; i++){
31.             query += ", " + col[i] + " " + colType[i];
32.         }
33.         query += ")";
34.         dbcmd = dbcon.CreateCommand(); // create empty command
35.         dbcmd.CommandText = query; // fill the command
36.         reader = dbcmd.ExecuteReader(); // execute command which returns a reader
37.    
38.     }
39.    
40.     function InsertIntoSingle(tableName : String, colName : String, value : String){ // single insert
41.         var query : String;
42.         query = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES (" + value + ")";
43.         dbcmd = dbcon.CreateCommand(); // create empty command
44.         dbcmd.CommandText = query; // fill the command
45.         reader = dbcmd.ExecuteReader(); // execute command which returns a reader
46.     }
47.    
48.     function InsertIntoSpecific(tableName : String, col : Array, values : Array){ // Specific insert with col and values
49.         var query : String;
50.         query = "INSERT INTO " + tableName + "(" + col[0];
51.         for(var i=1; i<col.length; i++){
52.             query += ", " + col[i];
53.         }
54.         query += ") VALUES (" + values[0];
55.         for(i=1; i<values.length; i++){
56.             query += ", " + values[i];
57.         }
58.         query += ")";
59.         dbcmd = dbcon.CreateCommand();
60.         dbcmd.CommandText = query;
61.         reader = dbcmd.ExecuteReader();
62.     }
63.    
64.     function InsertInto(tableName : String, values : Array){ // basic Insert with just values
65.         var query : String;
66.         query = "INSERT INTO " + tableName + " VALUES (" + values[0];
67.         for(var i=1; i<values.length; i++){
68.             query += ", " + values[i];
69.         }
70.         query += ")";
71.         dbcmd = dbcon.CreateCommand();
72.         dbcmd.CommandText = query;
73.         reader = dbcmd.ExecuteReader();
74.     }
75.    
76.     function SingleSelectWhere(tableName : String, itemToSelect : String, wCol : String, wPar : String, wValue : String){ // Selects a single Item
77.         var query : String;
78.         query = "SELECT " + itemToSelect + " FROM " + tableName + " WHERE " + wCol + wPar + wValue;
79.         dbcmd = dbcon.CreateCommand();
80.         dbcmd.CommandText = query;
81.         reader = dbcmd.ExecuteReader();
82.         var readArray = new Array();
83.         while(reader.Read()){
84.             readArray.Push(reader.GetString(0)); // Fill array with all matches
85.         }
86.         return readArray; // return matches
87.     }
89.    
90.     function CloseDB(){
91.         reader.Close(); // clean everything up
92.         reader = null;
93.         dbcmd.Dispose();
94.         dbcmd = null;
95.         dbcon.Close();
96.         dbcon = null;
97.     }
98.    
99. }

An Example of Creating a Table
You can create two arrays, one being the column headers, and another being the data types.

Code (csharp):

2. var db : dbAccess;
3. function Start(){
4.     db = new dbAccess();
5.     db.OpenDB("myDB.sqdb");
6.     var tableName = "myTable";
7.     var columnNames = new Array("firstName","lastName");
8.     var columnValues = new Array("text","text");
9.     db.CreateTable(tableName,columnNames,columnValues);
10.     db.CloseDB();
11. }

An Example of Easy Insert
This example is an easy way to add a single record

Code (csharp):

1. var db : dbAccess;
2. function Start(){
3.     db = new dbAccess();
4.     db.OpenDB("myDB.sqdb");
5.     var tableName = "myTable";
6.     // IMPORTANT remember to add single ' to any strings, do not add them to numbers!
7.     var values = new Array("'Bob'","'Sagat'");
8.     db.InsertInto(tableName, values);
9.     db.CloseDB();
10. }

An Example of Single WHERE select
I am not done with this class, but this is an example of getting an array of items that match a WHERE clause.

Code (csharp):

1. var db : dbAccess;
2. function Start(){
3.     db = new dbAccess();
4.     db.OpenDB("myDB.sqdb");
5.     var tableName = "myTable";
6.     // table name, I want to return everyone whose first name is Bob when their last name is = to Sagat, this returs an array
7.     var resultArray = db.SingleSelectWhere(tableName, "firstName", "lastName","=","'Sagat'"); // Remember the '' on String values
8.     print(resultArray[0]);
9.     // of course you can loop through them all if you wish
10.     db.CloseDB();
11.    
12. }

de (csharp):

2. using UnityEngine;
3. using System;
4. using Mono.Data.Sqlite;
7. public class DbAccess
8. {
9.     private SqliteConnection dbConnection;
10.     private SqliteCommand dbCommand;
11.     private SqliteDataReader reader;
14.     /// <summary>
15.     ///  string to connect. The simpliest one looks like "URI=file:filename.db"
16.     /// </summary>
17.     /// <param name="connectionString">
18.     /// A <see cref="System.String"/>
19.     /// </param>
20.     public DbAccess (string connectionString)
21.     {
22.         OpenDB (connectionString);
23.     }
25.     /// <summary>
26.     ///  The same as <see cref="DbAccess#Dbaccess" / >
27.     /// </summary>
28.     /// <param name="connectionString">
29.     /// A <see cref="System.String"/>
30.     /// </param>
31.     public void OpenDB (string connectionString)
32.     {
33.         dbConnection = new SqliteConnection (connectionString);
34.         dbConnection.Open ();
35.         Debug.Log ("Connected to db");
36.     }
38.     /// <summary>
39.     /// Closes connection to db
40.     /// </summary>
41.     public void CloseSqlConnection ()
42.     {
43.         if (dbCommand != null) {
44.             dbCommand.Dispose ();
45.         }
46.         dbCommand = null;
47.         if (reader != null) {
48.             reader.Dispose ();
49.         }
50.         reader = null;
51.         if (dbConnection != null) {
52.             dbConnection.Close ();
53.         }
54.         dbConnection = null;
55.         Debug.Log ("Disconnected from db.");
56.     }
59.     /// <summary>
60.     ///  Executes query given by sqlQuery
61.     /// </summary>
62.     /// <param name="sqlQuery">
63.     /// query
64.     /// A <see cref="System.String"/>
65.     /// </param>
66.     /// <returns>
67.     /// null, if any error
68.     /// result of query, otherwise
69.     /// A <see cref="SqliteDataReader"/>
70.     /// </returns>
71.     public SqliteDataReader ExecuteQuery (string sqlQuery)
72.     {
73.         dbCommand = dbConnection.CreateCommand ();
74.         dbCommand.CommandText = sqlQuery;
75.        
76.         reader = dbCommand.ExecuteReader ();
77.        
78.        
79.         return reader;
80.     }
82.     /// <summary>
83.     ///  Selects everything from table
84.     /// </summary>
85.     /// <param name="tableName">
86.     /// name of table
87.     /// A <see cref="System.String"/>
88.     /// </param>
89.     /// <returns>
90.     /// result of query
91.     /// A <see cref="SqliteDataReader"/>
92.     /// </returns>
93.     public SqliteDataReader ReadFullTable (string tableName)
94.     {
95.         string query = "SELECT * FROM " + tableName;
96.         return ExecuteQuery (query);
97.     }
99.     /// <summary>
100.     /// Inserts data into table
101.     /// </summary>
102.     /// <param name="tableName">
103.     /// name of table to insert data
104.     /// A <see cref="System.String"/>
105.     /// </param>
106.     /// <param name="values">
107.     /// array of data in string representation
108.     /// A <see cref="System.String[]"/>
109.     /// </param>
110.     /// <returns>
111.     /// result of query
112.     /// A <see cref="SqliteDataReader"/>
113.     /// </returns>
114.     public SqliteDataReader InsertInto (string tableName, string[] values)
115.     {
116.         string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
117.         for (int i = 1; i < values.Length; ++i) {
118.             query += ", " + values[i];
119.         }
120.         query += ")";
121.         return ExecuteQuery (query);
122.     }
124.     /// <summary>
125.     /// Inserts data into specific columns of table
126.     /// </summary>
127.     /// <param name="tableName">
128.     /// name of table
129.     /// A <see cref="System.String"/>
130.     /// </param>
131.     /// <param name="cols">
132.     /// name of columns
133.     /// A <see cref="System.String[]"/>
134.     /// </param>
135.     /// <param name="values">
136.     /// values
137.     /// A <see cref="System.String[]"/>
138.     /// </param>
139.     /// <returns>
140.     /// result of query
141.     /// A <see cref="SqliteDataReader"/>
142.     /// </returns>
143.     public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values)
144.     {
145.         if (cols.Length != values.Length) {
146.             throw new SqliteException ("columns.Length != values.Length");
147.         }
148.         string query = "INSERT INTO " + tableName + "(" + cols[0];
149.         for (int i = 1; i < cols.Length; ++i) {
150.             query += ", " + cols[i];
151.         }
152.         query += ") VALUES (" + values[0];
153.         for (int i = 1; i < values.Length; ++i) {
154.             query += ", " + values[i];
155.         }
156.         query += ")";
157.         return ExecuteQuery (query);
158.     }
160.     /// <summary>
161.     /// deletes any data from table
162.     /// </summary>
163.     /// <param name="tableName">
164.     /// table name
165.     /// A <see cref="System.String"/>
166.     /// </param>
167.     /// <returns>
168.     /// result of query
169.     /// A <see cref="SqliteDataReader"/>
170.     /// </returns>
171.     public SqliteDataReader DeleteContents (string tableName)
172.     {
173.         string query = "DELETE FROM " + tableName;
174.         return ExecuteQuery (query);
175.     }
177.     /// <summary>
178.     /// Creates table with specified columns
179.     /// </summary>
180.     /// <param name="name">
181.     /// table name to be created
182.     /// A <see cref="System.String"/>
183.     /// </param>
184.     /// <param name="col">
185.     /// array, containing names of columns
186.     /// A <see cref="System.String[]"/>
187.     /// </param>
188.     /// <param name="colType">
189.     /// array, containing types of columns
190.     /// A <see cref="System.String[]"/>
191.     /// </param>
192.     /// <returns>
193.     /// result of query
194.     /// A <see cref="SqliteDataReader"/>
195.     /// </returns>
196.     public SqliteDataReader CreateTable (string name, string[] col, string[] colType)
197.     {
198.         if (col.Length != colType.Length) {
199.             throw new SqliteException ("columns.Length != colType.Length");
200.         }
201.         string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
202.         for (int i = 1; i < col.Length; ++i) {
203.             query += ", " + col[i] + " " + colType[i];
204.         }
205.         query += ")";
206.         return ExecuteQuery (query);
207.     }
209.     /// <summary>
210.     /// Selects from table with specified parameters.
211.     /// Ex: SelectWhere("puppies", new string[] = {"breed"}, new string[] = {"earType"}, new string[] = {"="}, new string[] = {"floppy"});
212.     /// the same as command: SELECT breed FROM puppies WHERE earType = floppy
213.     /// </summary>
214.     /// <param name="tableName">
215.     /// name of table to select
216.     /// A <see cref="System.String"/>
217.     /// </param>
218.     /// <param name="items">
219.     /// item names
220.     /// A <see cref="System.String[]"/>
221.     /// </param>
222.     /// <param name="col">
223.     /// array, containing columns of parameters
224.     /// A <see cref="System.String[]"/>
225.     /// </param>
226.     /// <param name="operation">
227.     /// A <see cref="System.String[]"/>
228.     /// </param>
229.     /// <param name="values">
230.     /// A <see cref="System.String[]"/>
231.     /// </param>
232.     /// <returns>
233.     /// result of query
234.     /// A <see cref="SqliteDataReader"/>
235.     /// </returns>
236.     public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)
237.     {
238.         if (col.Length != operation.Length || operation.Length != values.Length) {
239.             throw new SqliteException ("col.Length != operation.Length != values.Length");
240.         }
241.         string query = "SELECT " + items[0];
242.         for (int i = 1; i < items.Length; ++i) {
243.             query += ", " + items[i];
244.         }
245.         query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
246.         for (int i = 1; i < col.Length; ++i) {
247.             query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
248.         }
249.        
250.         return ExecuteQuery (query);
251.        
252.     }
253.    
254.    
255.    
256. }

de (JavaScript):

1. #pragma strict
2. /*  Script for testing out SQLite in Javascript
3.           2011 - Alan Chatham
4.           Released into the public domain
5.         This script is a GUI script - attach it to your main camera.
6.         It creates/opens a SQLite database, and with the GUI you can read and write to it.
7.                                         */
8. // This is the file path of the database file we want to use
9. // Right now, it'll load TestDB.sqdb in the project's root folder.
10. // If one doesn't exist, it will be automatically created.
11. public var DatabaseName : String = "TestDB.sqdb";
12. // This is the name of the table we want to use
13. public var TableName : String = "TestTable";
14. var db : dbAccess;
15. function Start() {
16.     // Give ourselves a dbAccess object to work with, and open it
17.     db = new dbAccess();
18.     db.OpenDB(DatabaseName);
19.     // Let's make sure we've got a table to work with as well!
20.     var tableName = TableName;
21.     //var columnNames = new Array("firstName","lastName");
22.     var columnNames = new Array("firstName","lastName", "age"); // o negocio e mais embaixo
23.     //var columnValues = new Array("text","text");
24.     var columnValues = new Array("text","text", "text");
25.     try {
26.         db.CreateTable(tableName,columnNames,columnValues);
27.     }
28.     catch(e) {// Do nothing - our table was already created
29.         //- we don't care about the error, we just don't want to see it
30.     }
31. }
32. // These variables just hold info to display in our GUI
33. var firstName : String = "First Name";
34. var lastName : String = "Last Name";
35. var age : String = "Age";
36. var DatabaseEntryStringWidth = 100;
37. var scrollPosition : Vector2;
38. var databaseData : ArrayList = new ArrayList();
39. // This GUI provides us with a way to enter data into our database
40. //  as well as a way to view it
41. function OnGUI() {
42.     GUI.Box(Rect (25,25,Screen.width - 50, Screen.height - 50),"");
43.     GUILayout.BeginArea(Rect(50, 50, Screen.width - 100, Screen.height - 100));
44.     // This first block allows us to enter new entries into our table
45.         GUILayout.BeginHorizontal();
46.             firstName = GUILayout.TextField(firstName, GUILayout.Width (DatabaseEntryStringWidth));
47.             lastName = GUILayout.TextField(lastName, GUILayout.Width (DatabaseEntryStringWidth));
48.             age = GUILayout.TextField(age, GUILayout.Width (DatabaseEntryStringWidth));
49.         GUILayout.EndHorizontal();
50.         if (GUILayout.Button("Add to database")) {
51.             // Insert the data
52.            //InsertRow(firstName,lastName);
53.            InsertRow(firstName,lastName,age);
54.             // And update the readout of the database
55.             databaseData = ReadFullTable();
56.         }
57.         // This second block gives us a button that will display/refresh the contents of our database
58.         GUILayout.BeginHorizontal();
59.             if (GUILayout.Button ("Read Database"))
60.                 databaseData = ReadFullTable();
61.             if (GUILayout.Button("Clear"))
62.                 databaseData.Clear();
63.         GUILayout.EndHorizontal();
64.         GUILayout.Label("Database Contents");
65.         scrollPosition = GUILayout.BeginScrollView(scrollPosition, GUILayout.Height(100));
66.             for (var line : ArrayList in databaseData) {
67.                 GUILayout.BeginHorizontal();
68.                 for (var s in line) {
69.                     GUILayout.Label(s.ToString(), GUILayout.Width(DatabaseEntryStringWidth));
70.                 }
71.                 GUILayout.EndHorizontal();
72.             }
73.         GUILayout.EndScrollView();
74.         if (GUILayout.Button("Delete All Data")) {
75.             DeleteTableContents();
76.             databaseData = ReadFullTable();
77.         }
78.     GUILayout.EndArea();
79. }
80. // Wrapper function for inserting our specific entries into our specific database and table for this file
81. //function InsertRow(firstName:String, lastName:String) {
82. function InsertRow(firstName:String, lastName:String, age:String) { //
83.   //var values = new Array(("'"+firstName+"'"),("'"+lastName+"'"));
84.     var values = new Array(("'"+firstName+"'"),("'"+lastName+"'"),("'"+age+"'"));
85.     db.InsertInto(TableName, values);
86. }
87. // Wrapper function, so we only mess with our table.
88. function ReadFullTable() {
89.     return db.ReadFullTable(TableName);
90. }
91. // Another wrapper function...
92. function DeleteTableContents() {
93.     db.DeleteTableContents(TableName);
94. }