Hi everybody I just learned some things in the past week about SQLite in Unity and I thought I share them as I had to figure out most of it myself. All code in C#.

Set up a database in Unity

Its my first time i am working with db in unity. I am basically from gaming background so mostly we use PlayerPref But sometime we really required a Database to store the values like High score, levels etc. So playerpref. will not help it out when we deal with the app data maintain through the server.

Its very simple procedure to hand on with Databse. In my blog i have provided you all the files required to set up the games.

Setup the Sqlite Database

1- Create a folder "Plugins" in the asset folder of your game, then paste two files i have provided in the zip folder and file name are "sqlite3.dll" and "sqlite3.def". Now put 3 more files which you will find in the same zip folder or for better option i suggest go to the path and take these files files from there.

here is the path  "C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0"

and the files name are

1 - Mono.Data.Sqlite

2 - Mono.Data.SqliteClient

3 - System.Data

4 - System.Data.Sqlite

When you find these files in the path provided paste them in the plugins folder.

Now you have all the files which will help you to build a db. now you need a db browser. Download sqlite browser or you can have it from the zip. Run the sqlite.exe and then window will open

Now create database and saved it.

Now come back to the unity create a script and attached it to the camera. here is the code just paste as it is.

using UnityEngine;
using System.Collections;
using Mono.Data.Sqlite;// store in the plugins folder
using System.Data.Sql; // store in the plugins folder
using System.Data; // store in the plugins folder
using System; // store in the plugins folder

public class DBsc : MonoBehaviour 
{
    void Start ()
    {
        string conn = "URI=file:" + Application.dataPath + "/Datab.db";       //returns the complete path to database file exist.

        Debug.Log("Path:"+conn);              //By this line you can see the complete path of the database

        //insert into Database

        IDbConnection dbconn;              //here we initialize a variable dbconn of type IDbConnection

        dbconn = (IDbConnection) new SqliteConnection(conn);          //Here we assign dbconn to hold the refrence     of      SqliteConnection and then type cast it with IDbConnection.


        dbconn.Open();                         //Open connection to the database once.

        IDbCommand dbcmd = dbconn.CreateCommand();                 //Here we are creating a variable of type databse command

        string insertQuery = "INSERT INTO Table1(id,name) VALUES ( '1' , 'su' )";     //Here we are inserting values in the table.Here field name need to be same as in database you have mentioned.

        dbcmd.CommandText = insertQuery ;

        IDataReader reader = dbcmd.ExecuteReader();         //This is used to execute the command which is of string type.
        //read from Database

        IDbCommand dbcmd1 = dbconn.CreateCommand();
        string sqlQuery = "SELECT * FROM Table1";
        dbcmd1.CommandText = sqlQuery;
        IDataReader reader1 = dbcmd1.ExecuteReader();
        
        while (reader1.Read())               //this loop will run untill read doesn't reach to the end of the rows of the table
        {
            int id= reader1.GetInt32(0);       //this will read data of the firstcolumn
            string name = reader1.GetString(1);   //this will read data of the second column

            Debug.Log( "  name ="+name+"  id="+  id);
        }

        //Close all the variale you have created above and put the values to be null
        reader.Close();
        reader = null;
        reader1.Close();
        reader1 = null;
        dbcmd.Dispose();
        dbcmd = null;
        dbcmd1.Dispose();
        dbcmd1 = null;
        dbconn.Close();
        dbconn = null;
    }
}

Implimentation

Now in the code first we get the path of database created then we are building connection with it, then we are inserting values inside the table exists in the database.

So these are the simple steps through which you can access the db values .

Now just run the game and then you will see the output in the console ,

I have attached the screenshot just to match up with it. Hope it works at your end.

If you got anyerror about sqlite3.dll nullexception the you just have to change the existing sqlite.dll file in the project. download it through google and it will work.