Reading and writing method of Unity3d SQLite database

First, we'll copy the Mono.data.sqlite.dll and sqlite3.dll two dynamic link libraries from the Unity installation path to the plugins directory of Untiy, as shown in:

Use Navicat for SQLite to create a SQLite database file, put it in the Resources folder directory, as shown in:

Create a new dbaccess footstep and add the following steps:

Using Unityengine;

Using System;

Using System.Collections;

Using Mono.Data.Sqlite;

Using System.IO;

public class DbAccess {

Private Sqliteconnection Dbconnection;//sql Connection

Private Sqlitecommand dbcommand=null;//sql Command

Private Sqlitedatareader Dbreader;//sql Reader

Public DbAccess (String connectionString)

{

String appdbpath = "";

if (Application.platform = = runtimeplatform.windowseditor)//If it is in the Windows editor

{

Appdbpath = application.datapath+ "//" +connectionstring;

}

else if (Application.platform = = runtimeplatform.android)--if it is an Android platform

{

Appdbpath = Application.persistentdatapath + "/" + connectionString;

if (! File.exists (Appdbpath))

{

www loader = new www ("jar:file://" + Application.datapath + "/" + connectionString);//Copy the database to the Android writable path, note: SQLite cannot read data in the installation package

File.writeallbytes (appdbpath,loader.bytes);

}

}

Opendb ("Data source=" +appdbpath);

}

private void Opendb (string connectionString)

{

Try

{

DbConnection = new Sqliteconnection (connectionString);

Dbconnection.open ();

Debug.Log ("Connect to DB");

}

catch (System.Exception ex)

{

Debug.Log (ex. Message);

}

}

public void Closesqlconnection ()//Close database connection

{

if (dbcommand!=null)

Dbcommand.dispose ();

DbCommand = null;

if (dbreader!=null)

Dbreader.dispose ();

Dbreader = null;

if (dbconnection!=null)

Dbconnection.close ();

DbConnection = null;

}

Public Sqlitedatareader ExecuteQuery (string sqlquery)//Execute Query

{

DbCommand = Dbconnection.createcommand ();

Dbcommand.commandtext = sqlquery;

Dbreader = Dbcommand.executereader ();

return dbreader;

}

Public Sqlitedatareader readfulltable (string tableName)//Read entire table

{

string query = "SELECT * from" + tablename+ ";";

return ExecuteQuery (query);

}

Public Sqlitedatareader Insertinto (string tablename,string[] values)//inserting data into a table

{

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)//Replace the data in the table

{

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)//delete data in table

{

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)//insert a specific value

{

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)//delete table

{

string query = "DELETE from" + tableName;

return ExecuteQuery (query);

}

Public Sqlitedatareader createtable (string name, string[] col, string[] coltype)//CREATE TABLE

{

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)/ /integrated all operations after execution

{

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);

}

}

Use case:

There is data in the dialog table of the database:

DbAccess db = new DbAccess ("/resources/dazzleparkour.sqlite");

using (Sqlitedatareader reader = db. Selectwhere ("Dialog", new string[] {"Id,name,filename,scale"}, new string[] {"Scale"}, new string[] {"="}, new Strin G[] {"1"})//read out data with scale equal to 1

{

while (reader. Read ())//loop through the data

{

int name = reader. GetInt32 (reader. GetOrdinal ("id"));

Debug.Log (name);

}

Reader. Close ();

Db. Closesqlconnection ();

}

The last printed data is as follows

When packing, be careful to change the API resolution level in playersetting to. Net 2.0

Reading and writing method of Unity3d SQLite database