Unity3d,c# using SQLite as a database solution idea

Source: Internet
Author: User
Tags sqlite sqlite database







1, the editor to establish a good database structure, the creation of SQLite database files, you can use the Navicat graphical interface editor to operate.
2, the establishment of a good database, the suffix name into the. txt format (convenient unity3d loading), put the file into the Assest/resources directory (new directory).
Files placed in the resources directory, on the pc/ios/android side can not be differentiated by resource to load, assuming that the database file fame: Data.txt, the statement is as follows:
Textasset txt = resources.load ("Data", typeof (Textasset)) as Textasset;
3, write the read Textasset file to the corresponding platform sandbox path, the code is:
Databasefilepath = application.persistentdatapath+ "//" +DATA.DB; (sandbox, different platform paths, both readable and writable)
File.writeallbytes (databasefilepath,txt.bytes);
4. Load the database file under the sandbox path for read and write operations. The dbaccess encapsulates database operations. Which requires two DLL files, one so file (Android platform requires LIBSQ









<p><span style="font-family: Arial, Helvetica, sans-serif;">
using UnityEngine;</span></p>
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);
	}
}





Unity3d,c# using SQLite as a database solution idea


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.