Unity中数据库建立与读写详情

两个类，DbAccess和TestDB

TestDB类测试，创建数据库，创建数据表，插入数据，更新数据，删除数据，查询数据

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

public class TestDB : MonoBehaviour {

	DbAccess db;	//数据库
	string appDBPath;	//数据库路径

	string name;
	int age;
	float exp;

	//创建数据库
	void CreateDataBase()
	{
#if UNITY_EDITOR
		appDBPath = Application.dataPath + "/Test.db";
#elif UNITY_STANDALONE_WIN
		appDBPath = Application.dataPath + "/Test.db";
#elif UNITY_ANDROID
		appDBPath = Application.persistentDataPath + "/Test.db";
		//判断路径内数据库是否存在
		if(!File.Exists(appDBPath))=
		{
			//拷贝数据库
			StartCoroutine(CopyDataBase());
		}
#elif UNITY_IPHONE
		appDBPath = Application.persistentDataPath + "/Test.db";
		//判断路径内数据库是否存在
		if(!File.Exists(appDBPath))
		{
			//拷贝数据库
			StartCoroutine(CopyDataBase());
		}
#endif
		//"URI=file:" 必须添加
		db = new DbAccess ("URI=file:" + appDBPath);
	}

	//拷贝数据库
	IEnumerator CopyDataBase()
	{
		//用www先从unity中下载数据库
		//Application.streamingAssetsPath
#if UNITY_IPHONE
		WWW loadDB = new WWW(Application.dataPath
		                     + "/Raw" + "/Test.db");
#elif UNITY_ANDROID
		WWW loadDB = new WWW("jar:file://" + 
		Application.dataPath + "!/assets" + "/Test.db");

#elif UNITY_EDITOR||UNITY_STANDALONE_WIN
		WWW loadDB = new WWW(
			Application.dataPath + "/Test.db");
#endif
		yield return loadDB;
		//拷贝数据库
		File.WriteAllBytes (appDBPath, loadDB.bytes);

	}

	void OnGUI()
	{
		if (GUILayout.Button ("创建数据库"))
			CreateDataBase ();
		if (GUILayout.Button ("创建数据表"))
			CreateTable ();
		if (GUILayout.Button ("插入数据"))
			InsertData ();
		if (GUILayout.Button ("更新数据"))
			UpdateData ();
		if (GUILayout.Button ("删除数据"))
			DeleteData ();
		if (GUILayout.Button ("查询数据"))
		    FindData ();

		GUILayout.Label ("name:" + name);
		GUILayout.Label ("age:" + age);
		GUILayout.Label ("exp:" + exp);
		GUILayout.Label ("path:" + appDBPath);
	}

	//创建表
	void CreateTable()
	{
		//打开数据库
		CreateDataBase ();
		//创建数据库表
		db.CreateTable ("role", 
		                new string[]{"id","name",
						"age","lv","exp"}, 
						new string[]{"int","text",
						"int","int","float"});
		//关闭数据库
		db.CloseSqlConnection ();
	}

	//插入数据
	void InsertData()
	{
		CreateDataBase ();
		//插入数据
		db.InsertInto ("role", new string[]
		               {"1","'张三'","18","1","1.2"});
		db.CloseSqlConnection ();

	}

	//更新数据
	void UpdateData()
	{
		CreateDataBase ();
		db.UpdateInto ("role",new string[]{"lv","exp"},
		new string[]{"10","100"},"id","1");
		db.CloseSqlConnection ();
	}

	//删除数据
	void DeleteData()
	{
		CreateDataBase ();
		db.Delete ("role",new string[]{"id","id"},
					new string[]{"1","2"});
		db.CloseSqlConnection ();
	}

	//查找数据
	void FindData()
	{
		CreateDataBase ();
		SqliteDataReader sqReader = 
			db.SelectWhere ("role",
			                new string[]{"name","age","exp"},
			new string[]{"id"},new string[]{"="},
			new string[]{"3"});	
		while(sqReader.Read())
		{
			name = sqReader.GetString
				(sqReader.GetOrdinal("name"));
			age = sqReader.GetInt32
				(sqReader.GetOrdinal("age"));
			exp = sqReader.GetFloat
				(sqReader.GetOrdinal("exp"));
		}
		Debug.Log ("name:" + name);

		db.CloseSqlConnection ();
	}

}

DbAccess，链接数据库

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);
		
	}
	
	
	/// <summary>
	/// 插入数据 param tableName=表名 values=数据内容
	/// </summary>
	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>
	/// 插入数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容
	/// </summary>
	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>
	/// 删除数据 param tableName=表名 cols=字段 colsvalues=内容
	/// </summary>
	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];
		}
		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);
		
	}
	
	/// <summary>
	/// 创建表 param name=表名 col=字段名 colType=字段类型
	/// </summary>
	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);
		
	}
	

	/// <summary>
	/// 插入数据 param tableName=表名 items=结果字段 col=查找字段 operation=运算符 values=内容
	/// </summary>
	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);
			
		
	}
	
}