Simple example how to interact with sqlite database in Unity and C#

Here is a script called SQLiteManager:

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

public class SQLiteManager : MonoBehaviour
{
    private string dbPath;

    void Start()
    {
        // Set the database path (use Application.persistentDataPath for mobile)
        dbPath = "URI=file:" + Application.dataPath + "/Database/mydatabase.db";
        CreateTable();
        InsertData("John Doe", 25);
        UpdateData(1, "Jane Doe", 30);
        SelectData();
    }

    void CreateTable()
    {
        using (var connection = new SqliteConnection(dbPath))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INTEGER);";
                command.ExecuteNonQuery();
            }
        }
    }

    void InsertData(string name, int age)
    {
        using (var connection = new SqliteConnection(dbPath))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "INSERT INTO Users (Name, Age) VALUES (@name, @age);";
                command.Parameters.Add(new SqliteParameter("@name", name));
                command.Parameters.Add(new SqliteParameter("@age", age));
                command.ExecuteNonQuery();
            }
        }
    }

    void UpdateData(int id, string name, int age)
    {
        using (var connection = new SqliteConnection(dbPath))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "UPDATE Users SET Name = @name, Age = @age WHERE Id = @id;";
                command.Parameters.Add(new SqliteParameter("@id", id));
                command.Parameters.Add(new SqliteParameter("@name", name));
                command.Parameters.Add(new SqliteParameter("@age", age));
                command.ExecuteNonQuery();
            }
        }
    }

    void SelectData()
    {
        using (var connection = new SqliteConnection(dbPath))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM Users;";
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Debug.Log("ID: " + reader["Id"] + ", Name: " + reader["Name"] + ", Age: " + reader["Age"]);
                    }
                }
            }
        }
    }
}

Explanation:

• Database Path: Uses Application.dataPath (for PC) or Application.persistentDataPath (for mobile).
• Creating Table: Ensures the Users table exists.
• Inserting Data: Adds a new user.
• Updating Data: Updates a user’s details.
• Selecting Data: Retrieves and logs all users.

Make sure to import Mono.Data.Sqlite and add the SQLite package to your Unity project (sqlite3.dll for Windows, libsqlite3.so for Android if needed).