Converting from SQLite Database to JSON

Good day,

I need help with a project I am working on, its a multiplayer virtual environment simulating a home (similar to The Sims game ) however it is currently saving data intoan SQLlite database . Is it possible for me to convert the SQL lite database into a json string?

below is a snippet of my code

public class DBInstanceInspector : ScriptableObject {

public bool DebugMode = false;

public string SQL_DB_LOCATION = "";
public string SQL_DB_NAME = "sensor_readings";

private IDbConnection dbConnection = null;
private IDbCommand dbCommand = null;
private IDataReader dbReader = null;

public void InitialiseSQLiteConnection()
{
    /*
     *  Important to note:
     *  - during builds, this is located in the project root (where assets, obj and other folders located)
     *  - during runtime (Windows at least), this is located in the SAME directory as the executable
     *  
     *  Currently, this will always make it point to Assets/Databases/ directory.
     */

    SQL_DB_LOCATION = "URI=file:" + Application.dataPath + "/Databases/" + SQL_DB_NAME + ".db";
    if (DebugMode)
        Debug.Log(SQL_DB_LOCATION);

    Debug.Log("Opening SQLite Connection at " + SQL_DB_LOCATION);
    dbConnection = new SqliteConnection(SQL_DB_LOCATION);
    dbCommand = dbConnection.CreateCommand();

    dbConnection.Open();
    OptimiseDBForPerformance();
    dbConnection.Close();
}

private void OptimiseDBForPerformance()
{
    // WAL = write ahead logging, basically a speed increase (see https://sqlite.org/wal.html)
    dbCommand.CommandText = "PRAGMA journal_mode = WAL;";
    dbCommand.ExecuteNonQuery();

    // Speed increase in commits (https://sqlite.org/pragma.html#pragma_synchronous)
    dbCommand.CommandText = "PRAGMA synchronous = OFF";
    dbCommand.ExecuteNonQuery();
}

public void CloseSQLiteConnection()
{
    if (dbReader != null && !dbReader.IsClosed)
        dbReader.Close();
    dbReader = null;

    if (dbCommand != null)
        dbCommand.Dispose();
    dbCommand = null;

    if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
        dbConnection.Close();
    dbConnection = null;
}

public bool DoesTableExistInDB(string tableName)
{
    Debug.Log("Checking if table exists: " + tableName);
    bool tableExists = true;

    dbConnection.Open();
    dbCommand.CommandText = "SELECT name FROM sqlite_master WHERE name='" + tableName + "'";
    dbReader = dbCommand.ExecuteReader();
    if (!dbReader.Read())
        tableExists = false;
    dbReader.Close();
    dbConnection.Close();

    return tableExists;
}

public void RemoveTableInDB(string tableName)
{
    if (dbCommand != null)
    {
        Debug.Log("Dropping old SQLite table if exists: " + tableName);
        dbCommand.CommandText = "DROP TABLE IF EXISTS " + tableName;
        dbCommand.ExecuteNonQuery();
    }
}

public int GetNumberOfRowsInTable(string tableName)
{
    Debug.Log("Finding total number of rows in SQLite table if exists: " + tableName);
    int numberOfRows = 0;

    dbConnection.Open();
    dbCommand.CommandText = "SELECT Count(*) FROM " + tableName;
    dbReader = dbCommand.ExecuteReader();
    while (dbReader.Read())
        numberOfRows = dbReader.GetInt32(0);
    dbReader.Close();
    dbConnection.Close();

    return numberOfRows;
}

Ok, as i said in the comment i’ll assume for now that you just want to store the data from your sensor class(es) in a json file.

We can see that your “BaseSensorReading” is marked as serializable. Though since your actual sensor value is not a field it would not be serialized. So you would need a seperate class that represents a data record (like a row inside a database). As long as the class and all data that should be stored is serializable by Unity you could use the JsonUtility to save several datasets you have gathered into a file. First of all Unity’s JsonUtility does only support an object as base element in a json file. Json itself allows an array as well but if we want to use the JsonUtility we need a wrapper class.

[System.Serializable]
public class SensorData
{
    public string SensorType;
    public string AreaName;
    public string SensorName
    public int Year, Month, Day;
    public int Hour, Minutes, Seconds;
    public float SensorValue;
    public string SensorBookmarkName;
    public string PlayerID;
    public int SessionID;
}

[System.Serializable]
public class Data
{
    public List<SensorData> data = new List<SensorData>();
}

Now you can just create an instance of the Data class and add new instances of the SensorData class whenever you generate a data record. So somewhere you would create a variable like this

Data myJsonData = new Data();

To add a new data record just do

SensorData record = new SensorData();
record.SensorType = yourType;
// [...] fill all the other fields

myJsonData.data.Add(record);

that way you can store your gathered records in that list. Once you’re done you can save that information into a json file by simply doing:

System.IO.File.WriteAllText("C:\\YourPath\\To\\Your\\File", JsonUtility.ToJson(myJsonData));

Note by default ToJson will produce “compact” json. So without spaces or line breaks. If you want a human readable format you can pass “true” as optional parameter as you can read here

System.IO.File.WriteAllText("C:\\YourPath\\To\\Your\\File", JsonUtility.ToJson(myJsonData, true));

This will create a json file from the SensorData records you have put into the data list.

HI @Bunny83, sorry about that I am still a beginner in programming so some of the concepts I am still learning. Basically, have 1 table of data with about 8 fields that are being collected from a class I have called Base sensor readings. Basically, in the virtual environment or game, I am building I have sensors that are being triggered during gameplay and I want the data from these sensors (which is that base sensor class) stored in a JSON file. in this case, the order of columns doesn’t matter but as long as the fields are passed into the JSON file and stored. Below are the fields and the snippet of the class i want to get data from.

Blockquote

          *0    SensorType (text)
         * 1    AreaName (text)
         * 2    SensorName (text)
         * 3-5  Date - Year, Month, Day (integers)
         * 6-8  Time - Hour, Minutes, Seconds (integers)
         * 9    SensorValue (dependent on sensor reading type)
         * 10   SensorBookmarkName (text)
         * 11   PlayerID (text)
         * 12   SessionID (integer)

Blockquote


[System.Serializable]
public class BaseSensorReading {

	public Sensor.SensorType sensorType;
	public string areaName;
	public string sensorName;
	public DateTimeValues sensorDateTime;

	public virtual void SetSensorDateTime(int year, int month, int day, int hours, int minutes, int seconds)
	{
		if (sensorDateTime == null)
			sensorDateTime = new DateTimeValues(year, month, day, hours, minutes, seconds);
		else
		{
			sensorDateTime.SetDate(year, month, day);
			sensorDateTime.SetTime(hours, minutes, seconds);
		}
	}

	public virtual string GetSensorReadingString()
	{
		return sensorDateTime.GetDateString() + ";"
			+ sensorDateTime.GetTimeString() + ";"
			+ sensorType.ToString() + ";" + areaName + ";" + sensorName;
	}

	public virtual float GetSensorValue()
	{
		return 0f;
	}

	//public BaseSensorReading data = new BaseSensorReading();
	

}

I can’t seem to understand how I can be able to get that data from that class of sensor readings into a JSON string.