Lesson 22.3 – Creating the SQL to save and load the saved game data

Lesson Objectives

At the end of this lesson, you will know…

• How to connect your program to a SQL database
• How to create SQL statements to add, read, delete, and update data in SQL database tables

Now, we are going to use the SQL database you created, to store, update, and retrieve the player’s game data.

This lesson is a long one. We need to add the code to save the game data to the database, update the database for each new save, and read the saved data. We also need to change some existing classes, that let you create the Player object from the SQL data.

How to connect your C# program to a SQL Server database

To connect to the database, we need a “connection string”.

A connection string tells our program where the database server is located, which database we want to use, and the database’s user ID and password – if you configure your database to use them (which we did not do for this database).

If you are using MS SQL Server, you will use this connection string:

Server=(local);Database=SuperAdventure;Trusted_Connection=True;

This tells SuperAdventure that the SQL engine is on the local computer (the same one we are running the program from), the database name is SuperAdventure, and we are not using a user ID and password to connect to the database. If you had your database on a different computer, you would use that computer’s network name, instead of “(local)”.

Every SQL engine has a slightly different format for its connection string. Fortunately, http://www.connectionstrings.com/ has every connection string you will probably ever need. You can go there, select the SQL engine you are using, and it will tell you the format to use.

Step 1: Open the solution in Visual Studio, and edit the Player.cs file.

Add this new function to the Player class:

public static Player CreatePlayerFromDatabase(int currentHitPoints, int maximumHitPoints, int gold, int experiencePoints, int currentLocationID)
{
   Player player = new Player(currentHitPoints, maximumHitPoints, gold, experiencePoints);
   player.MoveTo(World.LocationByID(currentLocationID));
   return player;
}

This is similar to the function we use to create the player from the XML data, except it uses the parameters we will read from the columns in the SavedGame table.

Step 2: In the Engine project, create a new class named PlayerDataMapper. This is where we will put our database functions.

Copy/paste this code into the PlayerDataMapper class (I’ll explain what it is doing below):

using System;
using System.Data;
using System.Data.SqlClient;
namespace Engine
{
    public static class PlayerDataMapper
    {
        private static readonly string _connectionString = "Data Source=(local);Initial Catalog=SuperAdventure;Integrated Security=True";
        public static Player CreateFromDatabase()
        {
            try
            {
                // This is our connection to the database
                using(SqlConnection connection = new SqlConnection(_connectionString))
                {
                    // Open the connection, so we can perform SQL commands
                    connection.Open();
                    Player player;
                    // Create a SQL command object, that uses the connection to our database
                    // The SqlCommand object is where we create our SQL statement
                    using(SqlCommand savedGameCommand = connection.CreateCommand())
                    {
                        savedGameCommand.CommandType = CommandType.Text;
                        // This SQL statement reads the first rows in teh SavedGame table.
                        // For this program, we should only ever have one row,
                        // but this will ensure we only get one record in our SQL query results.
                        savedGameCommand.CommandText = "SELECT TOP 1 * FROM SavedGame";
                        // Use ExecuteReader when you expect the query to return a row, or rows
                        SqlDataReader reader = savedGameCommand.ExecuteReader();
                        // Check if the query did not return a row/record of data
                        if(!reader.HasRows)
                        {
                            // There is no data in the SavedGame table, 
                            // so return null (no saved player data)
                            return null;
                        }
                        // Get the row/record from the data reader
                        reader.Read();
                        // Get the column values for the row/record
                        int currentHitPoints = (int)reader["CurrentHitPoints"];
                        int maximumHitPoints = (int)reader["MaximumHitPoints"];
                        int gold = (int)reader["Gold"];
                        int experiencePoints = (int)reader["ExperiencePoints"];
                        int currentLocationID = (int)reader["CurrentLocationID"];
                        // Create the Player object, with the saved game values
                        player = Player.CreatePlayerFromDatabase(currentHitPoints, maximumHitPoints, gold,
                            experiencePoints, currentLocationID);
                    }
                    // Read the rows/records from the Quest table, and add them to the player
                    using(SqlCommand questCommand = connection.CreateCommand())
                    {
                        questCommand.CommandType = CommandType.Text;
                        questCommand.CommandText = "SELECT * FROM Quest";
                        SqlDataReader reader = questCommand.ExecuteReader();
                        if(reader.HasRows)
                        {
                            while(reader.Read())
                            {
                                int questID = (int)reader["QuestID"];
                                bool isCompleted = (bool)reader["IsCompleted"];
                                // Build the PlayerQuest item, for this row
                                PlayerQuest playerQuest = new PlayerQuest(World.QuestByID(questID));
                                playerQuest.IsCompleted = isCompleted;
                                // Add the PlayerQuest to the player's property
                                player.Quests.Add(playerQuest);
                            }
                        }
                    }
                    // Read the rows/records from the Inventory table, and add them to the player
                    using(SqlCommand inventoryCommand = connection.CreateCommand())
                    {
                        inventoryCommand.CommandType = CommandType.Text;
                        inventoryCommand.CommandText = "SELECT * FROM Inventory";
                        SqlDataReader reader = inventoryCommand.ExecuteReader();
                        if(reader.HasRows)
                        {
                            while(reader.Read())
                            {
                                int inventoryItemID = (int)reader["InventoryItemID"];
                                int quantity = (int)reader["Quantity"];
                                // Add the item to the player's inventory
                                player.AddItemToInventory(World.ItemByID(inventoryItemID), quantity);
                            }
                        }
                    }
                    // Now that the player has been built from the database, return it.
                    return player;
                }
            }
            catch(Exception ex)
            {
                // Ignore errors. If there is an error, this function will return a "null" player.
            }
            return null;
        }
        public static void SaveToDatabase(Player player)
        {
            try
            {
                using(SqlConnection connection = new SqlConnection(_connectionString))
                {
                    // Open the connection, so we can perform SQL commands
                    connection.Open();
                    // Insert/Update data in SavedGame table
                    using(SqlCommand existingRowCountCommand = connection.CreateCommand())
                    {
                        existingRowCountCommand.CommandType = CommandType.Text;
                        existingRowCountCommand.CommandText = "SELECT count(*) FROM SavedGame";
                        // Use ExecuteScalar when your query will return one value
                        int existingRowCount = (int)existingRowCountCommand.ExecuteScalar();
                        if(existingRowCount == 0)
                        {
                            // There is no existing row, so do an INSERT
                            using(SqlCommand insertSavedGame = connection.CreateCommand())
                            {
                                insertSavedGame.CommandType = CommandType.Text;
                                insertSavedGame.CommandText = 
                                    "INSERT INTO SavedGame " +
                                    "(CurrentHitPoints, MaximumHitPoints, Gold, ExperiencePoints, CurrentLocationID) " +
                                    "VALUES " +
                                    "(@CurrentHitPoints, @MaximumHitPoints, @Gold, @ExperiencePoints, @CurrentLocationID)";
                                // Pass the values from the player object, to the SQL query, using parameters
                                insertSavedGame.Parameters.Add("@CurrentHitPoints", SqlDbType.Int);
                                insertSavedGame.Parameters["@CurrentHitPoints"].Value = player.CurrentHitPoints;
                                insertSavedGame.Parameters.Add("@MaximumHitPoints", SqlDbType.Int);
                                insertSavedGame.Parameters["@MaximumHitPoints"].Value = player.MaximumHitPoints;
                                insertSavedGame.Parameters.Add("@Gold", SqlDbType.Int);
                                insertSavedGame.Parameters["@Gold"].Value = player.Gold;
                                insertSavedGame.Parameters.Add("@ExperiencePoints", SqlDbType.Int);
                                insertSavedGame.Parameters["@ExperiencePoints"].Value = player.ExperiencePoints;
                                insertSavedGame.Parameters.Add("@CurrentLocationID", SqlDbType.Int);
                                insertSavedGame.Parameters["@CurrentLocationID"].Value = player.CurrentLocation.ID;
                                // Perform the SQL command.
                                // Use ExecuteNonQuery, because this query does not return any results.
                                insertSavedGame.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            // There is an existing row, so do an UPDATE
                            using(SqlCommand updateSavedGame = connection.CreateCommand())
                            {
                                updateSavedGame.CommandType = CommandType.Text;
                                updateSavedGame.CommandText =
                                    "UPDATE SavedGame " +
                                    "SET CurrentHitPoints = @CurrentHitPoints, " +
                                    "MaximumHitPoints = @MaximumHitPoints, " +
                                    "Gold = @Gold, " +
                                    "ExperiencePoints = @ExperiencePoints, "+
                                    "CurrentLocationID = @CurrentLocationID";
                                // Pass the values from the player object, to the SQL query, using parameters
                                // Using parameters helps make your program more secure.
                                // It will prevent SQL injection attacks.
                                updateSavedGame.Parameters.Add("@CurrentHitPoints", SqlDbType.Int);
                                updateSavedGame.Parameters["@CurrentHitPoints"].Value = player.CurrentHitPoints;
                                updateSavedGame.Parameters.Add("@MaximumHitPoints", SqlDbType.Int);
                                updateSavedGame.Parameters["@MaximumHitPoints"].Value = player.MaximumHitPoints;
                                updateSavedGame.Parameters.Add("@Gold", SqlDbType.Int);
                                updateSavedGame.Parameters["@Gold"].Value = player.Gold;
                                updateSavedGame.Parameters.Add("@ExperiencePoints", SqlDbType.Int);
                                updateSavedGame.Parameters["@ExperiencePoints"].Value = player.ExperiencePoints;
                                updateSavedGame.Parameters.Add("@CurrentLocationID", SqlDbType.Int);
                                updateSavedGame.Parameters["@CurrentLocationID"].Value = player.CurrentLocation.ID;
                                // Perform the SQL command.
                                // Use ExecuteNonQuery, because this query does not return any results.
                                updateSavedGame.ExecuteNonQuery();
                            }
                        }
                    }
                    // The Quest and Inventory tables might have more, or less, rows in the database
                    // than what the player has in their properties.
                    // So, when we save the player's game, we will delete all the old rows
                    // and add in all new rows.
                    // This is easier than trying to add/delete/update each individual rows
                    // Delete existing Quest rows
                    using(SqlCommand deleteQuestsCommand = connection.CreateCommand())
                    {
                        deleteQuestsCommand.CommandType = CommandType.Text;
                        deleteQuestsCommand.CommandText = "DELETE FROM Quest";
                        deleteQuestsCommand.ExecuteNonQuery();
                    }
                    // Insert Quest rows, from the player object
                    foreach(PlayerQuest playerQuest in player.Quests)
                    {
                        using(SqlCommand insertQuestCommand = connection.CreateCommand())
                        {
                            insertQuestCommand.CommandType = CommandType.Text;
                            insertQuestCommand.CommandText = "INSERT INTO Quest (QuestID, IsCompleted) VALUES (@QuestID, @IsCompleted)";
                            insertQuestCommand.Parameters.Add("@QuestID", SqlDbType.Int);
                            insertQuestCommand.Parameters["@QuestID"].Value = playerQuest.Details.ID;
                            insertQuestCommand.Parameters.Add("@IsCompleted", SqlDbType.Bit);
                            insertQuestCommand.Parameters["@IsCompleted"].Value = playerQuest.IsCompleted;
                            insertQuestCommand.ExecuteNonQuery();
                        }
                    }
                    // Delete existing Inventory rows
                    using(SqlCommand deleteInventoryCommand = connection.CreateCommand())
                    {
                        deleteInventoryCommand.CommandType = CommandType.Text;
                        deleteInventoryCommand.CommandText = "DELETE FROM Inventory";
                        deleteInventoryCommand.ExecuteNonQuery();
                    }
                    // Insert Inventory rows, from the player object
                    foreach(InventoryItem inventoryItem in player.Inventory)
                    {
                        using(SqlCommand insertInventoryCommand = connection.CreateCommand())
                        {
                            insertInventoryCommand.CommandType = CommandType.Text;
                            insertInventoryCommand.CommandText = "INSERT INTO Inventory (InventoryItemID, Quantity) VALUES (@InventoryItemID, @Quantity)";
                            insertInventoryCommand.Parameters.Add("@InventoryItemID", SqlDbType.Int);
                            insertInventoryCommand.Parameters["@InventoryItemID"].Value = inventoryItem.Details.ID;
                            insertInventoryCommand.Parameters.Add("@Quantity", SqlDbType.Int);
                            insertInventoryCommand.Parameters["@Quantity"].Value = inventoryItem.Quantity;
                            insertInventoryCommand.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                // We are going to ignore errors, for now.
            }
        }
    }
}

Explanation of the PlayerDataMapper class

In order to use the .NET SQL objects, we need these “using” statements:

using System.Data;
using System.Data.SqlClient;

These namespaces are where the SqlConnection, SqlCommand, and SqlDataReader classes exist. Those are the classes we will use to access the SQL server.

On line 9, we have the connection string.

private static readonly string _connectionString = "Data Source=(local);Initial Catalog=SuperAdventure;Integrated Security=True";

If you aren’t using SQL Server, you will need to change this to the correct one for your database. This is a static variable because we are using it in the static methods of this static class. So, any class-level variables we use in them must also be static.

We made the class a “public static class” because we are not going to create an instance of it. We are only going to call its static functions to manage our data access – similar to the World class.

CreateFromDatabase() function description

This function will be called when the player starts the game. We will see if there is saved game data in the database. If there is, we will create a Player object, and return it to SuperAdventure.cs. If nothing is in the database, we will return null, and SuperAdventure.cs will create the player from the XML file, or create a new player.

There is a “try/catch” in here (lines 13 and 109) because you might have problems with this code (the database might not be running, the connection string might not be correct, etc.) The function will “try” to execute the code in the “try” section. If there is an error, it will run the “catch” section.

If you have a problem running this, you can set a breakpoint in the “catch” and examine the error (the “ex” variable). That may help you (and me) discover the source of the problem.

Starting on line 16, we have something new, a “using” inside a function:

using(SqlConnection connection = new SqlConnection(_connectionString))
{
   … lots of code here
}

This “using” is different from the “using” lines we add at the top of a class. Notice that it has opening and closing curly braces after it, similar to an “if”.

Inside the parentheses we are instantiating a SqlConnection object, using our connection string. This is the object that connects to the SQL database. All our commands will use it.

A SqlConnection object is a special type of object. It is a disposable object.

Usually, when you create an object, .NET knows when you don’t need it any more. If you instantiate a variable inside a function, .NET knows it is not needed after the function is done. It can re-use that space in memory. However, objects such as a SqlConnection object connect to resources outside your program. .Net is not sure when it can get rid of that object and re-use the memory (or other resources the object used).

So, you need to tell .NET when you are done with disposable objects.

We could do it like this:

SqlConnection connection = new SqlConnection(_connectionString);
… lots of code here
connection.Dispose();

In that example, we instantiate the connection object without a “using”, and we call the Dispose() method on it to tell .NET we are done with it.

However, the “using”, with the curly braces, will automatically do that for us. It sets the “scope” of the connection variable (where it exists) to the inside of the curly braces. When it reaches the closing curly brace, it automatically calls the Dispose() on the connection object.

On line 19, we open the connection to the database, so we can start communicating with it.

Line 21 creates the variable we will use to hold our Player object, if we find data in the database. If the database is empty, this function will return this object, which will be “null” (no player found).

Line 25 creates our SqlCommand object, from the SqlConnection object – so it will communicate with the SQL Server the SqlConnection object is talking with.

The SqlCommand object is what we will use to create our SQL statement that queries the database. Notice that it also is disposable, and we create it with a “using”, like the SqlConnection object.

Lines 27-31, we give more details of what the SqlCommand will be. It will be a Text query (we will write our own SQL statement), and the statement is:

SELECT TOP 1 * FROM SavedGame

A “select” statement means “get some rows/records from the database”. In this case, we want to get the first row “TOP 1”. The “*” means that we want all the columns. You can write SQL queries that only return some of the columns. For that, you would include a list of the columns you want, like:

SELECT CurrentHitPoints, MaximumHitPoints FROM SavedGame

The “FROM SavedGame” says what table has the data we are looking for. You can write more complex SQL queries that access more than one table. But, describing everything you can do in SQL would take dozens more lessons. So, we’ll just cover the basics here.

On line 34, we instantiate out SqlDataReader object for our SqlCommand. This will hold the results we get back from the database, after we run the query.

We run the query when we call savedGameCommand.ExecuteReader(). This runs our query and starts streaming the result back to our SqlDataReader. The SqlDataReader does not hold the results right now. Instead, we will tell it to read the rows (although, in this example, we will only ever have one row at the most).

Lines 37-42, we check if the reader sees any rows. If there are no rows (there is no saved game data), we return null from this function.

If the reader has rows, we read the first one on line 45.

In lines 48-52, we get the values from the columns that the reader has, convert them to integers, and store them in variables.

Finally, on line 55, we create the Player object from the values we read from the database.

The next two sections (lines 59-82 and 84-103) are where we read the player’s quests and inventories from their tables.

These parts of the function have something new in them, because they might have more than one row in their query results. The “while(reader.Read())” is a loop. If the reader can read a row, this value is true, so the code inside will run. This code will create a new PlayerQuest, or Inventory object, and add it to the player variable. When the reader cannot read any more rows, “reader.Read()” will return “false”, and the loop will stop running.

On line 106, after the player object has been created, and given all its inventory and quests, it is returned from the function.

SaveToDatabase() function description

This function will be called when the player closes the game. It will save their data to the database, like it currently does to the XML file.

Much of this is similar to the CreateFromDatabase function. It uses a SqlConnection object, and SqlCommand objects that perform queries. So, I’ll only describe what it does differently here.

On line 130, the query is:

SELECT count(*) FROM SavedGame

The “count(*)” will count the number of rows/records in the SavedGame table and return that value. So, when we run this query, we won’t get a row of data. Instead, we will get a single number.

SQL has many different built-in functions similar to “count”. It can do averages, sums, minimum value, maximum value, etc. Here are the built-in functions of Microsoft SQL Server.

On line 133, we execute the SqlCommand. Notice that this uses “ExecuteScalar”, instead of “ExecuteReader”. This is because our SQL statement only returns one value (that’s what “scalar” means, with SQL). We are not going to be reading rows of data, so we do not need a reader.

On line 135, we check the count value. If it was “0”, that means there is no existing row/record in the SavedGame table. So, we will need to do an “INSERT”, to add a new row. If there is an existing row, the function goes to the “else” on line 164. That is where we will do an “UPDATE” to the values for the existing record.

Our SQL command is a little different on lines 142-145. For an INSERT command, we tell it which table we want to insert into (SavedGame), the columns we want to add values to (the list on line 143), and the values we want to add (lines 144-145).

Notice that the values on line 145 have a “@” in front of them. This is how you can signify that value is a parameter – a value we are going to pass into the SQL statement.

When you build your SQL statement, use parameters to add any variable values – especially if they are values you receive from user input. If you build your CommandText by concatenating a string, it’s possible for someone to pass in special values that cause your SQL statement to do something dangerous. This is known as a “SQL injection attack”.

Here is more information on how to prevent a SQL injection attack.

In lines 148-157, we add the parameters to the SqlCommand, using their name, datatype, and value. Then, on line 161, we run the SQL statement. Notice that this uses an “ExecuteNonQuery”. This is because we do not expect SQL to return any results to us. We only want to insert some data into the database.

In lines 170-176, we update the existing record in the SavedGame table (if it already had a record, when we checked the table on line 133).

The format of the SQL statement is a little different. We tell it the table to update (SavedGame), then tell it to set each column value to our parameter value. We populate the parameters the same way as we did for the INSERT statement, by adding parameters on lines 181-190.

For the Quests and Inventory items, we run several SQL queries. At first, we run a DELETE query, to delete everything from the table. Then, we do an insert for each quest or inventory item.

We do this because there are three possible situations for each quest and inventory item.

1. It can exist in the Player object, but not have a record in the table (it’s new, since the last time the game was saved).
2. It can have a record in the table, but not in the Player object (it existed the last time the game was saved, but doesn’t any more – for example, the player sold all of the item from their inventory).
3. It exists in the Player object, and exists in the table, but might have different values (the player may have completed the quest since the last saved game).

To cover all those situations, we would need to check each item, in the database and in the Player object. Then, we would do an INSERT, DELETE, or UPDATE, depending on the situation.

It’s simpler to delete everything, then re-populate the table with all the items/quests that the player currently has.

So, in lines 206-212, we create and run a query to delete all the rows from the Quest table. We do the same thing for the Inventory table in lines 232-238.

After we delete the old rows, we loop through the Player’s PlayerQuest and Inventory properties, and insert each object into the table.

Step 3: Modify SuperAdventure.cs, to use the new functions that save and load the game with the SQL database.

Change the part of the constructor code that loads the player data from the XML file to this:

_player = PlayerDataMapper.CreateFromDatabase();
if(_player == null)
{
   if(File.Exists(PLAYER_DATA_FILE_NAME))
   {
       _player = Player.CreatePlayerFromXmlString(File.ReadAllText(PLAYER_DATA_FILE_NAME));
   }
   else
   {
       _player = Player.CreateDefaultPlayer();
   }
}

This will try to load the player from the PlayerDataMapper class. If there is an error, or if the SavedGame table is empty, PlayerDataMapper.CreateFromDatabase() will return a null. So, we know we need to check the XML file for the saved game. If we don’t see the XML file, we will create a new player.

This adds a little extra safety. If the database doesn’t work, we will still have the XML file. If you want, after you know the database is working, you could remove the code to use the XML file for the saved game data.

You also need to change the function that saves the game when the player exits the program. Modify SuperAdventure_FormClosing to this:

private void SuperAdventure_FormClosing(object sender, FormClosingEventArgs e)
{
   File.WriteAllText(PLAYER_DATA_FILE_NAME, _player.ToXmlString());
   PlayerDataMapper.SaveToDatabase(_player);
}

Now, when the player exits the game, it will save the data to the XML file and the database.

Check that your program works

Now, run SuperAdventure, and see if it still works.

Because this is a big change, there might be a problem. The most likely thing is that the connection string might not be correct for your database. Put a breakpoint at line 16 of PlayerDataMapper, and see if you can find where the program has an error.

If you do have a problem, leave a comment below. Let me know what SQL engine you are using, if it is not Microsoft SQL Server.

If everything works, you should be able to use your SQL management program to look at the data in your tables. You might even be able to modify the values, like you would with a spreadsheet. You can even change a value in the database and give yourself a million rat tails, if that makes you happy.

Summary

This is the basics of working with SQL. When you work with larger programs, your databases will be more complex, and your SQL statements will be more complex. For example, if you had a website that sold shirts, you could create a query to tell you your most popular shirt, by number of sales, for each month of the year, for each country you sell to.

You will also want to look at things like SQL transactions. In a SQL transaction, you group SQL statements together. If one of them fails, all the changes from the other statements are reverted/removed.

If our game was a more professional game, we would probably include all the queries in SaveToDatabase() inside one transaction. We would at least create transactions for the places where we delete the records, the insert new ones from the Player object. That way, if the inserts failed, the delete would be undone.

This is also what you would use if you write a program for a bank, to transfer money between accounts. To do that, you would probably write two queries – one to remove the money from the first account, another query to add it to the receiving account. If you didn’t use a transaction, and there was an error between the two queries, you would have very unhappy customers – with money missing from their accounts.

Basics of performing a SQL statement in .NET

1. Get your connection string
2. Create a SqlConnection object, using your connection string.
3. Create a SqlCommand object, using your SqlConnection.
4. Add your SQL statement to your SqlCOmmand object.
5. Add your parameter values to your SqlCommand object, if needed.
6. Execute your SqlCommand, using the appropriate type of Execute function.
7. Read your results, if any.

The three ways to execute a SqlCommand

ExecuteReader(), when you expect to receive a row, or many rows, of data.

ExecuteScalar(), when you expect to receive a single value.

ExecuteNonQuery, when you do not expect to receive any data from your SQL statement (for example, insert, update, or delete data).

Source code for this lesson

Source code on GitHub

Source code on Dropbox

Next lesson: Lesson 23.1 – Creating a console front-end for the game

Previous lesson: Lesson 22.2 – Creating database tables from classes

All lessons: Learn C# by Building a Simple RPG Index