2021-06-17 unit learning notes (23) use SQLite to store and read the item data of backpacks and stores
These days, I'm learning the knowledge of game data saving and reading, and then find what I wrote before Backpack store function The problem of data storage is not taken into account, so it has to be reconstructed these days. Although the function is realized on the surface, it is difficult to realize when storing and reading backpacks and store items, so I reconstructed that part of the code.
In the process of refactoring, I found that the code I wrote before can be optimized in many places. At the same time, I finally understood the difference between value type and reference type. However, this part can be described in the next learning note. This article first summarizes how to use SQLite to store and read the item data of backpacks and stores.
- First of all, start from the database part. Because the amount of data to be stored is relatively small, I use SQLite here. The following is a database manager written by myself in combination with online materials. The database manager is divided into two parts. The first part is to provide external methods to operate the database, and the other part is the common operations in the database, such as addition, deletion, query and modification, etc.
- In the first part, all methods originally need to open and close the database, but I put these two operations into the archive manager. The game starts to open the database and closes the database at the end of the game, which is more convenient.
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
using System;
using MyGameDemo.Item;
public class DBManager : MonoBehaviour
{
public static DBManager instance;
//Form name
public string itemInfo = "ItemInfo";
public string playerBag = "PlayerBag";
//Establish database connection
SqliteConnection connection;
//Database command
SqliteCommand command;
//Database reader
public SqliteDataReader reader;
private void Awake()
{
instance = this;
DontDestroyOnLoad(gameObject);
}
/// <summary>
///Load item data
/// </summary>
public List<ItemData> LoadItemDatas(string tableName)
{
List<ItemData> itemDatas = new List<ItemData>();
//OpenConnect();
if (!IsExistTable(tableName))
{
CreateTable(tableName);
}
SelectFullTableData(tableName);
while (reader.Read())
{
int i = 0;
ItemData itemData = new ItemData();
itemData.ID = int.Parse(reader.GetValue(++i).ToString());
itemData.itemName = reader.GetValue(++i).ToString();
itemData.description = reader.GetValue(++i).ToString();
itemData.iconName = reader.GetValue(++i).ToString();
itemData.itemCount = int.Parse(reader.GetValue(++i).ToString());
itemData.itemPrice = int.Parse(reader.GetValue(++i).ToString());
itemData.itemTypes = (ItemTypes)Enum.Parse
(typeof(ItemTypes), reader.GetValue(++i).ToString());
itemDatas.Add(itemData);
if (tableName != itemInfo)
{
itemData.GridPosition = int.Parse(reader.GetValue(++i).ToString());
}
}
//CloseDB();
return itemDatas;
}
public void SaveItemDatas(string tableName, List<ItemData> itemDatas)
{
//OpenConnect();
DeleteFullTableData(tableName);
foreach (var itemData in itemDatas)
{
string[] values = SelectData(itemInfo, new string[] { "ItemID", itemData.ID.ToString() });
InsterInto(tableName, values);
UpdataData(tableName, new string[]
{
"ItemCount", itemData.itemCount.ToString() ,
"GridPosition",itemData.GridPosition.ToString()
},
new string[] { "ItemID", itemData.ID.ToString() });
}
//CloseDB();
}
public bool IsExistData(string tableName,int itemID)
{
//OpenConnect();
string[] data = SelectData(tableName, new string[] { "ItemID", itemID.ToString() });
//CloseDB();
if (data.Length == 0)
return false;
else
return true;
}
#region database operation
/// <summary>
///Open database
/// </summary>
public void OpenConnect()
{
try
{
//The database is stored in Asset/StreamingAssets
string path = Application.streamingAssetsPath + "/MyGameDemo.db";
//New database connection
connection = new SqliteConnection(@"Data Source = " + path);
//Open database
connection.Open();
//Debug.Log("open database");
}
catch (Exception ex)
{
Debug.Log(ex.ToString());
}
}
/// <summary>
///Close database
/// </summary>
public void CloseDB()
{
if (command != null)
{
command.Cancel();
}
command = null;
if (reader != null)
{
reader.Close();
}
reader = null;
if (connection != null)
{
connection.Close();
}
connection = null;
//Debug.Log("close database");
}
/// <summary>
///Execute SQL command
/// </summary>
///< param name = "querystring" > SQL command string < / param >
/// <returns></returns>
private SqliteDataReader ExecuteQuery(string queryString)
{
command = connection.CreateCommand();
command.CommandText = queryString;
reader = command.ExecuteReader();
return reader;
}
/// <summary>
///Create table
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
protected SqliteDataReader CreateTable(string tableName)
{
string sql = "CREATE TABLE " + tableName + " as select * from " + itemInfo + " where 1=0;";
return ExecuteQuery(sql);
}
/// <summary>
///Judge whether the table exists
/// </summary>
///< param name = "tablename" > table name < / param >
/// <returns></returns>
private bool IsExistTable(string tableName)
{
string sql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tableName + "' ";
ExecuteQuery(sql);
string count = "0";
while (reader.Read())
{
count = reader[0].ToString();
}
if (count != "0")
return true;
else
return false;
}
/// <summary>
///Delete table
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private SqliteDataReader DeleteTable(string tableName)
{
string sql = "DROP TABLE " + tableName;
return ExecuteQuery(sql);
}
/// <summary>
///Inserts data into the specified table
/// </summary>
///< param name = "tablename" > specify the table < / param >
///< param name = "values" > inserted data < / param >
/// <returns></returns>
private SqliteDataReader InsterInto(string tableName, string[] values)
{
string sql = "INSERT INTO " + tableName + " values (";
foreach (var item in values)
{
sql += "'" + item + "',";
}
sql = sql.TrimEnd(',') + ")";
//Debug.Log("insert succeeded");
return ExecuteQuery(sql);
}
/// <summary>
///Update data
/// </summary>
/// <param name="tableName"></param>
///< param name = "values" > data to be modified < / param >
///< param name = "conditions" > modified conditions < / param >
/// <returns></returns>
private SqliteDataReader UpdataData(string tableName, string[] values, string[] conditions)
{
string sql = "update " + tableName + " set ";
for (int i = 0; i < values.Length - 1; i += 2)
{
sql += values[i] + "='" + values[i + 1] + "',";
}
sql = sql.TrimEnd(',') + " where (";
for (int i = 0; i < conditions.Length - 1; i += 2)
{
sql += conditions[i] + "='" + conditions[i + 1] + "' and ";
}
sql = sql.Substring(0, sql.Length - 4) + ");";
//Debug.Log("update succeeded");
return ExecuteQuery(sql);
}
/// <summary>
///Delete data
/// </summary>
/// <param name="tableName"></param>
///< param name = "conditions" > query criteria < / param >
/// <returns></returns>
private SqliteDataReader DeleteValues(string tableName, string[] conditions)
{
string sql = "delete from " + tableName + " where (";
for (int i = 0; i < conditions.Length - 1; i += 2)
{
sql += conditions[i] + "='" + conditions[i + 1] + "' and ";
}
sql = sql.Substring(0, sql.Length - 4) + ");";
return ExecuteQuery(sql);
}
/// <summary>
///Clear the data of the whole table
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private SqliteDataReader DeleteFullTableData(string tableName)
{
string queryString = "delete from " + tableName;
return ExecuteQuery(queryString);
}
/// <summary>
///Query the data of the whole table
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private SqliteDataReader SelectFullTableData(string tableName)
{
string queryString = "select * from " + tableName;
return ExecuteQuery(queryString);
}
/// <summary>
///Query specific data according to specific conditions
/// </summary>
///< param name = "tablename" > data table name < / param >
///< param name = "values" > data to query < / param >
///< param name = "conditions" > query conditions < / param >
/// <returns></returns>
private SqliteDataReader SelectGivenData(string tableName, string[] values, string[] conditions)
{
string sql = "select " + values[0];
for (int i = 1; i < values.Length; i++)
{
sql += " , " + values[i];
}
sql += " from " + tableName + " where( ";
for (int i = 0; i < conditions.Length - 1; i += 2)
{
sql += conditions[i] + " =' " + conditions[i + 1] + " 'and ";
}
sql = sql.Substring(0, sql.Length - 4) + ");";
return ExecuteQuery(sql);
}
/// <summary>
///Query one piece of data in the whole table according to specific conditions
/// </summary>
///< param name = "tablename" > table name < / param >
///< param name = "fields" > need to find data < / param >
/// <returns></returns>
private string[] SelectData(string tableName, string[] conditions)
{
List<string> list = new List<string>();
string sql = "SELECT * FROM " + tableName + " where( ";
for (int i = 0; i < conditions.Length - 1; i += 2)
{
sql += conditions[i] + " =' " + conditions[i + 1] + " 'and ";
}
sql = sql.Substring(0, sql.Length - 4) + ");";
reader = ExecuteQuery(sql);
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
object obj = reader.GetValue(i);
list.Add(obj.ToString());
}
}
return list.ToArray();
}
#endregion
}
- In addition, considering that when filling in the store item data, it will be easier to fill in the unit scene. In order to avoid the cumbersome operation of "writing the data in the scene and then copying it to the database", it is necessary to judge whether there is this table in the database and whether there is data in the table at the beginning of the game. If one of the two is missing, it is based on the store items filled in the scene, Create a new set of store data and write it to the database when the game is closed.
public class MapStore : MapResources
{
//Store name
public string storeName;
//The name and number of store items filled in the scene are used to initialize the store data for the first time and recover the store items regularly later
public List<StoreItemData> storeItemDatas = new List<StoreItemData>();
//Store data read from database
public List<ItemData> itemDatas = new List<ItemData>();
private UIStoreWindow uiStoreWindow;
void Start()
{
uiStoreWindow = UIManager.instance.GetUIWindow<UIStoreWindow>();
//Read data from database
itemDatas = DBManager.instance.LoadItemDatas(storeName);
//If the data is loaded for the first time, the database will create a new table according to the store name, and there will be no data in the table,
//Therefore, add store data according to the values set in the inspector and store the data at the end of the game
if(itemDatas.Count == 0)
{
foreach (var storeItem in storeItemDatas)
{
var itemData = ItemManager.instance.itemDatas.Find
(i => i.itemName == storeItem.itemName);
var storeItemData = ItemManager.instance.NewItemData
(itemData, uiStoreWindow.belongTo, storeItem.itemCount);
storeItemData.GridPosition = uiStoreWindow.inventoryGridList.Count;
itemDatas.Add(storeItemData);
}
//Initialize the grid position of the item
foreach (ItemTypes itemType in Enum.GetValues(typeof(ItemTypes)))
{
InitGridPos(itemType);
}
}
//Load data for the second time, that is, there is data in the table
else
{
foreach(var itemData in itemDatas)
{
var tempData = ItemManager.instance.itemDatas.Find
(i => i.ID == itemData.ID);
var icon = GameObjectPool.instance.CreateObj
(uiStoreWindow.belongTo + itemData.ID, tempData.iconPrefab, transform.position, transform.rotation);
GameObjectPool.instance.CollectionObject(icon);
itemData.iconPrefab = icon;
}
}
}
private void InitGridPos(ItemTypes itemType)
{
//There are several grids in the container UI to put similar items into them in turn
int gridPos = uiStoreWindow.inventoryGridList.Count-1;
foreach (var storeItem in itemDatas)
{
if (storeItem.itemTypes == itemType)
storeItem.GridPosition = gridPos--;
}
}
//After clicking the store, load the data in the clicked store into the store UI, and then display the store UI
public void ShowStoreItems()
{
uiStoreWindow.LoadStoreItems(itemDatas);
}
}
[Serializable]
public class StoreItemData
{
public string itemName;
public int itemCount;
}