Unity3d, C # using sqlite as a database solution

Unity3d, C # using sqlite as a database solution

 

1. The editor establishes the database structure and generates the sqlite database file. You can use the navicat graphic interface editor to operate the file.
2. Create a data warehouse and change the suffix to. txt format (to facilitate unity3d loading). Put the file in the Assest/Resources Directory (to create a directory ).
Files stored in the Resources directory can be loaded with Resources without distinction on the Pc, ios, and android terminals. Assume that the database file name is data.txt. The statement is as follows:
TextAsset txt = Resources. Load ("data", typeof (TextAsset) as TextAsset;
3. Write the read TextAsset file to the sandbox path of the corresponding platform. The code is:
DatabaseFilePath = Application. persistentDataPath + "//" + data. db)
File. WriteAllBytes (databaseFilePath, txt. bytes );
4. Load the database files in the sandbox path for read and write operations. DbAccess encapsulates database operations. Two dll files and one so file are required. (libsq is required for the android platform.

 

 

 

Using UnityEngine;

 

using System.Collections;using Mono.Data.Sqlite;using System;using System.IO;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];}if (col.Length == 0) {query += " FROM " + tableName;} else {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);}}public class DataCenter{private string databaseFilename = "data.db";private string databaseFilePath;private DbAccess dbaccess;public DataCenter(){databaseFilePath = Application.persistentDataPath+"//"+databaseFilename;if (!File.Exists (databaseFilePath)) {TextAsset txt = Resources.Load ("data", typeof(TextAsset))as TextAsset;File.WriteAllBytes(databaseFilePath,txt.bytes);//copy data file to sandbox}dbaccess = new DbAccess (@"Data Source=" + databaseFilePath);}}