Using SQLite in Unity allows for full database access without having to send out external information or commands, as the database in stored with the game or app. This is not meant for secure information as anyone can open the database.
I haven’t tried any of this in BOLT yet but I will add to this post after I have tried it.
Steps to get setup
- Create a new folder under Assets called “Plugins” if there isn’t already one there.
- Download SQlite
- Copy “sqlite3.def” and “sqlite3.dll” into that Plugins folder you just created.
- Download an SQLite database manger program
- User the database manager program to create a new database in your projects “Assets” folder. (Make note of the full name of this database file)
- Copy “System.Data.dll” and “Mono.Data.Sqlite.dll” from your Unity install path + ” \Unity \Editor\Data\Mono\lib\mono\2.0 ” and paste them into the Plugins folder that you created.
If you are using Bolt in the project some of these files are already included and will give you an error in teh console until you remove the duplicate. Just delete the files mentioned as being duplicated in the console and everything will still work.
When used with Bolt.
You are now ready to create the C# code to connect to the database. Here is an example.
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
using System.Data;
using System;
public class databasetest : MonoBehaviour
{
// Start is called before the first frame update
void Start()
{
string conn = "URI=file:" + Application.dataPath + "/leaderboard.db"; //Path to database.
Debug.Log(conn);
IDbConnection dbconn;
dbconn = (IDbConnection)new SqliteConnection(conn);
dbconn.Open(); //Open connection to the database.
IDbCommand dbcmd = dbconn.CreateCommand();
string sqlQuery = "SELECT score_id, player_name, player_score " + "FROM Leaderboard_Table";
dbcmd.CommandText = sqlQuery;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
int score_idx = reader.GetInt32(0);
string player_namex = reader.GetString(1);
int player_scorex = reader.GetInt32(2);
Debug.Log("scoreID= " + score_idx + " name =" + player_namex + " score =" + player_scorex);
}
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbconn.Close();
dbconn = null;
}
// Update is called once per frame
void Update()
{
}
}- Replace “leaderboard.db” with the name of your database
- Edit “SELECT score_id, player_name, player_score ” + “FROM Leaderboard_Table” to reflect actual columns and tables from your database.
- Put this script on an active gameObject in your screen and the Console area should show you the information from your database.
If you are getting complaints about the Table not being found. Take a look at the name of your database and make sure it matches your code. My code said “Leaderboard.s3db” but my actual database was called “leaderboards.db”
SQLite creates a new empty database if it fails to find the one you specified in code, and that database will be completely empty, leading to the lack of finding a table.
Inserting data into the database
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
using System.Data;
using System;
public class database_senddata : MonoBehaviour
{
// Start is called before the first frame update
void Start()
{
string conn = "URI=file:" + Application.dataPath + "/leaderboard.db"; //Path to database.
//Debug.Log(conn);
IDbConnection dbconn;
dbconn = (IDbConnection)new SqliteConnection(conn);
dbconn.Open(); //Open connection to the database.
IDbCommand dbcmd = dbconn.CreateCommand();
// Getting Info From Database
// string sqlQuery = "SELECT score_id, player_name, player_score " + "FROM Leaderboard_Table";
// Sending Info to Database
string sqlQuery = "INSERT INTO Leaderboard_Table (player_name, player_score) VALUES ('newentry','1000')";
dbcmd.CommandText = sqlQuery;
IDataReader reader = dbcmd.ExecuteReader();
// Getting information from the database **************************************
//
// while (reader.Read())
// {
// int score_idx = reader.GetInt32(0);
// string player_namex = reader.GetString(1);
// int player_scorex = reader.GetInt32(2);
//
// Debug.Log("scoreID= " + score_idx + " name =" + player_namex + " score =" + player_scorex);
// }
//
// ****************************************************************************
// Sending information to the database Just requires the sqlQuery be changed to an INSERT
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbconn.Close();
dbconn = null;
}
// Update is called once per frame
void Update()
{
}
}
This code is the same as above except the reading of data has been commented out and an INSERT command had been put in it’s place. This allows us to add information to the database.
Here are the data types for SQLite
