Select Page

Summary of How To Use SQLite on Unity3D Free Version

By Frozenfire, from the original post Using SQLite Database in Unity3D 4.3.3f. However this guide is for Windows users.

1. Download a Precompiled DLL of SQLite http://www.sqlite.org/download.html
2. Move the sqlite3.dll and sqlite3.def to Assets/Plugins in your unity project
3. Download SQLite Browser http://sourceforge.net/projects/sqlitebrowser/
4. Create a database in your Assets/ folder in your unity project with SQLite broswer
5. Copy System.Data.dll and Mono.Data.Sqlite.dll from C:Program Files (x86)UnityEditorDataMonolibmono2.0 and paste them in your Assets/ folder in your unity project
6. Access the database as above, only where “/GameMaster” is put “/YourDbName”. Note if you did this correctly MonoDevelop should have all of the methods when you type reader.
7. When building you must copy your database file to the folder that automatically is created called yourProject_Data wherever you saved the executable.

Using SQLite Database In Unity3D 4.3.3f

Using SQLite Database In Unity3D 4.3.3f

What I want is to create an inventory system for my game. I read through all the options from the world wide web and there’s many options that I can choose. Some of them are using XML, JSON and then SQLite database which then attract my attention and interest.

I’ve experience working with MySQL when I was developing plugin and theme for WordPress. I know how JSON works, and never like it since the very first encounter with it. XML? Never work with it and I was thinking if one day I want to move on into “online gaming” the SQLite will be easily dumped into and SQL that can be use by MySQL. So the decision was made.

It wasn’t an easy road to take. I’ve found many example that doesn’t work and spilled out errors which make I almost give up. Then I found something work, but it’s in Javascript which will be against my principle of using C# only. Beside, using 2 language will give a lot of errors later that is hard for my level of experience to debug.

But finally, I check out the official Mono website. Unity scripting was backed by Mono actually. It was this page. SQLite – Mono. It’s not an exact implementation though but was easier that I thought.

Here’s the code.

[code language=”csharp”]

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

public class DBAccess : MonoBehaviour {

// Use this for initialization
void Start () {

string connectionString = "URI=file:" +Application.dataPath + "/GameMaster"; //Path to database.
IDbConnection dbcon;
dbcon = (IDbConnection) new SqliteConnection(connectionString);

dbcon.Open(); //Open connection to the database.

IDbCommand dbcmd = dbcon.CreateCommand();

string sql = "SELECT firstname, lastname " + "FROM addressbook";

dbcmd.CommandText = sql;

IDataReader reader = dbcmd.ExecuteReader();

while(reader.Read()) {
string FirstName = reader.GetString (0);
string LastName = reader.GetString (1);
Console.WriteLine("Name: " +
FirstName + " " + LastName);
Debug.Log (FirstName + LastName);
}

// clean up
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;

}

// Update is called once per frame
void Update () {

}

void OnGUI () {

string connectionString = "URI=file:" +Application.dataPath + "/GameMaster"; //Path to database.
IDbConnection dbcon;
dbcon = (IDbConnection) new SqliteConnection(connectionString);

dbcon.Open(); //Open connection to the database.

IDbCommand dbcmd = dbcon.CreateCommand();

string sql = "SELECT firstname FROM addressbook WHERE rowid=1";

dbcmd.CommandText = sql;

IDataReader reader = dbcmd.ExecuteReader();

while(reader.Read()) {
string FirstName = reader.GetString (0);
//string LastName = reader.GetString (1);
//Console.WriteLine("Name: " +
//FirstName + " " + LastName);
//Debug.Log (FirstName + LastName);

GUI.Box (new Rect (Screen.width – 270, Screen.height – 55, 260, 30), "Copyright "+FirstName+" 2014");
}

// clean up
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;

}
}

[/code]

In

[code language=”csharp”]

void start () {

}

[/code]

The code suppose to read the value in the database and print the value using the debug log in Unity.

The Database Table Data

The Database Table Data

The debug log print the value correctly.

The debug log print the value correctly.

Then the code;

[code language=”csharp”]

void OnGui () {

}

[/code]

is where I want to test the implementation. I manage to print out the value and show it on screen, but it happen to be a very bad practice. Some nice guys at Unity community forum point out to me that I should read and get all the data in the variable at the start() and then just get the string value whenever I need them in the code.

It’s different when working with PHP, when developing the WordPress plugin and theme. That environment, I execute SQL queries whenever I need them, anywhere, anytime I want. And the execution is as simple as 1 line of code. (Because I like using overloading method, tried that in C# but I can’t get it to work yet.)

Print my name taken from the database.

Print my name taken from the database.

But now, I have to think out a framework, the flow chart of how I’m going to actually use this database system. It will take some time but after this, I have a framework that I can use for my future game. smile

Update 30th August, 2014 : Download the codes from GitHub, link below.

https://github.com/PangeranWiguan/W.G.-Unity3D-SQLite

Update: This summary how to setup the SQLite in Unity3D free edition.