Tag: Database

How to work with an SQLite Database in a Unity 2018 application

In this tutorial, I am going to explain how to add SQLite capabilities to a Unity application. I will use the latest stable version of the Unity game engine, namely, 2018.3.6f1.

To enable SQLite capabilities in a Unity application, you will need a native SQLite library that implements SQLite database engine as well as a managed .NET library that works as a wrapper for the native library and can be referenced in a Unity application. Moreover, you will need different libraries for a different build (e.g., x86 or x64). Luckily, we can get the entire libraries from the https://system.data.sqlite.org.

To get the libraries, follow the steps below:

  1. Go to the download page
  2. Download the precompiled binaries (NOT the mixed-mode libraries) for 64-bit Windows (.NET Framework 4.6) or just click here
  3. Download the precompiled binaries (NOT the mixed-mode libraries) for 32-bit Windows (.NET Framework 4.6) or just click here
  4. Extract both packages in separate directories

Next, we are going to add the libraries to a Unity application:

  1. Create a new Unity application.
  2. Create a folder named Plugins in the Assets directory.
  3. In the Plugins directory create a folder x86 for 32-bit libraries, a folder x64 for 64-bit libraries, and a folder managed for managed libraries.
  4. Next, drag-and-drop the System.Data.SQLite.dll file from one of the extracted packages (does not matter if 32-bit or 64-bit) into the Assets/Plugins/managed directory.
  5. NOTE: It can happen that Unity automatically adds the SQLite.Interop.dll file into the Assets directory. In that case, delete the SQLite.Interop.dll file from the Assets directory.
  6. Next, drag-and-drop the SQLite.Interop.dll file from the extracted 64-bit package to the Assets/Plugins/x64 directory and the file with the same name from the extracted 32-bit package to the
    Assets/Plugins/x32 directory.
  7. Now you should have the following file structure in the project:

Next, we need to configure the imported libraries:

  1. Select the SQLite.Interop.dll in the Assets/Plugins/x64 directory
  2. In the Inspector panel uncheck Any Platform; select Editor and Standalone; and deselect other available platforms.
  3. In Platform settings, select x86_x64 and uncheck x86

As a result, you should have the settings as in the image below

Repeat the same procedure for the SQLite.Interop.dll file in the Assets/Plugins/x86 directory. The only exception is that in Platform settings you must select x86 and deselect x86_x64. The image below shows the configuration for the 32-bit library.

Finally, configure the managed library System.Data.SQLite.dll. In the Inspector panel, you have to deselect the Any Platform option and include the Editor and Standalone platforms.

Next, we are going to write some code to demonstrate the SQLite capability.

  1. Add an empty GameObject to the scene.
  2. Create a new script in the Assets directory, call it DBConnector, and attach it to the GameObject you have just created.
  3. Now, double-click on the script to open it in the Visual Studio.
  4. Replace the content of the file DBconnector.cs with the following code
using UnityEngine;
using System.Data.SQLite;

public class DBConnector : MonoBehaviour
{
    void Start()
    {
        SQLiteConnection connection = 
                         new SQLiteConnection(@"Data Source=F:\mydb.db;Version=3;");
        connection.Open();

        SQLiteCommand command = connection.CreateCommand();
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = "CREATE TABLE IF NOT EXISTS 'highscores' ( " +
                          "  'id' INTEGER PRIMARY KEY, " +
                          "  'name' TEXT NOT NULL, " +
                          "  'score' INTEGER NOT NULL" +
                          ");";

        command.ExecuteNonQuery();
        connection.Close();
    }
}

The code above opens a new SQLite connection to a database on the partition F with the name mydb.db (if the file does not exist, then it will be created) and subsequently creates a new table with the name highscores and fields id, name, and score.

If you start the application in the Unity editor, then everything will work just fine, and you will find the newly created database on the partition F (of course, if you have that partition). However, if you build the application as the Standalone application (either 32-bit or 64-bit version), then it won’t work.

The reason is that Unity will put the native library into the YourAppName/YourAppName_Data/Plugins directory. The wrapper (managed library), however, will look for the native library in the YourAppName/YourAppName_Data/Mono directory, which actually does not exist. So, to get your application working correctly, create the
YourAppName/YourAppame_Data/Mono directory and copy the SQLite.Interop.dll file from the YourAppName/YourAppName_Data/Plugins directory into it.

That is all! You can download the source code here:

https://github.com/asigitov/Unity2018_SQLite

%d