原文：http://forum.unity3d.com/threads/28500-SQLite-Class-Easier-Database-Stuff
原始文章主要是使用JavaScript语言建立本地数据库
以前在开发中一直使用IOS源生的数据库，通过传递消息的形式在与Unity3D中进行交互。本文我在详细说说如何使用C#语言来在MAC 操作系统下创建Unity本地数据库，我是C#控哇咔咔～～～
首先你需要得到Mono.Data.Sqlite.dll 文件 与System.Data.dll文件。如果你在Mac 操作系统下使用Unity那么很悲剧，找不到这两个文件，至少我没能找到。后来我在Windows下的Unity安装路径中找到了它。为了方便大家我将这两个文件上传至网盘中，如果没有这两个文件的朋友请下载。
.zip文件下载完毕后直接解压，然后将Mono.Data.Sqlite.dll 文件 与System.Data.dll文件放在Unity工程中的Assets文件夹中。如下图所示，两个文件已经放置在Project视图当中。
Ok ，我们编写C#脚本，原始文章没有Unity数据库更新与删除的方法，我在这里加上更新与删除的方法，方便大家开发时使用。因为其实Unity中更新与删除数据库也是个比较重要的功能。
注意：下面脚本不要绑定在任何游戏对象身上，大家无需把它当作脚本可以当作一个工具类来使用。
using UnityEngine;
using System;
using System.Collections;
using Mono.Data.Sqlite;
public class DbAccess
{
private SqliteConnection dbConnection;
private SqliteCommand dbCommand;
private SqliteDataReader reader;
public DbAccess (string connectionString)
{
OpenDB (connectionString);
}
public DbAccess ()
{
}
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);
}
}
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.");
}
public SqliteDataReader ExecuteQuery (string sqlQuery)
{
dbCommand = dbConnection.CreateCommand ();
dbCommand.CommandText = sqlQuery;
reader = dbCommand.ExecuteReader ();
return reader;
}
public SqliteDataReader ReadFullTable (string tableName)
{
string query = "SELECT * FROM " + tableName;
return ExecuteQuery (query);
}
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);
}
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);
}
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);
}
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);
}
public SqliteDataReader DeleteContents (string tableName)
{
string query = "DELETE FROM " + tableName;
return ExecuteQuery (query);
}
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 += ")";
return ExecuteQuery (query);
}
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);
}
}
首先是创建本地数据库，我们创建C#脚本Test.cs直接绑定在摄像机中。
using UnityEngine;
using System.Collections;
public class Test : MonoBehaviour
{
void Start ()
{
//创建数据库名称为xuanyusong.db
DbAccess db = new DbAccess("data source=xuanyusong.db");
//创建数据库表，与字段
db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"});
//关闭对象
db.CloseSqlConnection();
}
}
运行游戏后，数据库对象会自动生成在项目的根目录中。查看数据库的软件我使用的是Navicat Premium，如果没有请大家下载，然后继续。如下图所示，数据库文件xuanyusong.db已经生成在项目的根目录中，接着我使用Navicat Premium软件将这个数据库打开。数据库的表名为momo 打开表后字段包含name、 qq 、email、 blog。都是我们在代码中创建的。（点击查看大图）OK，我们继续。首先是插入数据，记得将编码修改成UTF-16 不然中文会乱码。using UnityEngine; using System.Collections; public class Test : MonoBehaviour { void Start () { //创建数据库名称为xuanyusong.db DbAccess db = new DbAccess("data source=xuanyusong.db"); //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); db.CloseSqlConnection(); } }接着是更新数据。UpdateInto是我新写的方法，接受更新多条数据。using UnityEngine; using System.Collections; public class Test : MonoBehaviour { void Start () { //创建数据库名称为xuanyusong.db DbAccess db = new DbAccess("data source=xuanyusong.db"); db.UpdateInto("momo",new string[]{"name","qq"},new string[]{"'xuanyusong'","'11111111'"}, "email", "'xuanyusong@gmail.com'" ); db.CloseSqlConnection(); } }然后是删除数据DELETE也是我封装的方法。using UnityEngine; using System.Collections; public class Test : MonoBehaviour { void Start () { //创建数据库名称为xuanyusong.db DbAccess db = new DbAccess("data source=xuanyusong.db"); //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); //我在数据库中连续插入三条数据 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); //然后在删掉两条数据 db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); db.CloseSqlConnection(); } }最后是查找数据。using UnityEngine; using System.Collections; using Mono.Data.Sqlite; public class Test : MonoBehaviour { void Start () { //创建数据库名称为xuanyusong.db DbAccess db = new DbAccess("data source=xuanyusong.db"); //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); //我在数据库中连续插入三条数据 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); //然后在删掉两条数据 db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); //注解1 SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"}); while (sqReader.Read()) { Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name")) + sqReader.GetString(sqReader.GetOrdinal("email"))); } db.CloseSqlConnection(); } }注解1：这里的结构非常像安卓的数据库指针，然后while循环把每一条数据都取出来。 sqReader.Gerordinal()方法就是拿到对应列名称的数据。如下图所示，经过一些列的添加与删除的操作最后数据库的内容如下。如下图所示，我使用Log也将数据库name 与 email的字段打印了出来。最后我在强调一点，我们在OnStart方法中db.CreateTable创建数据库表，如果重复创建系统会抛出错误。避免这个情况请保证你的数据库表只会被创建一次。祝大家学习愉快嘎嘎嘎～～～留言中看到有朋友说报错，那么MOMO将我的工程打包，提供下在地址Unity 使用SQLite本地数据库的下载地址如下：http://vdisk.weibo.com/s/abGmB如下图所示，请先在PlaySettings中修改Api Compatibility Level 改成.NET 2.0，如果不修改会报错注意：Error building Player: Extracting referenced dlls failed.无论你编译任何平台都请修改一下这里， 留言中有朋友在编译PC平台中 因为没有修改这里导致无法编译成功。。IOS平台SQLite的使用：using UnityEngine; using System.Collections; using Mono.Data.Sqlite; public class Test : MonoBehaviour { void Start () { //数据库文件储存地址 string appDBPath = Application.persistentDataPath + "/xuanyusong.db"; DbAccess db = new DbAccess(@"Data Source=" + appDBPath); //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); //我在数据库中连续插入三条数据 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); //然后在删掉两条数据 db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); //注解1 using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) { while (sqReader.Read()) { //目前中文无法显示 Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name"))); Debug.Log(sqReader.GetString(sqReader.GetOrdinal("email"))); } sqReader.Close(); } db.CloseSqlConnection(); } }using UnityEngine; using System.Collections; using Mono.Data.Sqlite; public class Test : MonoBehaviour { void Start () { //数据库文件储存地址 string appDBPath = Application.persistentDataPath + "/xuanyusong.db"; //注意！！！！！！！这行代码的改动 DbAccess db = new DbAccess("URI=file:" + appDBPath); //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); //我在数据库中连续插入三条数据 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); //然后在删掉两条数据 db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); //注解1 using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) { while (sqReader.Read()) { Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("name"))); Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("email"))); } sqReader.Close(); } db.CloseSqlConnection(); } void Update() { if (Input.GetKeyDown(KeyCode.Escape) ¦¦Input.GetKeyDown(KeyCode.Home) ) { Application.Quit(); } } }using UnityEngine; using System.Collections; using Mono.Data.Sqlite; public class Test : MonoBehaviour { string name = null; string email = null; string appDBPath = null; void Start () { //////////-------- //请注意！！！！！！！ //这里的修改 appDBPath = Application.dataPath + "/xuanyusong.db"; DbAccess db = new DbAccess(@"Data Source=" + appDBPath); //////////-------- //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); //我在数据库中连续插入三条数据 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); //然后在删掉两条数据 db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"}); while (sqReader.Read()) { Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name")) + sqReader.GetString(sqReader.GetOrdinal("email"))); //取值 name = sqReader.GetString(sqReader.GetOrdinal("name")); email = sqReader.GetString(sqReader.GetOrdinal("email")); } db.CloseSqlConnection(); } void OnGUI() { ///为了让大家看的更清楚 我将数据库取出的内容显示在屏幕中 if(name != null) { GUILayout.Label("XXXXXXXXXXXXX" + name); } if (email!= null) { GUILayout.Label("XXXXXXXXXXXXX" + email); } if(appDBPath != null) { GUILayout.Label("数据库的路径" + appDBPath); } } }using System.Collections; using Mono.Data.Sqlite; //using Mono.Data.SqliteClient; public class Test : MonoBehaviour { string name = null; string email = null; string path = null; void Start () { //数据库文件储存地址 //注意这里的修改！！！！！！！！！！！！！！ string appDBPath = Application.dataPath + "/xuanyusong.db"; //-------------------------- DbAccess db = new DbAccess(@"Data Source=" + appDBPath); path = appDBPath; //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); //我在数据库中连续插入三条数据 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); //然后在删掉两条数据 db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); //注解1 using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) { while (sqReader.Read()) { //目前中文无法显示 Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("name"))); Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("email"))); name = sqReader.GetString(sqReader.GetOrdinal("name")); email = sqReader.GetString(sqReader.GetOrdinal("email")); } sqReader.Close(); } db.CloseSqlConnection(); } void OnGUI() { if(name != null) { GUILayout.Label(name); } if(email != null) { GUILayout.Label(email); } if(path != null) { GUILayout.Label(path); } } }
