Unity3D: SQLite quick start ( create DB dynamically )

Trabla: Unity3D: SQLite quick start ( create DB dynamically )

Solving:

There are two main approaches to deal with SQLite in Unity3D:
1. Create DB dynamically
2. Copy db file from Assets/StreamingAssets/

This tutorial explains first approach - Create DB dynamically

1. Create new unity project

2. Create following folder structure in Assets
/Assets/
/Assets/Plugins/
/Assets/Plugins/Android
/Assets/Scripts/

3. Copy following dlls from Unity3D installations on your PC
to folder /Aseets/Plugins/ :
Copy from:
C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0

Mono.Data.Sqlite.dll
I18N.MidEast.dll
I18N.Other.dll
I18N.Rare.dll

Unity3D SQLite quick start ( create DB dynamically ) - codingtrabla tutorial 2

4. Download sqllite3.dll for windows ( is used for Windows )
and copy to /Aseets/Plugins/ :

https://www.sqlite.org/download.html

Unity3D SQLite quick start ( create DB dynamically ) - codingtrabla tutorial 3

Note: if your OS is 64 bit you need x64 sqlite.dll
http://blog.synopse.info/post/2013/03/23/Latest-version-of-sqlite3.dll-for-Windows-64-bit

Unity3D SQLite quick start ( create DB dynamically ) - codingtrabla tutorial 4

5. Download libsqlite3.so ( lib for Android support )
and copy to folder /Assets/Plugins/Android/

I used libsqlite3.so from here:

https://github.com/Busta117/SQLiteUnityKit/

Unity3D SQLite quick start ( create DB dynamically ) - codingtrabla tutorial 5

6. Add script "DBHelper.cs" into folder "/Aseets/Scripts/" with following code:

using UnityEngine;
using System.Collections;

using System.IO;
using Mono.Data.Sqlite;

namespace XOGOS.DB //change to yours
{

    public abstract class DBDescriptor{
        public string name = null;

        public abstract void CreateDatabase (SqliteConnection con);

    }

    public class DBHelper{

        public static SqliteConnection GetConnection( DBDescriptor dbDescriptor ){

            //Path to DB on device
            var localDBPath = System.IO.Path.Combine (Application.persistentDataPath, dbDescriptor.name);
            bool exists = File.Exists (localDBPath);

            if (!exists) {

                SqliteConnection.CreateFile( localDBPath );
            }

            var connection = new SqliteConnection ("Data Source=" + localDBPath );

            if (!exists) {
                dbDescriptor.CreateDatabase( connection );
            }

            return connection;

        }

    }
}

7. Add script "GameDatabaseDescriptior.cs" into folder "/Aseets/Scripts/" with following code:

using UnityEngine;
using System.Collections;

using System.IO;
using Mono.Data.Sqlite;

using XOGOS.DB;

public class GameDatabaseDescriptor : XOGOS.DB.DBDescriptor {

    public GameDatabaseDescriptor( ){
        //name of database file
        name = "my_app_db.db3";
    }

    public override void CreateDatabase( SqliteConnection connection ){

        //Create table sql
        var sql = "CREATE TABLE my_users( id INTEGER PRIMARY KEY AUTOINCREMENT, name text NOT NULL);";

        connection.Open ();

        using (var cmd = connection.CreateCommand()) {
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }

        //Inserting some dummy data
        sql = "INSERT INTO my_users(name) VALUES (@name);";

        using (var cmd = connection.CreateCommand()) {
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@name","Трололо");

            cmd.ExecuteNonQuery();
        }

        connection.Close ();

    }
}

8. Now create simple UI in UnityEditor:
1) Add UI -> "Panel"
2) Add UI -> "InputField" as child of "Panel"
3) Add UI->Button as child of "Panel" , rename to "ButtonSave" + change text to "Save into DB"
4) Add UI->Button as child of Panel, rename to "ButtonGetData" + change text to "Get Data From DB"
5) Add UI->Text as child of Panel , rename to "TextOutput" + clear text in element

Unity3D SQLite quick start ( create DB dynamically ) - codingtrabla tutorial 6

9. Create new script "PanelScript.cs"   for "Panel" with next code:

using UnityEngine;
using System.Collections;

using UnityEngine.UI;
using UnityEngine.Events;

using System;
using System.Runtime.InteropServices;

using System.Collections.Generic; // Dictionary
using Mono.Data.Sqlite;
using XOGOS.DB;

public class PanelScript : MonoBehaviour {

    public InputField input;
    public Button save;
    public Button getData;
    public Text output;

    private SqliteConnection dbCon;

    void Awake(){
        Application.targetFrameRate = 30;

    }

    public void SaveData(){

        var sql = "INSERT INTO my_users(name) VALUES (@name);";

        using (var cmd = dbCon.CreateCommand()) {
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@name",input.text);

            cmd.ExecuteNonQuery();
        }

    }

    public void GetData(){

        //Clear output Text Field
        output.text = "";

        var sql = "SELECT * FROM my_users;";

        using (var cmd = dbCon.CreateCommand()) {
            cmd.CommandText = sql;

            using(var reader = cmd.ExecuteReader()){
                while(reader.Read()){
                    output.text = output.text + reader.GetInt32(0).ToString() + "|" + reader.GetString(1)+"\n";
                }
            }
        }

    }

    // Use this for initialization
    void Start () {
        // Initializing DB
        dbCon = DBHelper.GetConnection ( new GameDatabaseDescriptor() );
        dbCon.Open ();
    }


    void OnApplicationQuit() {
        dbCon.Close();
    }

}

10. In Unity Editor select "Panel" and drag-n-drop ui elements into script reference: