Database Setup

This guide covers connecting Unity applications to both SQL and SQLite databases using Navicat as the database management tool.

Preparing SQL Database

Create your SQL database with the required tables. Modify database names, table structures, and credentials according to your project needs.

Preparing SQLite Database

SQLite databases store data in a single file. Place the .db file in your project's StreamingAssets folder for easy access during runtime.

Connecting to SQL Database

The following method establishes a connection to a MySQL database:

private void FetchPlayerData()
{
    using var dbConnection = new MySqlConnection(sqlConnectionString);
    
    try
    {
        dbConnection.Open();
        string sqlQuery = "SELECT * FROM player_table";
        
        using var command = new MySqlCommand(sqlQuery, dbConnection);
        using var dataReader = command.ExecuteReader();
        
        while (dataReader.Read())
        {
            int playerId = dataReader.GetInt32("playerID");
            string playerName = dataReader.GetString("playerName");
            string playerScore = dataReader.GetString("score");
            
            playerRecords.Add(playerName, playerScore);
            Debug.Log($"Player found - ID: {playerId}, Name: {playerName}, Score: {playerScore}");
        }
    }
    catch (MySqlException error)
    {
        Debug.LogError($"Database connection failed: {error.Message}");
    }
}

Connecting to SQLite Database

The following method handles SQLite database connectivity:

private void LoadLocalData()
{
    using var localDb = new SqliteConnection(localDbPath);
    
    try
    {
        localDb.Open();
        string query = "SELECT * FROM user_records";
        
        using var cmd = new SqliteCommand(query, localDb);
        using var reader = cmd.ExecuteReader();
        
        while (reader.Read())
        {
            int recordId = reader.GetInt32(0);
            string userName = reader.GetString(1);
            string userPassword = reader.GetString(2);
            
            Debug.Log($"Record loaded - ID: {recordId}, Username: {userName}, Password: {userPassword}");
        }
    }
    catch (SqliteException ex)
    {
        Debug.LogError($"Local database error: {ex.Message}");
    }
}

Required Declarations

public Dictionary<string, string> playerRecords = new Dictionary<string, string>();

private MySqlConnection remoteConnection;
private string sqlConnectionString;
private string localDbPath;

private void Start()
{
    sqlConnectionString = "server=localhost;user id=root;password=your_password;database=gamedata;charset=utf8;";
    localDbPath = "URI=file:" + Application.streamingAssetsPath + "/game.db";
}

Package Dependencies

Ensure your project includes the necessray NuGet packages:

• MySql.Data - for MySQL/SQL server connectivity
• Mono.Data.Sqlite - for SQLite support

Install these via the Unity Package Manager or import the DLLs directly into your Assets/Plugins folder.