Since the release of the Unity 2018 that provides the support for .NET Framework 4.x developers can relatively easy integrate MySQL functionality into Unity applications. In this tutorial, we are going to implement a Highscore Table in Unity using a MySQL database for data storing and MySQL connector for inserting data into and retrieving data from the database. For that, we will go through the following steps:
- Install MySQL Server Community Edition and configure a database for high scores storing
- Create a simple graphical user interface in Unity for inserting and displaying high scores
- Add and setup the MySQL Connector in the Unity application so we can communicate with the MySQL Server
- Implement logic for inserting high scores into and retrieving them from the database
Let us start with the installation of the MySQL Server and configuration of a database. Go to the MySQL download page
https://dev.mysql.com/downloads/mysql/, download and install the latest MySQL Installer (Comunity Version). On the Select Products and Features Pane add MySQL Server, MySQL Workbench, and MySQL Connector /NET to the list of To Be Installed Products/Features.
After the installation, you will be prompted to configure the MySQL Server. Select configurations settings you want to apply (I left all settings as they were), provide the root password and execute the configuration process.
Having the MySQL Server up and running, start the MySQL Workbench application.
Next, connect to the MySQL Server, for that, click on the connection button in the MySQL Connections pane and provide the root password.
In the Navigator panel, activate the Schemas tab.
Click on the Create a new schema in the connected Server button.
In the new schema window, enter the name of the schema (I named it mygame) and confirm by clicking on Apply. Subsequently, a new window Apply SQL Script to Database will appear. Click Apply again to create the schema.
Next, in the Navigator panel, click on the arrow next to the mygame schema to expand the schema. Then, right-click on the Tables node to show the context menu. In the menu, select the Create Table… option.
In the appeared window, provide a name for a new table (e.g., highscores), and create three columns. The first column with the name id and of type INT will be the Primary Key (PK) of the table. The value of the primary key must Not equal Null (NN) and must be Unique (UQ) for the entire table. Additionally, I checked the AutoIncrement (AI) option so the database engine will generate the value on each insert operation.
The second column, with the name playername and of type VARCHAR, will hold the name of the player who achieved the highscore. The value must not be null.
Finally, the third column, with the name highscore, will contain the high score itself.
To create the table, click on the Apply button and in the window Apply SQL Script to Database click Apply again.
In the next step, we are going to create a new Unity application with a simple user interface that will allow to add new high scores to the database and retrieve and show top five high scores from the database.
First of all, start the Unity and create a new project with the name of your choice.
Now, with the Unity Editor opened , add the Canvas GameObject to the Scene. For that, click on GameObject -> UI -> Canvas. This will the Canvas GameObject and the EventSystem GameObject with all needed Components.
Next, add to the Canvas two GameObjects of the type Input Field (GameObject -> UI -> Input Field). Rename one Input Field to PlayerNameInput and the other to HighscoreInput. In the RectTransform Component of both Input Fields, set the Anchors to Top Left., Width to 160 pixels, and Height to 30 pixels. Set the position of the PlayerNameInput GameObject to (10, -10, 0) and the position of the HighscoreInput GameObject to (10, -50, 0).
In the Hierarchy panel, expand the PlayerNameInput GameObject and select the child GameObject with the name Placeholder. Now, in the Inspector panel, change the content of the Text Attribute in the Text Component to Enter player name… Repeat the same procedure for the HighscoreInput GameObject and change the content of the Text Attribute to Enter high score…
Subsequently, add two GameObjects of the type Button (GameObject -> UI -> Button) to the Canvas. Rename one Button to InsertButton and the other to RetrieveButton. Set the Anchors of both Buttons to Top Left, Width to 160 pixels, and Height to 30 pixels. Set the position of the InsertButton GameObject to (10, -90, 0) and the position of the RetrieveButton GameObject to (200, -230, 0).
In the Hierarchy panel, expand the InsertButton GameObject and select the child GameObject with the name Text. Now, in the Inspector panel, change the content of the Text Attribute in the Text Component to Insert. Repeat the same procedure for the RetrieveButton GameObject and change the content of the Text Attribute to Retrieve Top Five Highscores.
Next, add to the Canvas a new GameObject of the type Panel (GameObject -> UI -> Panel). In the Inspector panel, set the Anchor of the Panel to Top Left, Width to 300 pixels, and Height to 210 pixels. Set the position of the Panel to (200, -10, 0).
Add to the Panel GameObject, ten new GameObjects of the type Text (GameObject -> UI -> Text). We will use these labels to output the top five high scores from our database. Rename the first five Text GameObjects to PlayerNameX with X in the range from 1 to 5. Rename the last five Text GameObjects to HighscoreX with X in the range from 1 to 5. Now, we have to layout the labels. Set the Anchors of all labels to Top Left, Width to 135 pixels, and Height to 30 pixels. Next, set the position of the PlayerName labels to PlayerName1 (10, -10, 0), PlayerName2 (10, -50, 0), PlayerName3 (10, -90, 0), PlayerName4 (10, -130, 0), and PlayerName5 (10, -170, 0). Notice, that the position of the labels is given relative to the anchor of the Panel GameObject. Finally, set the position of the Highscore labels equal to the positions of PlayerName label, yet set the X coordinate to 150.
To complete the scene add two empty GameObjects (GameObject -> Create Empty) to the root of the scene. Rename the first one to DBInterface and the second one to UserInterface. Later we will, attach the scripts for database communication and user interaction handling to these objects.
At the end, you should have the following scene hierarchy:
And your user interface shold look like this:
Next, we are going to implement the logic for inserting data into and retrieving data from the database. For that, we first need to add MySQL Connector library to our Unity project. Create a new folder in the Assets folder of the project and name it Plugins. Then, open Explorer and navigate to the folder with the MySQL Connector, in my case it was C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2. From this folder drag and drop the files MySql.Data.dll and Google.Protobuf.dll into the Assets\Plugins folder of the Unity project. Now, we have the needed functionality to access the database.
Now, let us code! Create a new folder in the Assets folder of the Unity project and call it Scripts. In this folder, create a new script and call it DBInterface. Now, double-click on the script icon to open it in the Visual Studio. Replace the content, of the file DBInterface.cs with the following code:
using System.Collections.Generic;
using UnityEngine;
using MySql.Data.MySqlClient;
public class DBInterface : MonoBehaviour
{
private MySqlConnectionStringBuilder stringBuilder;
public string Server;
public string Database;
public string UserID;
public string Password;
// Start is called before the first frame update
void Start()
{
stringBuilder = new MySqlConnectionStringBuilder();
stringBuilder.Server = Server;
stringBuilder.Database = Database;
stringBuilder.UserID = UserID;
stringBuilder.Password = Password;
}
public void InsertHighscore(string playerName, int highscore)
{
using (MySqlConnection connection = new MySqlConnection(stringBuilder.ConnectionString))
{
try
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO highscores (playerName, highscore) VALUES (@playerName, @highscore)";
command.Parameters.AddWithValue("@playerName", playerName);
command.Parameters.AddWithValue("@highscore", highscore);
command.ExecuteNonQuery();
connection.Close();
}
catch (System.Exception ex)
{
Debug.LogError("DBInterface: Could not insert the highscore! " + System.Environment.NewLine + ex.Message);
}
}
}
public List<System.Tuple<string,int>> RetrieveTopFiveHighscores()
{
List<System.Tuple<string, int>> topFive = new List<System.Tuple<string, int>>();
using(MySqlConnection connection = new MySqlConnection(stringBuilder.ConnectionString))
{
try
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT playername, highscore FROM highscores ORDER BY highscore DESC LIMIT 5";
var reader = command.ExecuteReader();
while (reader.Read())
{
var ordinal = reader.GetOrdinal("playername");
string playername = reader.GetString(ordinal);
ordinal = reader.GetOrdinal("highscore");
int highscore = reader.GetInt32(ordinal);
System.Tuple<string, int> entry = new System.Tuple<string, int>(playername, highscore);
topFive.Add(entry);
}
connection.Close();
}
catch(System.Exception ex)
{
Debug.LogError("DBInterface: Could not retrieve the top five highscores! " + System.Environment.NewLine + ex.Message);
}
}
return topFive;
}
}
We first declare the MySqlConnectionStringBuilder object that we will use to create a proper connection string for communication with the database. We also declare four public fields Server, Database, UserId, and Password, so we can fill them in in the Unity editor. The values of these fields will be passed to the MySqlConnectionStringBuilder object at the application start.
Next, we provide a public method with the name InsertHighscore. The method takes two arguments, namely, playername of the type string and highscore of the type int. The logic of the method opens a new connection to the database, creates and executes a command that inserts a new record into the database, and finally closes the connection.
The second method of the DBInterface class has the name RetrieveTopFiveHighscore. It takes no arguments, but returns a list of tuples. The first element of each tuple contains the name of a player and the second one contains the high score of that player. The method’s logic opens a new connection to the database, creates and executes a command that selects all records in the database, sorts them in descending order, and then returns top five elements. Each returned record is then processed and put in the list. Finally, the connection will be closed and the list returned to the caller.
Now, go back to the Unity editor, add the DBInterface script to the DBInterface object in the scene, and fill in the database settings.
Finally, we are going to implement the logic for our user interface. For that, create a new script in the folder Assets\Scripts of the Unity project and name it UserInterface. Double-click on the script icon to open in the Visual Studio. Replace, the content of the file UserInterface.cs with the following code:
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
public class UserInterface : MonoBehaviour
{
public InputField PlayerName;
public InputField Highscore;
public List<Text> PlayerNames = new List<Text>();
public List<Text> Highscores = new List<Text>();
DBInterface DBInterface;
// Start is called before the first frame update
void Start()
{
DBInterface = FindObjectOfType<DBInterface>();
}
public void InsertHighscore()
{
if(DBInterface == null)
{
Debug.LogError("UserInterface: Could not insert a highscore. DBIitefrace is not present.");
return;
}
if(PlayerName == null || Highscore == null)
{
Debug.LogError("UserInterface: Could not insert a highscore. PlayerName or Highscore is not set.");
return;
}
if(string.IsNullOrEmpty(PlayerName.text) || string.IsNullOrWhiteSpace(PlayerName.text))
{
Debug.LogError("UserInterface: Could not insert a highscore. PlayerName is empty.");
return;
}
int highscore;
if (!System.Int32.TryParse(Highscore.text, out highscore))
{
Debug.LogError("UserInterface: Could not insert a highscore. Highscore is not an integer.");
return;
}
DBInterface.InsertHighscore(PlayerName.text, highscore);
PlayerName.text = "";
Highscore.text = "";
}
public void RetrieveTopFiveHighscores()
{
if (DBInterface == null)
{
Debug.LogError("UserInterface: Could not retrieve the top five highscores. DBIitefrace is not present.");
return;
}
if (PlayerNames.Count < 5 || Highscores.Count < 5)
{
Debug.LogError("UserInterface: Could not retrieve the top five highscores. Not all PlayerName labels or Highscore labels are present.");
return;
}
clearScoreboard();
List<System.Tuple<string, int>> highscores = DBInterface.RetrieveTopFiveHighscores();
for(int i=0; i<highscores.Count; i++)
{
PlayerNames[i].text = highscores[i].Item1;
Highscores[i].text = highscores[i].Item2.ToString();
}
}
private void clearScoreboard()
{
foreach(Text playername in PlayerNames)
{
playername.text = "";
}
foreach(Text highscore in Highscores)
{
highscore.text = "";
}
}
}
In this script, we first declare two objects of the type Input Field with names PlayerName and Highscore. These objects will contain references to the Input Field Components in the scene, so we can access them and read their values. We also declare two lists that will contain references to the PlayerName labels and Highscore labels of the GUI, so we can change their values when the user will click on the RetrieveButton. Additionally, we declare an object of the type DBInterface, that will be set on application start. We will use this object to access the database functions we implemented earlier.
The UserInterface class contains two functions: InsertHighscore and RetrieveTopFiveHighscores. The InsertHighscore function checks first if the DBInterface object could be successfully retrieved, then it accesses the Input Fields objects it checks if the fields contain any information and if the high score can be converted from string to integer. Finally, it uses the DBInterface object to insert the high score into the database.
The RetrieveTopFiveHighscores function checks if the DBInterface object is presented and if all references to the labels are properly set. Next, it asks DBInterface object to retrieve records from the database, clears the labels, and fills in the labels with new information.
There is also a small private function that clears the content of labels which is self-explaining though.
Finally, switch back to the Unity editor and set all references needed by the UserInterface script. It should look like this:
Last thing we need to do is define the proper actions for the buttons’ OnClick event. In the Hierarchy panel of the Unity editor select the InserButton first. In the Inspector panel, locate the OnClick section of the Button script and set the reference to the UserInterface GameObject. Select the UserInterface.InsertHighscore function as the target.
Repeat the same steps for the RetrieveButton, but select the UserInterface.RetrieveTopFiveHighscores as the target.
That is all. You can now insert high scores into the database and retrieve them back.
You can download the source code of the project from the GitHub: