devbox
凡人多烦事01
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

unity连接sqlite数据库操作_unity using unity.visualscripting.dependencies.sql

作者：凡人多烦事01 | 2024-02-21 22:51:03

赞

踩

unity using unity.visualscripting.dependencies.sqlite;

原文链接：http://www.unitymanual.com/bbs/thread-198-1-1.html

最近几天在讨论游戏本地数据存储方式，考虑到跨平台性。最开始打算用xml，但是考虑到xml文件多而杂，而且后期游戏更新。也不是很方便，于是提出了采用sqlite，可是没想到的是原本信心满满的我却遭到了闭门羹，呵呵....

原来，不是用 以前做c#  winform应用那套dll， unity 3d有自己对应的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;那么这些操作做完了以后，如果你的代码写得没有 问题 ，那么你就可以成功了。

好啦 ，前面准工作完成 下面就直接看测试代码吧
首先是一个 Sqlite的数据库操作类，该类是我改编的 网上也有很多，不过好像是js编写，于是自己动手改编成了c#希望能对大家有用，呵呵...

好了，上类 SqliteDbHelper：

  1. using UnityEngine;
  2. using System.Collections;
  3. using Mono.Data.Sqlite;<span style="font-family: 'microsoft yahei';">//导入sqlite数据集，也就是Plugins文件夹下的那个dll文件</span>
  4. using System;<span style="font-family: 'microsoft yahei';">//数据集 是formwork2.0 用vs开发要自己引用框架中的System.Data</span>
  5. using System.Data;
  1.  
  2. public class SqliteDbHelper
  3. {
  4.     /// <summary>
  5.     /// 声明一个连接对象
  6.     /// </summary>
  7.     private SqliteConnection dbConnection;
  8.     /// <summary>
  9.     /// 声明一个操作数据库命令
  10.     /// </summary>
  11.     private SqliteCommand dbCommand;
  12.     /// <summary>
  13.     /// 声明一个读取结果集的一个或多个结果流
  14.     /// </summary>
  15.     private SqliteDataReader reader;
  16.     /// <summary>
  17.     /// 数据库的连接字符串，用于建立与特定数据源的连接
  18.     /// </summary>
  19.     /// <param name="connectionString">数据库的连接字符串，用于建立与特定数据源的连接</param>
  20.     public SqliteDbHelper (string connectionString)
  21.     {
  22.      <span style="white-space:pre">	</span>OpenDB(connectionString);
  23.         Debug.Log(connectionString);
  24.     }
  25.     public void OpenDB (string connectionString)
  26.     {
  27.       try
  28.       {
  29.        dbConnection = new SqliteConnection (connectionString);
  30.        dbConnection.Open();
  31.        Debug.Log ("Connected to db");
  32.       }
  33.       catch(Exception e)
  34.       {
  35.        string temp1 = e.ToString();
  36.        Debug.Log(temp1);
  37.       }
  38.     }
  39.     /// <summary>
  40.     /// 关闭连接
  41.     /// </summary>
  42.     public void CloseSqlConnection ()
  43.     {
  44.       if (dbCommand != null)
  45.       {
  46.       <span style="white-space:pre">	</span>dbCommand.Dispose();
  47.       }
  48.       dbCommand = null;
  49.       if (reader != null)
  50.       {
  51.         reader.Dispose ();
  52.       }
  53.       reader = null;
  54.       if (dbConnection != null)
  55.       {
  56.         dbConnection.Close ();
  57.       }
  58.       dbConnection = null;
  59.       Debug.Log ("Disconnected from db.");
  60.     }
  61.     /// <summary>
  62.     /// 执行查询sqlite语句操作
  63.     /// </summary>
  64.     /// <param name="sqlQuery"></param>
  65.     /// <returns></returns>
  66.     public SqliteDataReader ExecuteQuery (string sqlQuery)
  67.     {
  68.      dbCommand = dbConnection.CreateCommand ();
  69.      dbCommand.CommandText = sqlQuery;
  70.      reader = dbCommand.ExecuteReader ();
  71.      return reader;
  72.     }
  73.     /// <summary>
  74.     /// 查询该表所有数据
  75.     /// </summary>
  76.     /// <param name="tableName">表名</param>
  77.     /// <returns></returns>
  78.     public SqliteDataReader ReadFullTable (string tableName)
  79.     {
  80.      string query = "SELECT * FROM " + tableName;
  81.      return ExecuteQuery (query);
  82.     }
  83.     /// <summary>
  84.     /// 动态添加表字段到指定表
  85.     /// </summary>
  86.     /// <param name="tableName">表名</param>
  87.     /// <param name="values">字段集合</param>
  88.     /// <returns></returns>
  89.     public SqliteDataReader InsertInto (string tableName, string[] values)
  90.     {
  91.      string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
  92.      for (int i = 1; i < values.Length; ++i)
  93.      {
  94.       query += ", " + values;
  95.      }
  96.      query += ")";
  97.      return ExecuteQuery (query);
  98.     }
  99.     /// <summary>
  100.     /// 动态更新表结构
  101.     /// </summary>
  102.     /// <param name="tableName">表名</param>
  103.     /// <param name="cols">字段集</param>
  104.     /// <param name="colsvalues">对于集合值</param>
  105.     /// <param name="selectkey">要查询的字段</param>
  106.     /// <param name="selectvalue">要查询的字段值</param>
  107.     /// <returns></returns>
  108.     public SqliteDataReader UpdateInto (string tableName, string []cols, string []colsvalues,string selectkey,string selectvalue)
  109.     {
  110.          string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0];
  111.          for (int i = 1; i < colsvalues.Length; ++i) 
  1. <span style="white-space:pre">	</span> {
  2.             query += ", " +cols+" ="+ colsvalues;
  3.          }
  4.          query += " WHERE "+selectkey+" = "+selectvalue+" ";
  5.          return ExecuteQuery (query);
  6.     }
  7.     /// <summary>
  8.     /// 动态删除指定表字段数据
  9.     /// </summary>
  10.     /// <param name="tableName">表名</param>
  11.     /// <param name="cols">字段</param>
  12.     /// <param name="colsvalues">字段值</param>
  13.     /// <returns></returns>
  14.     public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues)
  15.     {
  16.       string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0];
  17.       for (int i = 1; i < colsvalues.Length; ++i)
  18.       {
  19.          query += " or " +cols+" = "+ colsvalues;
  20.       }
  21.        Debug.Log(query);
  22.        return ExecuteQuery (query);
  23.     }
  24.     /// <summary>
  25.     /// 动态添加数据到指定表
  26.     /// </summary>
  27.     /// <param name="tableName">表名</param>
  28.     /// <param name="cols">字段</param>
  29.     /// <param name="values">值</param>
  30.     /// <returns></returns>
  31.      public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values)
  32.      {
  33.          if (cols.Length != values.Length)
  34.          {
  35.              throw new SqliteException ("columns.Length != values.Length");
  36.          }
  37.          string query = "INSERT INTO " + tableName + "(" + cols[0];
  38.          for (int i = 1; i < cols.Length; ++i)
  39.          {
  40.              query += ", " + cols;
  41.          }
  42.          query += ") VALUES (" + values[0];
  43.          for (int i = 1; i < values.Length; ++i)
  44.          {
  45.              query += ", " + values;
  46.          }
  47.          query += ")";
  48.          return ExecuteQuery (query);
  49.      }
  50.     /// <summary>
  51.     /// 动态删除表
  52.     /// </summary>
  53.     /// <param name="tableName">表名</param>
  54.     /// <returns></returns>
  55.      public SqliteDataReader DeleteContents (string tableName)
  56.      {
  57.          string query = "DELETE FROM " + tableName;
  58.          return ExecuteQuery (query);
  59.      }
  60.     /// <summary>
  61.     /// 动态创建表
  62.     /// </summary>
  63.     /// <param name="name">表名</param>
  64.     /// <param name="col">字段</param>
  65.     /// <param name="colType">类型</param>
  66.     /// <returns></returns>
  67.      public SqliteDataReader CreateTable (string name, string[] col, string[] colType)
  68.      {
  69.          if (col.Length != colType.Length)
  70.          {
  71.              throw new SqliteException ("columns.Length != colType.Length");
  72.          }
  73.          string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
  74.          for (int i = 1; i < col.Length; ++i)
  75.          {
  76.              query += ", " + col + " " + colType;
  77.          }
  78.          query += ")";
  79.          Debug.Log(query);
  80.          return ExecuteQuery (query);
  81.      }
  82.     /// <summary>
  83.     /// 根据查询条件 动态查询数据信息
  84.     /// </summary>
  85.     /// <param name="tableName">表</param>
  86.     /// <param name="items">查询数据集合</param>
  87.     /// <param name="col">字段</param>
  88.     /// <param name="operation">操作</param>
  89.     /// <param name="values">值</param>
  90.     /// <returns></returns>
  91.      public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)
  92.      {
  93.          if (col.Length != operation.Length || operation.Length != values.Length)
  94.          {
  95.              throw new SqliteException ("col.Length != operation.Length != values.Length");
  96.          }
  97.          string query = "SELECT " + items[0];
  98.          for (int i = 1; i < items.Length; ++i)
  99.          {
  100.             query += ", " + items;
  101.          }
  102.          query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
  103.          for (int i = 1; i < col.Length; ++i)
  104.          {
  105.              query += " AND " + col + operation + "'" + values[0] + "' ";
  106.          }
  107.          return ExecuteQuery (query);
  108.      }
  109. }
好了 sqlite数据操作类写好后：本来应该按照编码规范来写  因为我本身以前做c#的所有一般使用三层架构
Modle实例化对象类 ，DAl数据处理类，IDal数据接口类，Bll业务逻辑类
但是呢，目前只为测试 因此我就不一一详细介绍代码内容了，相信会c#的人都会三层架构，呵呵

ok，言归正传，下面将贴出unity3d 使用上面 sqliteDbHelper 操作类
SqliteDbTest： 
  1. using UnityEngine;
  2. using System.Collections;
  3. using System;
  4. using Mono.Data.Sqlite;
  5. using System.Data;
  1.  
  2. public class SqliteDbTest : MonoBehaviour {
  3.   <span style="white-space:pre">	</span>SqliteDbHelper db ;
  4. <span style="white-space:pre">	</span>int id=1;
  5. void Start ()
  6. {
  7.         db = new SqliteDbHelper("Data Source=./sqlite.db");
  8.         Debug.Log(db.ToString());
  9.    /*
  10.     SqliteDbAccess db = new SqliteDbAccess("data source=mydb1.db");
  11.   db.CreateTable("momo",new string[]{"name","qq","email","blog"},
  12.    new string[]{"text","text","text","text"});
  13.   db.CloseSqlConnection();
  14.    */
  15.   }    
  1. <span style="white-space:pre">	</span>public  string name = "";
  2.         public string emls = "";
  3. void OnGUI()
  4. {
  5.  
  6.   if(GUILayout.Button("create table"))
  7.   {
  8.    db.CreateTable("mytable",new string[]{"id","name","email"},new string[]{"int","varchar(20)","varchar(50)"});
  9.    Debug.Log("create table ok");
  10.   }
  11.  
  12.   if(GUILayout.Button("insert data"))
  13.   {
  14.  
  15.    db.InsertInto("mytable",
  16.                 new string[] { "" + (++id), "'随风去旅行"+id+"'","'zhangj_live"+id+"@163.com'"});//),"'aaa"+id+"'","'aaa"+id+"@sohu.com'"});
  17.  
  18.    Debug.Log("insert table ok");
  19.   }
  20.  
  21.  
  22.   if(GUILayout.Button("search database"))
  23.         {
  24.             IDataReader sqReader = db.SelectWhere("mytable", new string[]
  25.     {"name","email"},new string[]{"id"},new string[]{"="},new string[]{"2"});
  26.    while (sqReader.Read())
  27.    {
  28.      //Debug.Log(
  29.      name= "name="+sqReader.GetString(sqReader.GetOrdinal("name"));// +
  30.                  emls = "email=" + sqReader.GetString(sqReader.GetOrdinal("email"));
  31.      //);
  32.    }
  33.   }
  34.      if (name != "")
  35.      {
  36.         GUI.Label(new Rect(100, 100, 100, 100), name);
  37.         GUI.Label(new Rect(100, 200, 100, 100), emls);
  38.         //  GUILayout.Label(emls);
  39.      }
  40.   if(GUILayout.Button("close database"))
  41.   {
  42.    db.CloseSqlConnection();
  43.    Debug.Log("close table ok");
  44.   }
  45. }
  46. }
上面的代码很简单 ，相信稍微懂点unity3d的，都能看懂 因此就小偷懒一般 没写注释了，呵呵

由此可以看出 分别实现了
1.动态创建数据库及表结构
2.动态创加入数据
3,.查询指定字段
4.关闭数据连接

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/127123
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号