Backpack system based on SqLite storage + full process of picking up objects in the scene

Zero, functional analysis

For the backpack system, we first need to implement several key functions:

1. Persistent storage of item data

2. Clear display of items

3. For convenient management of items, you can add and delete items

4. How to pick up items in the scene

1. Persistent storage based on SqLite

The backpack system is necessary to achieve persistent storage of item data. You don’t want players to find that their items have disappeared when they open the game again.

In UNITY, there are several ways to store data persistently:

1.PlayerPrefs: A simple key-value storage system that comes with Unity.

2.ScriptableObject: The most flexible data management tool in Unity.

3.JSON: lightweight data exchange format.

4.XML: An extensible markup language.

5. Database: A method used when storing large amounts of data.

PlayerPrefs is not suitable for storing large amounts of data, so it is not used.

Although ScriptableObject can be stored persistently, it can only be stored in edit mode. After packaging, its value will be restored to the initial value every time it is started, so it is not used.

Finally decided to use database to store data and learn new content.

1. Download SqLite viewing software

For convenience, I chose Navicat Premium, which can be tried for free for 14 days. It is enough for temporary use. We only need software to view data at the beginning.

New db file

After downloading the software, use the software to create a new db file and select Sqlite3

Transfer db files to unity

I placed the file in Assets\StreamingAssets\SqLite\

2. Import DLL files

Before starting to write code we need to import the following three dll files into UNITY

Mono.Data.Sqlite.dll

In the Unity Editor installation directory “Editor\Data\MonoBleedingEdge\lib\mono\unityjit-win32\Mono.Data.Sqlite.dll“

System.Data.dll

In the Unity Editor installation directory “Editor\Data\MonoBleedingEdge\lib\mono\2.0System.Data.dll“

Sqlite3.dll

Download the corresponding version from the official website of Sqlite “https://www.sqlite.org/download.html”

Note that if Mono.Data.Sqlite.dll does not match the UNITY version, an error will be reportedLoading assembly failed “Assets/Plugins/Mono.Data.Sqlite.dll, this file is in the mono folder There are many, please change to the appropriate version.

3. Write SqLite code

Implement Sqlite updates, insertions, deletions, and searches

Define key variables

 /// <summary>
    /// Database Connectivity
    /// </summary>
    private SqliteConnection SqlConnection;
    /// <summary>
    /// Database commands
    /// </summary>
    private SqliteCommand SqlCommand;
    /// <summary>
    ///Database reading
    /// </summary>
    private SqliteDataReader SqlDataReader;
    /// <summary>
    /// Database path
    /// </summary>
    private string SqlitePath = "URI=file:" + Application.streamingAssetsPath + "/SqLite/GameDataSQLite.db";

    private Hashtable dataHashTable = new Hashtable();

It should be noted that the database path needs to add the suffix name of the file. The author wasted some time here.

Initialization code

 /// <summary>
    /// Establish database connection
    /// </summary>
    public void SqlDataLink()
    {
        try
        {
            SqlConnection = new SqliteConnection(SqlitePath);
            SqlConnection.Open();
            SqlCommand = SqlConnection.CreateCommand();
        }
        catch (System.Exception e)
        {
#if UNITY_EDITOR
            Debug.Log(e.ToString());
#endif
        }
    }

Create a table

A table named “ItemSys” is created here, which contains two fields: id and number.

The item setting information is not put in. I store the item setting information in ScriptableObject. When using it, you only need to read it based on the ID (read the CSV file and store it)

 public void SqlCreatTable()
    {
        //A table named "ItemSys" is created, which contains two fields: id, number.
        //Create backpack table
        SqlCommand.CommandText = "CREATE TABLE IF NOT EXISTS ItemSys (id TEXT, number INTEGER)";
        SqlCommand.ExecuteNonQuery();
    }

Insert data into the table

 public void SqlInsertItemData(string id, int number)
    {
        SqlCommand.CommandText = "INSERT INTO ItemSys (id, number) VALUES (@id, @number)";
        SqlCommand.Parameters.AddWithValue("@id", id);
        SqlCommand.Parameters.AddWithValue("@number", number);
    }

Close the database

 public void SqlClose()
    {
        if (SqlCommand != null)
        {
            SqlCommand.Dispose();
            SqlCommand = null;
        }

        if (SqlDataReader != null)
        {
            SqlDataReader.Close();
            SqlDataReader = null;
        }

        if (SqlConnection != null)
        {
            SqlConnection.Close();
            SqlConnection = null;
        }
    }

Test code

Create a new SqLiteManager file and mount both files to empty objects

using System.Text;
using UnityEngine;
using static Unity.VisualScripting.Dependencies.Sqlite.SQLite3;

public class SqLiteManager : MonoBehaviour
{
    private void Start()
    {
        SqLite sqlite = gameObject.GetComponent<SqLite>();
        sqlite.SqlDataLink();
        sqlite.SqlCreatTable();
        sqlite.SqlInsertItemData("wp0001",12);
        sqlite.SqlClose();
        
    }
}

To run the code, we open Navicat Premium, select New Connection again, select existing database file as type, and leave the username and password blank.

Be careful not to directly open files that have not been moved before. What we are changing is the file that was moved to UNITY.

After opening it, we will find that a new table has been successfully created in the database, and the specified data has also been successfully written.

Delete data in the table

Strike while the iron is hot and finish the rest

/// <summary>
///
/// </summary>
/// <param name="tableName">Table name</param>
/// <param name="_KEY">Key value 1</param>
/// <param name="key">Key value 2</param>
    public void SqlDelete(string tableName, string _KEY, string key)
    {
        // DELETE FROM table_name WHERE some_column = some_value;
        StringBuilder stringBuilder =new StringBuilder();
        stringBuilder.Append("delete from ");
        stringBuilder.Append(tableName);
        stringBuilder.Append(" where ");
        stringBuilder.Append(_KEY);
        stringBuilder.Append(" = '");
        stringBuilder.Append(key);
        stringBuilder.Append("'");
        SqlCommand.CommandText= stringBuilder.ToString();
        SqlCommand.ExecuteNonQuery();
    }

Find data in table

The basic syntax of SQLite’s SELECT statement is as follows:

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2… are the fields of the table, and their values are what you want to get. If you want to get all available fields, you can use the following syntax:

SELECT * FROM table_name;

 /// <summary>
 /// Find
 /// </summary>
 /// <param name="key"></param>
 /// <returns></returns>
 public bool SQL_Select(string key, string tableName)
 {
     StringBuilder stringBuilder = new StringBuilder();
     stringBuilder.Append("select ");
     stringBuilder.Append(key);
     stringBuilder.Append(" from ");
     stringBuilder.Append(tableName);
     try
     {
         SqlCommand.CommandText = stringBuilder.ToString();
         SqlDataReader = SqlCommand.ExecuteReader();
         if (SqlDataReader != null)
         {
             while (SqlDataReader.Read())
             {
                 var id = SqlDataReader.GetString(0);
                 var number = SqlDataReader.GetInt32(1);

                 Debug.LogFormat("id: {0}, number: {1}", id, number);
             }
             return true;
         }
     }
     catch (System.Exception e)
     {
         Debug.LogError(e.ToString());
     }
     return false;
 }

The result is as shown in the figure

Update data in the table

The basic syntax of an UPDATE query is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

 /// <summary>
    /// Update data in the table
    /// </summary>
    /// <param name="tableName">Table name</param>
    /// <param name="_VALUE_STRING">Target column name</param>
    /// <param name="value">New value</param>
    /// <param name="_KEY">Target row name</param>
    /// <param name="key">The value of the target row</param>
    public void SqlUpdata(string tableName, string _VALUE_STRING, int value, string _KEY, string key)
    {
        // UPDATE table_name SET column1 = value1, column2 = value2,... WHERE some_column = some_value;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.Append("update ");
        stringBuilder.Append(tableName);
        stringBuilder.Append(" set ");
        stringBuilder.Append(_VALUE_STRING);
        stringBuilder.Append("=");
        stringBuilder.Append(value);
        stringBuilder.Append(" where ");
        stringBuilder.Append(_KEY);
        stringBuilder.Append("= '");
        stringBuilder.Append(key);
        stringBuilder.Append("'; ");
        SqlCommand.CommandText = stringBuilder.ToString();
#if UNITY_EDITOR
        SqlDataReader = SqlCommand.ExecuteReader();
        Debug.Log(SqlDataReader);
#endif
    }

Example usage of this code:

Now create a table as shown in the figure, and change the number value of the row where wp0034 is located to 5.

Run the following code:

public class SqLiteManager : MonoBehaviour
{
    private void Start()
    {
        SqLite sqlite = gameObject.GetComponent<SqLite>();
        sqlite.SqlDataLink();
        sqlite.SqlUpdata("ItemSys", "number", 5, "id", "wp0034");
        sqlite.SqlClose();
    }
}

Open the software again to see that the data has been successfully changed. The results are as follows:

Summary of this paragraph

At this point, the basic code of SqLite has been written, but it is too cumbersome to use and cannot meet our actual needs.

Next, we will re-encapsulate these codes and officially start the code of the backpack system.

II. Basic reading and writing code of backpack system

In actual use, we do not directly read and write the database, but move the database data to the HashTable when the game is loaded to speed up data search.

Define key variables

Create a new script file in the Scripts folder to inherit SqLite and define some variables

public class ItemSystem : SqLite
{
     private Hashtable dataHashTable = new Hashtable();
}

Read database data into HashTable

Note: Close DataReader after each data reading, otherwise an error will be reported

 private void LoadItemData()
    {
        SqlDataLink();
        if (SQL_Select("*", "ItemSys") == false)
        {
            //Return if there is no data
            //Create a table and place it in the location of the new archive. If you don’t want to make multiple archives, you can place the created table here.
            return;
        }
        SqlDataReader.Close();
        SqlCommand.CommandText = "select * from ItemSys";
        SqlDataReader = SqlCommand.ExecuteReader();
        while (SqlDataReader.Read())
        {
            string key = SqlDataReader.GetString(0);
            dataHashTable[key] = SqlDataReader.GetInt32(1);
        }
        SqlDataReader.Close();
        SqlClose();
        //Check whether the data is imported successfully
        Debug.Log(dataHashTable["wp0001"]);
    }

After running, you can see that the data was successfully imported and the data is displayed normally.

Add or remove items from your backpack

 /// <summary>
    /// Add items to backpack
    /// </summary>
    /// <param name="id">Item id</param>
    /// <param name="num">Amount added</param>
    public void ItemSys_Add(string id,int num)
    {
        //The judgment condition to be added is whether it is less than the maximum stacking quantity of this item.
        dataHashTable[id] = (int)dataHashTable[id] + num;
    }


    /// <summary>
    ///Delete items from backpack
    /// </summary>
    /// <param name="id">Item id</param>
    /// <param name="num">Number of deletions</param>
    /// <returns>Whether it is successful or not if the quantity is insufficient</returns>
    public bool ItemSys_Del(string id, int num)
    {
        int temp = (int)dataHashTable[id] - num;
        if (temp > 0)
        {
            dataHashTable[id] = temp;
            return true;
        }
        else if (temp == 0)
        {
            //Remove the item
            dataHashTable.Remove(id);
            return true;
        }
        return false;
    }

Synchronize data to database

Previous additions and deletions were based on hash tables, and we also need to synchronize the changes to the database.

Since I have a small amount of data, I use full backup. If the amount of data is large, I can use differential backup. I will write about this later.

 /// <summary>
    /// Synchronize data to database
    /// </summary>
    void Synchronizeddata()
    {
        SqlDataLink();
        foreach (string key in dataHashTable.Keys)//Traverse key
        {
            SqlInsertItemData(key, (int)dataHashTable[key]);
        }
        SqlClose();
    }

Show item information

Before displaying information, we first make a GUI. Using the ui component that comes with unity, we can make a simple adaptive scrolling item table.

Right-click and select UI, create a new scroll view and name it “ItemSysUI”

Make an IyemSlot prefab of the item display box (here I found one from the uintry store) and place it under the Content of ItemSysUI

Add Vertical Layout Group component to Content

The test display effect is as follows:

After the UI is ready, start writing code:

Since I only store the item ID and quantity in the database, I also need to call the corresponding information based on the ID when generating the interface. Some of the code here may be difficult to read, but it is actually getting data from an array, so it is much easier to understand.

 /// <summary>
    /// Display the backpack system GUI
    /// </summary>
    /// <param name="dis_status">Whether to display, true or false</param>
    public void ItemSys_GUI(bool dis_status)
    {
        //Pay attention to whether the GUI is loaded during initialization. It is only enabled here. Remember to write
        GameObject gui =GameObject.Find("ItemSysUI");
        GameObject ItemContent = GameObject.Find("ItemContent");
        GameObject itemSlot = Resources.Load<GameObject>("UI/Prefabs/ItemSlot");
        ItemManager itemData = Resources.Load<ItemManager>("DataAssets/Item");
        CreateItemHashTable();
        gui.SetActive(dis_status);
        if (!dis_status)
            return;
        foreach (string key in dataHashTable.Keys)//Traverse key
        {
            //Generate a display box for each item
            GameObject slot = Instantiate(itemSlot, transform.position, Quaternion.identity);
            slot.transform.SetParent(ItemContent.transform);

            slot.transform.Find("Text/Name").GetComponent<Text>().text = itemData.dataArray[(int)array_id[key]].itemName;
            slot.transform.Find("Text/Description").GetComponent<Text>().text = itemData.dataArray[(int)array_id[key]].itemDescription;
            slot.transform.Find("Icon/Stack").GetComponent<Text>().text = dataHashTable[key].ToString();
        }
    }

The test can display the data in the database and the data obtained based on the ID normally, because at least I did not add too much information during the test. I can also specify pictures, call methods, etc. later.

4, additional functions

One-click organization (automated arrangement)

Item classification

Pick up items and add them to your backpack

wheel selection tool

to be continued…

Reference article:

Several methods for Unity to store game data_unity stores data

Use of Unity data storage Sqlite

Unity reports error Loading assembly failed “Assets/Plugins/Mono.Data.Sqlite.dll“

SQLite tutorial

Does SQL SqlDataReader need to be closed and released manually?