Using local database SQLite in Unity

SQLite security does not use MySQL high, because SQLite does not create users, as well as permission settings, SQLite is a standalone database, functional simplicity, miniaturization, MySQL test for server data volume of large functions need to install, such as a large number of Web site traffic management

In fact, MySQL and SQLite database operation functions are basically the same, no difference, the main difference is the permissions and library size

Note: In the use of this Sqlite data management, also need to put, several required dynamic library into plugins (Mono.Data.Sqlite, Sqlite3, System.Data) These dynamic library Baidu can search

1. First create a new database wrapper class dbaccess

Using System;
Using System.Collections;
Using Mono.Data.Sqlite;
Using Unityengine;

public class DbAccess

{

Private Sqliteconnection dbConnection;
Private Sqlitecommand DbCommand;
Private Sqlitedatareader reader;

Public DbAccess (String connectionString)
{

OPENDB (connectionString);

}

Open Database
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);
}

}

Close the database
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");

}

Execute SQL statement
Public Sqlitedatareader ExecuteQuery (string sqlquery)

{

DbCommand = Dbconnection.createcommand ();

Dbcommand.commandtext = sqlquery;

reader = Dbcommand.executereader ();

return reader;

}

Querying the entire table
Public Sqlitedatareader readfulltable (string tableName)

{

string query = "SELECT * from" + tableName;

return ExecuteQuery (query);

}

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

}

Modify a table
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);
}

Delete a table tuple
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);
}

Specific Insert tuple
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);

}

Delete entire table contents
Public Sqlitedatareader deletecontents (string tableName)

{

string query = "DELETE from" + tableName;

return ExecuteQuery (query);

}

Create a table
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);

}

Query by condition
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);

}

}

2. Create a new class below to manipulate the data cry, this script hangs on an active gameobject.

Using Unityengine;
Using System.Collections;
Using Mono.Data.Sqlite;

public class Test:monobehaviour
{

void Start ()
{
Database File storage Address
String Appdbpath = Application.datapath + "/mz.db";

DbAccess db = new DbAccess (@ "Data source=" + appdbpath);
Path = Appdbpath;

if (! System.IO.File.Exists (@ "Data source=" + Appdbpath))
{
Create a table
Db. CreateTable ("CQ", new string[] {"Name", "QQ", "email", "blog"}, new string[] {"Text", "text", "text", "text"});
}

Inserting data
Db. Insertinto ("CQ", new string[] {"' XQ '", "' 520520 '", "' [email protected] '", "' Www.blog1.com '"});
Db. Insertinto ("CQ", new string[] {"' XX '", "' 552200 '", "' [email protected] '", "' Www.blog2.com '"});
Db. Insertinto ("CQ", new string[] {"' XQ '", "' 520520 '", "' [email protected] '", "' Www.blog3.com '"});
Delete data
Db. Delete ("CQ", new string[] {"Email", "email"}, new string[] {"' [email protected] '", "' [email protected] '"});

using (Sqlitedatareader Sqreader = db. Selectwhere ("CQ", new string[] {"Name", "email"}, new string[] {"QQ"}, new string[] {"="}, new string[] {"520520"} ))
{
while (Sqreader.read ())
{
Currently Chinese cannot be displayed
Debug.Log ("CQ" + sqreader.getstring (sqreader.getordinal ("name"));

Debug.Log ("CQ" + sqreader.getstring (sqreader.getordinal ("email"));

}

Sqreader.close ();
}

Db. Closesqlconnection ();
}

}

Using local database SQLite in Unity