【Unity】连接sqlite数据库操作C#版，

【Unity】连接sqlite数据库操作C#版，

unity3d有自己对应的sqlite.dll分别需要三个文件
1.Mono.Data.Sqlite.dll
在unity安装文件“Unity\Editor\Data\MonoBleedingEdge\lib\mono”可以找到，注意mono文件夹下面 有对应版本号，可以根据自己的项目来决定选择。

2.System.Data.dll   同上位置一样可以找到 ，不过建议使用2.0版本

3.sqlite3.dll   就在\Unity\Editor下可以找到

除此之外，还需要把这3个文件放在你的项目的这个路径下面：\Assets\Plugins\，没有Plugins文件夹就必须创建这个文件夹，然后将这三个dll文件放在该文件夹下面。当然，如果你想能够在PC上面发布成可执行文件，还需要改动一些地方。在unity3d中的Play Setting ->Other Setting 中将Api Compatibility的等级改为.NET 2.0;那么这些操作做完了以后，如果你的代码写得没有问题，那么你就可以成功了。

好了，上类 SqliteDbHelper：

using UnityEngine;
using System.Collections;
//导入sqlite数据集，也就是Plugins文件夹下的那个dll文件
using Mono.Data.Sqlite;
using System;
//数据集 是formwork2.0 用vs开发要自己引用框架中的System.Data
using System.Data;
public class SqliteDbHelper
{
    /// <summary>
    /// 声明一个连接对象
    /// </summary>
    private SqliteConnection dbConnection;
    /// <summary>
    /// 声明一个操作数据库命令
    /// </summary>
    private SqliteCommand dbCommand;
    /// <summary>
    /// 声明一个读取结果集的一个或多个结果流
    /// </summary>
    private SqliteDataReader reader;
    /// <summary>
    /// 数据库的连接字符串，用于建立与特定数据源的连接
    /// </summary>
    /// <param name="connectionString">数据库的连接字符串，用于建立与特定数据源的连接</param>
    public SqliteDbHelper (string connectionString)
    {
     OpenDB (connectionString);
        Debug.Log(connectionString);
    }
    public void OpenDB (string connectionString)
    {
      try
      {
       dbConnection = new SqliteConnection (connectionString);
       dbConnection.Open();
       Debug.Log ("Connected to db");
      }
      catch(Exception e)
      {
       string temp1 = e.ToString();
       Debug.Log(temp1);
      }
    }
    /// <summary>
    /// 关闭连接
    /// </summary>
    public void CloseSqlConnection ()
    {
      if (dbCommand != null)
      {
                dbCommand.Dispose();
      }
      dbCommand = null;
      if (reader != null)
      {
       reader.Dispose ();
      }
      reader = null;
      if (dbConnection != null)
      {
       dbConnection.Close ();
      }
      dbConnection = null;
      Debug.Log ("Disconnected from db.");
    }
    /// <summary>
    /// 执行查询sqlite语句操作
    /// </summary>
    /// <param name="sqlQuery"></param>
    /// <returns></returns>
    public SqliteDataReader ExecuteQuery (string sqlQuery)
    {
     dbCommand = dbConnection.CreateCommand ();
     dbCommand.CommandText = sqlQuery;
     reader = dbCommand.ExecuteReader ();
     return reader;
    }
    /// <summary>
    /// 查询该表所有数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public SqliteDataReader ReadFullTable (string tableName)
    {
     string query = "SELECT * FROM " + tableName;
     return ExecuteQuery (query);
    }
    /// <summary>
    /// 动态添加表字段到指定表
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="values">字段集合</param>
    /// <returns></returns>
    public SqliteDataReader InsertInto (string tableName, string[] values)
    {
     string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
     for (int i = 1; i < values.Length; ++i)
     {
      query += ", " + values;
     }
     query += ")";
     return ExecuteQuery (query);
    }
    /// <summary>
    /// 动态更新表结构
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="cols">字段集</param>
    /// <param name="colsvalues">对于集合值</param>
    /// <param name="selectkey">要查询的字段</param>
    /// <param name="selectvalue">要查询的字段值</param>
    /// <returns></returns>
    public SqliteDataReader UpdateInto (string tableName, string []cols,
      string []colsvalues,string selectkey,string selectvalue)
    {
         string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0];
         for (int i = 1; i < colsvalues.Length; ++i) {
              query += ", " +cols+" ="+ colsvalues;
         }
          query += " WHERE "+selectkey+" = "+selectvalue+" ";
         return ExecuteQuery (query);
    }
    /// <summary>
    /// 动态删除指定表字段数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="cols">字段</param>
    /// <param name="colsvalues">字段值</param>
    /// <returns></returns>
    public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues)
    {
     string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0];
     for (int i = 1; i < colsvalues.Length; ++i)
        {
              query += " or " +cols+" = "+ colsvalues;
     }
         Debug.Log(query);
         return ExecuteQuery (query);
    }
    /// <summary>
    /// 动态添加数据到指定表
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="cols">字段</param>
    /// <param name="values">值</param>
    /// <returns></returns>
     public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols,
      string[] values)
     {
         if (cols.Length != values.Length)
         {
             throw new SqliteException ("columns.Length != values.Length");
         }
         string query = "INSERT INTO " + tableName + "(" + cols[0];
         for (int i = 1; i < cols.Length; ++i)
         {
             query += ", " + cols;
         }
         query += ") VALUES (" + values[0];
         for (int i = 1; i < values.Length; ++i)
         {
             query += ", " + values;
         }
         query += ")";
         return ExecuteQuery (query);
     }
    /// <summary>
    /// 动态删除表
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
     public SqliteDataReader DeleteContents (string tableName)
     {
         string query = "DELETE FROM " + tableName;
         return ExecuteQuery (query);
     }
    /// <summary>
    /// 动态创建表
    /// </summary>
    /// <param name="name">表名</param>
    /// <param name="col">字段</param>
    /// <param name="colType">类型</param>
    /// <returns></returns>
     public SqliteDataReader CreateTable (string name, string[] col, string[] colType)
     {
         if (col.Length != colType.Length)
         {
             throw new SqliteException ("columns.Length != colType.Length");
         }
         string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
         for (int i = 1; i < col.Length; ++i)
         {
             query += ", " + col + " " + colType;
         }
         query += ")";
         Debug.Log(query);
         return ExecuteQuery (query);
     }
    /// <summary>
    /// 根据查询条件 动态查询数据信息
    /// </summary>
    /// <param name="tableName">表</param>
    /// <param name="items">查询数据集合</param>
    /// <param name="col">字段</param>
    /// <param name="operation">操作</param>
    /// <param name="values">值</param>
    /// <returns></returns>
     public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)
     {
         if (col.Length != operation.Length || operation.Length != values.Length)
         {
             throw new SqliteException ("col.Length != operation.Length != values.Length");
         }
         string query = "SELECT " + items[0];
         for (int i = 1; i < items.Length; ++i)
         {
       query += ", " + items;
         }
          query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
         for (int i = 1; i < col.Length; ++i)
         {
             query += " AND " + col + operation + "'" + values[0] + "' ";
         }
         return ExecuteQuery (query);
     }
}

好了 sqlite数据操作类写好后：本来应该按照编码规范来写  因为我本身以前做c#的所有一般使用三层架构
Modle实例化对象类 ，DAl数据处理类，IDal数据接口类，Bll业务逻辑类
但是呢，目前只为测试 因此我就不一一详细介绍代码内容了，相信会c#的人都会三层架构，呵呵

ok，言归正传，下面将贴出unity3d 使用上面sqliteDbHelper操作类
SqliteDbTest：

using UnityEngine;
using System.Collections;
using System;
using Mono.Data.Sqlite;
using System.Data;
public class SqliteDbTest : MonoBehaviour {

SqliteDbHelper db ;
int id=1;
void Start ()
{
        db = new SqliteDbHelper("Data Source=./sqlite.db");
        Debug.Log(db.ToString());
   /*
    SqliteDbAccess db = new SqliteDbAccess("data source=mydb1.db");
  db.CreateTable("momo",new string[]{"name","qq","email","blog"},
   new string[]{"text","text","text","text"});
  db.CloseSqlConnection();
   */
  }    public  string name = "";
        public string emls = "";
void OnGUI()
{

  if(GUILayout.Button("create table"))
  {
   db.CreateTable("mytable",new string[]{"id","name","email"},new string[]{"int","varchar(20)","varchar(50)"});
   Debug.Log("create table ok");
  }

  if(GUILayout.Button("insert data"))
  {

   db.InsertInto("mytable",
                new string[] { "" + (++id), "'随风去旅行"+id+"'","'zhangj_live"+id+"@163.com'"});//),"'aaa"+id+"'","'aaa"+id+"@sohu.com'"});

   Debug.Log("insert table ok");
  }


  if(GUILayout.Button("search database"))
        {
            IDataReader sqReader = db.SelectWhere("mytable", new string[]
    {"name","email"},new string[]{"id"},new string[]{"="},new string[]{"2"});
   while (sqReader.Read())
   {

     //Debug.Log(
     name= "name="+sqReader.GetString(sqReader.GetOrdinal("name"));// +
                 emls = "email=" + sqReader.GetString(sqReader.GetOrdinal("email"));
     //);
   }

  }
        if (name != "")
        {
            GUI.Label(new Rect(100, 100, 100, 100), name);
            GUI.Label(new Rect(100, 200, 100, 100), emls);
            //  GUILayout.Label(emls);
        }
  if(GUILayout.Button("close database"))
  {
   db.CloseSqlConnection();
   Debug.Log("close table ok");
  }

}

}