Saving Data in Unity3D Using SQLite

1
using UnityEngine;
2
3
/// <summary>
4
/// This script shows the basic structure of all other scripts.
5
/// </summary>
6
public class HitCountExample : MonoBehaviour
7
{
8
    // Keep count of the clicks.
9
    [SerializeField] private int hitCount; // 1
10
11
    private void Start() // 2
12
    {
13
        // Read the persisted data and set the initial hit count.
14
        hitCount = 0; // 3
15
    }
16
17
    private void OnMouseDown() // 4
18
    {
19
        // Increment the hit count on each click and save the data.
20
        hitCount++; // 5
21
    }
22
}

1
Unity/Hub/Editor/2021.2.11f1/Editor/Data/MonoBleedingEdge/lib/mono/unity

1
using Mono.Data.Sqlite; // 1
2
using System.Data; // 1
3
using UnityEngine;
4
5
public class SqliteExampleSimple : MonoBehaviour
6
{
7
    // Resources:
8
    // https://www.mono-project.com/docs/database-access/providers/sqlite/
9
10
    [SerializeField] private int hitCount = 0;
11
12
    void Start() // 13
13
    {
14
        // Read all values from the table.
15
        IDbConnection dbConnection = CreateAndOpenDatabase(); // 14
16
        IDbCommand dbCommandReadValues = dbConnection.CreateCommand(); // 15
17
        dbCommandReadValues.CommandText = "SELECT * FROM HitCountTableSimple"; // 16
18
        IDataReader dataReader = dbCommandReadValues.ExecuteReader(); // 17
19
20
        while (dataReader.Read()) // 18
21
        {
22
            // The `id` has index 0, our `hits` have the index 1.
23
            hitCount = dataReader.GetInt32(1); // 19
24
        }
25
26
        // Remember to always close the connection at the end.
27
        dbConnection.Close(); // 20
28
    }
29
30
    private void OnMouseDown()
31
    {
32
        hitCount++;
33
34
        // Insert hits into the table.
35
        IDbConnection dbConnection = CreateAndOpenDatabase(); // 2
36
        IDbCommand dbCommandInsertValue = dbConnection.CreateCommand(); // 9
37
        dbCommandInsertValue.CommandText = "INSERT OR REPLACE INTO HitCountTableSimple (id, hits) VALUES (0, " + hitCount + ")"; // 10
38
        dbCommandInsertValue.ExecuteNonQuery(); // 11
39
40
        // Remember to always close the connection at the end.
41
        dbConnection.Close(); // 12
42
    }
43
44
    private IDbConnection CreateAndOpenDatabase() // 3
45
    {
46
        // Open a connection to the database.
47
        string dbUri = "URI=file:MyDatabase.sqlite"; // 4
48
        IDbConnection dbConnection = new SqliteConnection(dbUri); // 5
49
        dbConnection.Open(); // 6
50
51
        // Create a table for the hit count in the database if it does not exist yet.
52
        IDbCommand dbCommandCreateTable = dbConnection.CreateCommand(); // 6
53
        dbCommandCreateTable.CommandText = "CREATE TABLE IF NOT EXISTS HitCountTableSimple (id INTEGER PRIMARY KEY, hits INTEGER )"; // 7
54
        dbCommandCreateTable.ExecuteReader(); // 8
55
56
        return dbConnection;
57
    }
58
}

1
"CREATE TABLE IF NOT EXISTS HitCountTableSimple (id INTEGER PRIMARY KEY, hits INTEGER )"

1
"INSERT OR REPLACE INTO HitCountTableSimple (id, hits) VALUES (0, " + hitCount + ")"

1
"SELECT * FROM HitCountTableSimple"

1
[SerializeField] private int hitCountUnmodified = 0;
2
[SerializeField] private int hitCountShift = 0;
3
[SerializeField] private int hitCountControl = 0;

1
private KeyCode modifier = default;

1
private void Update()
2
{
3
    // Check if a key was pressed.
4
    if (Input.GetKey(KeyCode.LeftShift)) // 1
5
    {
6
        // Set the LeftShift key.
7
        modifier = KeyCode.LeftShift; // 2
8
    }
9
    else if (Input.GetKey(KeyCode.LeftControl)) // 1
10
    {
11
        // Set the LeftControl key.
12
        modifier = KeyCode.LeftControl; // 2
13
    }
14
    else // 3
15
    {
16
        // In any other case reset to default and consider it unmodified.
17
        modifier = default; // 4
18
    }
19
}

1
dbCommandCreateTable.CommandText = "CREATE TABLE IF NOT EXISTS HitCountTableExtended (id INTEGER PRIMARY KEY, hits INTEGER)";

1
private void OnMouseDown()
2
{
3
    var hitCount = 0;
4
    switch (modifier) // 1
5
    {
6
        case KeyCode.LeftShift:
7
            // Increment the hit count and set it to PlayerPrefs.
8
            hitCount = ++hitCountShift; // 2
9
            break;
10
        case KeyCode.LeftControl:
11
            // Increment the hit count and set it to PlayerPrefs.
12
            hitCount = ++hitCountControl; // 2
13
            break;
14
        default:
15
            // Increment the hit count and set it to PlayerPrefs.
16
            hitCount = ++hitCountUnmodified; // 2
17
            break;
18
    }
19
20
    // Insert a value into the table.
21
    IDbConnection dbConnection = CreateAndOpenDatabase();
22
    IDbCommand dbCommandInsertValue = dbConnection.CreateCommand();
23
    dbCommandInsertValue.CommandText = "INSERT OR REPLACE INTO HitCountTableExtended (id, hits) VALUES (" + (int)modifier + ", " + hitCount + ")";
24
    dbCommandInsertValue.ExecuteNonQuery();
25
26
    // Remember to always close the connection at the end.
27
    dbConnection.Close();
28
}

1
"INSERT OR REPLACE INTO HitCountTableExtended (id, hits) VALUES (" + (int)modifier + ", " + hitCount + ")"

1
void Start()
2
{
3
    // Read all values from the table.
4
    IDbConnection dbConnection = CreateAndOpenDatabase(); // 1
5
    IDbCommand dbCommandReadValues = dbConnection.CreateCommand(); // 2
6
    dbCommandReadValues.CommandText = "SELECT * FROM HitCountTableExtended"; // 3
7
    IDataReader dataReader = dbCommandReadValues.ExecuteReader(); // 4
8
9
    while (dataReader.Read()) // 5
10
    {
11
        // The `id` has index 0, our `value` has the index 1.
12
        var id = dataReader.GetInt32(0);  // 6
13
        var hits = dataReader.GetInt32(1); // 7
14
        if (id == (int)KeyCode.LeftShift) // 8
15
        {
16
            hitCountShift = hits; // 9
17
        }
18
        else if (id == (int)KeyCode.LeftControl) // 8
19
        {
20
            hitCountControl = hits; // 9
21
        }
22
        else
23
        {
24
            hitCountUnmodified = hits; // 9
25
        }
26
    }
27
28
    // Remember to always close the connection at the end.
29
    dbConnection.Close();
30
}