SQLite Class - Easier Database Stuff

Hello, I thought I would share my basic Sqlite class for those who are interesting in learning how to use unity with it or those who are looking to implement something similar.

This will help create cleaner code for those who need to use databases.
Ultimately I want to create an editor tool for database so that we can have another tool on top of Unity's awesome-ness.

"Update - 8/14 I added a Create Table Function"
"Update - 8/14 I added a Insert Functions"

The Class

Code:  

  
  

   
   

     
     

      
      import          System.
      
      Data;  
      
      // we import our  data class
     
     

     
     

      
      import          Mono.
      
      Data.
      
      SqliteClient; 
      
      // we import our sqlite client
     
     

     
     

      
       
     
     

     
     

      
      class dbAccess 
      
      {
     
     

     
     

      
          
      
      // variables for basic query access
     
     

     
     

      
          
      
      private 
      
      var connection : 
      
      String;
     
     

     
     

      
          
      
      private 
      
      var dbcon : IDbConnection;
     
     

     
     

      
          
      
      private 
      
      var dbcmd : IDbCommand;
     
     

     
     

      
          
      
      private 
      
      var reader : IDataReader;
     
     

     
     

      
          
     
     

     
     

      
          
      
      function OpenDB
      
      (p : 
      
      String
      
      )
      
      {
     
     

     
     

      
          connection = 
      
      "URI=file:" + p; 
      
      // we set the connection to our database
     
     

     
     

      
          dbcon = 
      
      new SqliteConnection
      
      (connection
      
      );
     
     

     
     

      
          dbcon.
      
      Open
      
      (
      
      );
     
     

     
     

      
          
      
      }
     
     

     
     

      
          
     
     

     
     

      
          
      
      function BasicQuery
      
      (q : 
      
      String, r : 
      
      boolean
      
      )
      
      { 
      
      // run a baic Sqlite query
     
     

     
     

      
              dbcmd = dbcon.
      
      CreateCommand
      
      (
      
      ); 
      
      // create empty command
     
     

     
     

      
              dbcmd.
      
      CommandText = q; 
      
      // fill the command
     
     

     
     

      
              reader = dbcmd.
      
      ExecuteReader
      
      (
      
      ); 
      
      // execute command which returns a reader
     
     

     
     

      
              
      
      if
      
      (r
      
      )
      
      { 
      
      // if we want to return the reader
     
     

     
     

      
              
      
      return reader; 
      
      // return the reader
     
     

     
     

      
              
      
      }
     
     

     
     

      
          
      
      }
     
     

     
     

      
          
     
     

     
     

      
          
      
      function CreateTable
      
      (
      
      name : 
      
      String, col : 
      
      Array, colType : 
      
      Array
      
      )
      
      { 
      
      // Create a table, name, column array, column type array
     
     

     
     

      
              
      
      var query : 
      
      String;
     
     

     
     

      
              query  = 
      
      "CREATE TABLE " + 
      
      name + 
      
      "(" + col
      
      [
      
      0
      
      ] + 
      
      " " + colType
      
      [
      
      0
      
      ];
     
     

     
     

      
              
      
      for
      
      (
      
      var i=
      
      1; i<col.
      
      length; i++
      
      )
      
      {
     
     

     
     

      
                  query += 
      
      ", " + col
      
      [i
      
      ] + 
      
      " " + colType
      
      [i
      
      ];
     
     

     
     

      
              
      
      }
     
     

     
     

      
              query += 
      
      ")";
     
     

     
     

      
              dbcmd = dbcon.
      
      CreateCommand
      
      (
      
      ); 
      
      // create empty command
     
     

     
     

      
              dbcmd.
      
      CommandText = query; 
      
      // fill the command
     
     

     
     

      
              reader = dbcmd.
      
      ExecuteReader
      
      (
      
      ); 
      
      // execute command which returns a reader
     
     

     
     

      
          
     
     

     
     

      
          
      
      }
     
     

     
     

      
          
     
     

     
     

      
          
      
      function InsertIntoSingle
      
      (tableName : 
      
      String, colName : 
      
      String, 
      
      value : 
      
      String
      
      )
      
      { 
      
      // single insert 
     
     

     
     

      
              
      
      var query : 
      
      String;
     
     

     
     

      
              query = 
      
      "INSERT INTO " + tableName + 
      
      "(" + colName + 
      
      ") " + 
      
      "VALUES (" + 
      
      value + 
      
      ")";
     
     

     
     

      
              dbcmd = dbcon.
      
      CreateCommand
      
      (
      
      ); 
      
      // create empty command
     
     

     
     

      
              dbcmd.
      
      CommandText = query; 
      
      // fill the command
     
     

     
     

      
              reader = dbcmd.
      
      ExecuteReader
      
      (
      
      ); 
      
      // execute command which returns a reader
     
     

     
     

      
          
      
      }
     
     

     
     

      
          
     
     

     
     

      
          
      
      function InsertIntoSpecific
      
      (tableName : 
      
      String, col : 
      
      Array, values : 
      
      Array
      
      )
      
      { 
      
      // Specific insert with col and values
     
     

     
     

      
              
      
      var query : 
      
      String;
     
     

     
     

      
              query = 
      
      "INSERT INTO " + tableName + 
      
      "(" + col
      
      [
      
      0
      
      ];
     
     

     
     

      
              
      
      for
      
      (
      
      var i=
      
      1; i<col.
      
      length; i++
      
      )
      
      {
     
     

     
     

      
                  query += 
      
      ", " + col
      
      [i
      
      ];
     
     

     
     

      
              
      
      }
     
     

     
     

      
              query += 
      
      ") VALUES (" + values
      
      [
      
      0
      
      ];
     
     

     
     

      
              
      
      for
      
      (i=
      
      1; i<values.
      
      length; i++
      
      )
      
      {
     
     

     
     

      
                  query += 
      
      ", " + values
      
      [i
      
      ];
     
     

     
     

      
              
      
      }
     
     

     
     

      
              query += 
      
      ")";
     
     

     
     

      
              dbcmd = dbcon.
      
      CreateCommand
      
      (
      
      );
     
     

     
     

      
              dbcmd.
      
      CommandText = query; 
     
     

     
     

      
              reader = dbcmd.
      
      ExecuteReader
      
      (
      
      );
     
     

     
     

      
          
      
      }
     
     

     
     

      
          
     
     

     
     

      
          
      
      function InsertInto
      
      (tableName : 
      
      String, values : 
      
      Array
      
      )
      
      { 
      
      // basic Insert with just values
     
     

     
     

      
              
      
      var query : 
      
      String;
     
     

     
     

      
              query = 
      
      "INSERT INTO " + tableName + 
      
      " VALUES (" + values
      
      [
      
      0
      
      ];
     
     

     
     

      
              
      
      for
      
      (
      
      var i=
      
      1; i<values.
      
      length; i++
      
      )
      
      {
     
     

     
     

      
                  query += 
      
      ", " + values
      
      [i
      
      ];
     
     

     
     

      
              
      
      }
     
     

     
     

      
              query += 
      
      ")";
     
     

     
     

      
              dbcmd = dbcon.
      
      CreateCommand
      
      (
      
      );
     
     

     
     

      
              dbcmd.
      
      CommandText = query; 
     
     

     
     

      
              reader = dbcmd.
      
      ExecuteReader
      
      (
      
      ); 
     
     

     
     

      
          
      
      }
     
     

     
     

      
          
     
     

     
     

      
          
      
      function SingleSelectWhere
      
      (tableName : 
      
      String, itemToSelect : 
      
      String, wCol : 
      
      String, wPar : 
      
      String, wValue : 
      
      String
      
      )
      
      { 
      
      // Selects a single Item
     
     

     
     

      
              
      
      var query : 
      
      String;
     
     

     
     

      
              query = 
      
      "SELECT " + itemToSelect + 
      
      " FROM " + tableName + 
      
      " WHERE " + wCol + wPar + wValue; 
     
     

     
     

      
              dbcmd = dbcon.
      
      CreateCommand
      
      (
      
      );
     
     

     
     

      
              dbcmd.
      
      CommandText = query; 
     
     

     
     

      
              reader = dbcmd.
      
      ExecuteReader
      
      (
      
      );
     
     

     
     

      
              
      
      var readArray = 
      
      new 
      
      Array
      
      (
      
      );
     
     

     
     

      
              
      
      while
      
      (reader.
      
      Read
      
      (
      
      )
      
      )
      
      { 
     
     

     
     

      
                  readArray.
      
      Push
      
      (reader.
      
      GetString
      
      (
      
      0
      
      )
      
      ); 
      
      // Fill array with all matches
     
     

     
     

      
              
      
      }
     
     

     
     

      
              
      
      return readArray; 
      
      // return matches
     
     

     
     

      
          
      
      }
     
     

     
     

      
       
     
     

     
     

      
          
     
     

     
     

      
          
      
      function CloseDB
      
      (
      
      )
      
      {
     
     

     
     

      
              reader.
      
      Close
      
      (
      
      ); 
      
      // clean everything up
     
     

     
     

      
              reader = 
      
      null; 
     
     

     
     

      
              dbcmd.
      
      Dispose
      
      (
      
      ); 
     
     

     
     

      
              dbcmd = 
      
      null; 
     
     

     
     

      
              dbcon.
      
      Close
      
      (
      
      ); 
     
     

     
     

      
              dbcon = 
      
      null; 
     
     

     
     

      
          
      
      }
     
     

     
     

      
          
     
     

     
     

      
      }
     
     
  
  

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

Code:  

  
  

   
   

     
     

      
      var db : dbAccess;
     
     

     
     

      
      function 
      
      Start
      
      (
      
      )
      
      {
     
     

     
     

      
          db = 
      
      new dbAccess
      
      (
      
      );
     
     

     
     

      
          db.
      
      OpenDB
      
      (
      
      "myDB.sqdb"
      
      );
     
     

     
     

      
          
      
      var tableName = 
      
      "myTable";
     
     

     
     

      
          
      
      var columnNames = 
      
      new 
      
      Array
      
      (
      
      "firstName",
      
      "lastName"
      
      );
     
     

     
     

      
          
      
      var columnValues = 
      
      new 
      
      Array
      
      (
      
      "text",
      
      "text"
      
      );
     
     

     
     

      
          db.
      
      CreateTable
      
      (tableName,columnNames,columnValues
      
      );
     
     

     
     

      
          db.
      
      CloseDB
      
      (
      
      );
     
     

     
     

      
      }
     
     
  
  

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

Code:  

  
  

   
   

     
     

      
      var db : dbAccess;
     
     

     
     

      
      function 
      
      Start
      
      (
      
      )
      
      {
     
     

     
     

      
          db = 
      
      new dbAccess
      
      (
      
      );
     
     

     
     

      
          db.
      
      OpenDB
      
      (
      
      "myDB.sqdb"
      
      );
     
     

     
     

      
          
      
      var tableName = 
      
      "myTable";
     
     

     
     

      
          
      
      // IMPORTANT remember to add single ' to any strings, do not add them to numbers!
     
     

     
     

      
          
      
      var values = 
      
      new 
      
      Array
      
      (
      
      "'Bob'",
      
      "'Sagat'"
      
      );
     
     

     
     

      
          db.
      
      InsertInto
      
      (tableName, values
      
      );
     
     

     
     

      
          db.
      
      CloseDB
      
      (
      
      );
     
     

     
     

      
      }
     
     
  
  

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:  

  
  

   
   

     
     

      
      var db : dbAccess;
     
     

     
     

      
      function 
      
      Start
      
      (
      
      )
      
      {
     
     

     
     

      
          db = 
      
      new dbAccess
      
      (
      
      );
     
     

     
     

      
          db.
      
      OpenDB
      
      (
      
      "myDB.sqdb"
      
      );
     
     

     
     

      
          
      
      var tableName = 
      
      "myTable";
     
     

     
     

      
          
      
      // table name, I want to return everyone whose first name is Bob when their last name is = to Sagat, this returs an array
     
     

     
     

      
          
      
      var resultArray = db.
      
      SingleSelectWhere
      
      (tableName, 
      
      "firstName", 
      
      "lastName",
      
      "=",
      
      "'Sagat'"
      
      ); 
      
      // Remember the '' on String values
     
     

     
     

      
          
      
      print
      
      (resultArray
      
      [
      
      0
      
      ]
      
      );
     
     

     
     

      
          
      
      // of course you can loop through them all if you wish
     
     

     
     

      
          db.
      
      CloseDB
      
      (
      
      );
     
     

     
     

      
          
     
     

     
     

      
      }