Unity中Sqlite的配置与使用_unity 中使用sqlift设置

  最近在做Unity2D游戏开发，数据方面准备使用SQLite数据库。第一次用这个数据库不知道该怎么操作，找了许多资料终于还是解决了，把方法贴在这里记录留念。

首先是：需要准备的几个dll文件
注意：Unity只支持.net 3.5及以下
将上述文件放到：工程路径/Assets/Plugins/

数据库管理工具选用的Navicat for Sqlite10.0

下面的是连接和测试的代码，源代码是在网上找到的，不过有一点Bug，经过修改后贴在这里：

using UnityEngine;
using Mono.Data.Sqlite;
using System;


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[i];
        }
        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[i] + " =" + colsvalues[i];
        }
        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[i] + " = " + colsvalues[i];
        }
        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[i];
        }
        query += ") VALUES (" + values[0];
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + values[i];
        }
        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[i] + " " + colType[i];
        }
        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[i];
        }
        query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
        for (int i = 1; i < col.Length; ++i)
        {
            query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
        }
        return ExecuteQuery(query);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226

using UnityEngine;
using System.Data;
public class SqliteDbTest : MonoBehaviour
{
     private SqliteDbHelper db;
    int id = 1;
    void Start()
    {
        //这里是默认在工程路径下
        db = new SqliteDbHelper("Data Source=test.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), "'锡丞'", "'xic_xxx@126.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");
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68