Press "Enter" to skip to content

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:


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, 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);
   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()
                // This is our connection to the database
                using(SqlConnection connection = new SqlConnection(_connectionString))
                    // Open the connection, so we can perform SQL commands
                    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
                            // 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
                        // 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();
                                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
                    // 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();
                                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)
                using(SqlConnection connection = new SqlConnection(_connectionString))
                    // Open the connection, so we can perform SQL commands
                    // 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.
                            // 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.
                    // 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";
                    // 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;
                    // Delete existing Inventory rows
                    using(SqlCommand deleteInventoryCommand = connection.CreateCommand())
                        deleteInventoryCommand.CommandType = CommandType.Text;
                        deleteInventoryCommand.CommandText = "DELETE FROM Inventory";
                    // 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;
            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


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:



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)
       _player = Player.CreatePlayerFromXmlString(File.ReadAllText(PLAYER_DATA_FILE_NAME));
       _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());


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.



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


  1. Brian
    Brian March 7, 2016

    I am looking to move the class PlayerDataMapper.cs to it’s own DAL.

    This way I can build a DAL project that connects to MSSQL, a second for mysql and a third for Postgre.

    I am looking for some best practices so that I can swap any of the data layers out without changing Engine or the UI.

    • Scott Lilly
      Scott Lilly March 7, 2016

      A nice simple question (haha)

      There are a few different ways you could do this.

      1. If you only care about making the program connect to different database servers, you could use an object-relational mapper library, like NHibernate. With a tool like this, you need to add some configuration to your business objects, and the library will “auto-magically” handle connecting to the different database engines.
      2. If you want to do this as a coding experiment, I’ve always liked using the “factory” design pattern to manage the repository. Microsoft has an example of that here. A while back, I was messing around with this idea, and combining the SQL commands with a fluent interface. The source code for it is here:, if you want to look at it. The idea for that project was that it would know how to build the common SQL statements (INSERT, UPDATE, DELETE) for different SQL engines.

      If you have more questions, let me know what approach you are considering. I want to do some SQL posts here, and this might be a good subject to work with.

  2. Brian
    Brian March 8, 2016


    Thank you.  I have been in management for many years and not actually been coding. So I am trying to brush off some of the rust 🙂

    I am going to try the factory class example to see how that goes.  then I can move on to my next learning project.

    • Scott Lilly
      Scott Lilly March 8, 2016

      You’re welcome. Please let me know if you have any other questions.

  3. MarkR
    MarkR April 11, 2016

    Hi, Scott.

    Having a bit of trouble connecting to the database. I’m not getting any errors, but I’m not seeing the player information being saved in the database.

    I’m using SQL Server 2014 Express and it appears to be functioning correctly; I can connect to it manually in VS through the SQL Server Object Explorer and view the tables from inside of VS. I’ve tried changing the connection string to some of the examples found here and here (not sure what LocalDB thingy is).

    In the SQL Server Object Explorer in VS, if I right-click on, say, the SavedGame table and select View Data, I can see the default values for the player (CurrentHitPoints = 10, Gold = 20 etc.), but they don’t update after I move around, do battle and then exit the game. The xml file is saving correctly, but if I delete it, all the data is reset to default values, since that’s all that is in the database.

    I’ve copy-pasted the code from GitHub for all classes to avoid any errors (usually I type it into VS manually).

    If you need any other info, let me know.


    • Scott Lilly
      Scott Lilly April 11, 2016

      Hi Mark,

      Do you know how to use the debugger in Visual Studio? If not, I made this video, showing how to debug with Visual Studio.

      I would put breakpoints in the PlayerDataMapper.SaveToDatabase() function. Set one at line 121 (the “using(SqlConnection…” line), and the second at line 260 (the comment inside the “catch” section). Then, start the game, exit it, and use F10 to step through the SaveToDatabase function, line-by-line. That should show you where the error happens – the program will jump from the line it’s trying to execute to the “catch” statement. You can hover over the “ex” variable on line 258 to get more details about the error.

      If that does not help you find the error, please let me know and we can dig deeper into the problem.

  4. MarkR
    MarkR April 16, 2016

    Well, this is embarrassing. There never was an issue with values not being written to the database. I redid all the SQL lessons from scratch and was still getting the same result. It turns out, if you don’t hit the right “refresh” button in VS, you won’t see the updated values. I kept refreshing from the SQL Server Object Explorer (right-click on the table, refresh), then I noticed a refresh button (two circular arrows) above the table window. Hit that and it updates the values. Doh…

    An issue still remains (I hope it’s not as silly as the one above). At this point, I know the save-game values are in the database. So, if I delete the xml file, why aren’t they being pulled from the database? I started debugging the SuperAdventure class and found that _player = PlayerDataMapper.CreateFromDatabase() was returning null. I then debugged CreateFromDatabase() and got an exception after line 65, SqlDataReader reader = questCommand.ExecuteReader().

    Here’s a screenshot LINK REMOVED, FOR YOUR PRIVACY of what is says.

    As I’ve mentioned, all the code in Player.cs, SuperAdventure.cs and PlayerDataMapper.cs is copied straight from GitHub. Here’s LINK REMOVED, FOR YOUR PRIVACY just in case. I’m using VS 2013 Community.



    • Scott Lilly
      Scott Lilly April 16, 2016

      Hmmm… If you got that far, you can definitely connect to, and read from, the database – since you’ve gotten past the code that reads from the SavedGame table.

      The first thing I can think of that might cause an error there is if the Quest table does not exist in your database (or has a different name, like “Quests”, or has an unusual security setting on it). If that isn’t the problem, can you start SQL Server Management Studio (SSMS) and try to run the query “SELECT * FROM Quest”. You’ll need to set your database to SuperAdventure, so SSMS knows which database to run your query against. That might give you a more useful error description. If that doesn’t help you find the source of the problem, please let me know. We’ll keep looking.

      SQL Server Management Studio query

  5. MarkR
    MarkR April 16, 2016

    Success. But first, a failure on my part. When posting the exception message, I hovered my mouse over the word “Exception” instead of “ex”, which had the actual message, which was this: There is already an open DataReader associated with this Command which must be closed first.

    A search in Google brought me to this post on, which had the solution: add MultipleActiveResultSets=true to the connection string.

    Someone on the site mentioned, that it’s not the best way of solving this issue (supposedly this is, but it’s a little over my head). Nevertheless, it did the trick. Now, if I delete the xml file, the data is pulled from the database and a new xml file is created when the game exits.

    Thanks for the help.

    • Scott Lilly
      Scott Lilly April 16, 2016


      Line 60: using(SqlCommand questCommand = connection.CreateCommand()) should be creating a new SqlCommand object. So, that shouldn’t be a problem. However, if I had a dollar for every time something in programming should have, or shouldn’t have, done something, I would be retired. 🙂

      But, I’m glad to hear you got it running.

  6. Joe Simper
    Joe Simper December 6, 2016

    I got the same error that MarkR reported back in April. I solved it by explicitly closing the data reader at the bottom of the SqlCommand using blocks:

    // 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)
                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


  7. Bryce LaMay
    Bryce LaMay December 6, 2016

    Hey Scott,

    I just wanted to let you know that I encountered the exact same problem as Mark in April. Adding MultipleActiveResultSets=true to the connection string was the only thing that could fix it. I’m using SQL Server 2016. Bummer to hear that it’s bad practice 🙁

    • Scott Lilly
      Scott Lilly December 6, 2016

      You might want to try the fix Joe just submitted today (link to the comment), where he explicitly closes the reader at the bottom of the SqlCommand “using” block.

  8. Bryce LaMay
    Bryce LaMay December 11, 2016

    What a coincidence that Joe posted about that just 30 minutes before me! Thanks guys.

    On another note…I think I noticed a bug with this.

    The Player.CreatePlayerFromDatabase function uses MoveTo, which adds the currentLocation’s quest to the player if the player doesn’t already have it…which it won’t because CreatePlayerFromDatabase is called before the SqlDataReader adds the existing quests.

    This will cause an extra quest to be created if the player’s currentLocation has a quest there.

    Is there something that I missed?

    • Scott Lilly
      Scott Lilly December 11, 2016

      I believe you’re correct.

      I’ll need to test it, but I think you could:
      1. Remove the call to MoveTo() from the Player.CreatePlayerFromDatabase function.
      2. Add a call to MoveTo() at the end of the Program.LoadGameData function (which gets created in the next lesson).
      3. Make sure SuperAdventure.cs has the call to MoveTo() at the end of the constructor (which should already be there).

      I have the final version of the code, which is a few lessons past this lesson. I’ll need to revert back to this version and test these changes. If they work, I’ll update the lesson and source code files.


  9. Justin
    Justin March 31, 2017

    I have a NullReferenceException error that keeps occurring when I try to run the code. The exception happens at line 162 in the Player class.

    System.NullReferenceException occurred
    Message=Object reference not set to an instance of an object.
    at Engine.Player.HasRequiredItemToEnterThisLocation(Location location) in H:\Game Dev\Code\SuperAdventure\Engine\Player.cs:line 162

    I don’t understand the exception nor how to fix it.

    • Scott Lilly
      Scott Lilly April 1, 2017

      Hi Justin,

      Can you upload your solution (including the files in the sub-directories) to GitHub or Dropbox, so I can look at it?

      • Justin
        Justin April 1, 2017


        I have copied all code from the the three classes in lesson 22.3 and pasted over mine and still same issue occurring.
        Thank you for your help!

        • Scott Lilly
          Scott Lilly April 2, 2017

          The code looks good, when I run it. That makes me think the problem might be in the saved game data.

          If you are using the PlayerData.xml file, can you make a backup of the file, then delete it from the SuperAdventure\bin\debug directory? The next time you run the game, it should create a new PlayerData.xml file, which (hopefully) will work.

          If you are using a SQL database to save the game data, you could delete the rows in the tables and re-start the game (to have it create a good set of data).

          You could also edit the XML file, or SQL rows. The problem might be that the CurrentLocation node (or column) does not have a valid value. If you set that value to one of the Location IDs from the World class, that might fix the problem.

          Please tell me if you try those things, and the problem is not fixed.

  10. Justin
    Justin April 3, 2017

    Thank you, deleting the old XML file did solve the problem.

    • Scott Lilly
      Scott Lilly April 3, 2017

      You’re welcome. Please let me know if you encounter any other problems.

  11. Red
    Red September 15, 2017


    1. I have multiple SQL servers installed, eg. MSLocalDB, SQLEXPRESS, so I had to change the “Data Source=(local)” part of the connection string to “Data Source=(computer name)\SQLEXPRESS”.

    2. I have encountered the same problem as Brian, and Joe’s fix worked for me too. Maybe that fix could be added to the source code in this lesson.

    3. I just noticed that, these lessons doesn’t save the current weapon, and I have to select it again after every reload, but it can be fixed easily.

    4. I want to thank you Scott, for this tutorial, I really enjoy it. 🙂

    • Scott Lilly
      Scott Lilly September 15, 2017

      Hi Red,

      I’m glad to hear you liked the tutorial.

      Thanks for mentioning the problems you noticed. I thought everything was working, but I’ll double-check. If I can add in any required changes, without needing to update too many of the previous lessons or videos, I’ll try to get that in this weekend.

      If you’re using the database, you should look at a “bug fix” lesson I added earlier this week: Lesson 99.1 – Preventing duplicate quests (which has changes to PlayerDataMapper). Someone else found a bug when the player restarts the game after exiting it while at a location with a quest. It reminds me of a programming poem I read years ago, “The Last Bug” 🙂

  12. Justin
    Justin October 24, 2017

    I am having trouble even connecting to any databases. I’ve tried everything I try I get this error: TITLE: Connect to Server

    Cannot connect to (Local Computer).


    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click:

    The KB didn’t help at all either. Any ideas?

    • Scott Lilly
      Scott Lilly October 24, 2017

      Does your connection string have: “Server=(local);Database=SuperAdventure;Trusted_Connection=True;”, or does it have “(Local Computer)”, in place of “(local)”?

      If that doesn’t fix the problem, can you open the Windows Service Manager ( and look for a service named “SQL Server (MSSQLSERVER)”? It might have a different name than MSSQLSERVER. If so, let me know what it is. Also, see if its status is “Running”. If it is not running, you can double-click on the service and try starting it.

  13. Mathias
    Mathias November 29, 2017

    Hello, Ive just spent hours getting the connection string to work, maybe it could be added in the guide.

    I tried using the same data source as sql server management studio uses but then it said there was an error due to the fact my data source would be LENOVO-PC\SQLEXPRESS
    So i had to escape the slash using \\. How ever this would make the source fail. After many tries and searches i found out this new source was correct but i had to add a @ in front and then not escape the string

    private static readonly string _connectionString = @”Data Source=LENOVO-PC\SQLEXPRESS;Initial Catalog=SuperAdventure;Integrated Security=True”;

  14. Bryce
    Bryce April 18, 2018

    Hey Scott,

    Before I say anything, I need to express my deepest appreciation and gratitude for this guide and your kindness for putting it together. It feels pretty amazing to be able to open a program that I actually coded (well, typed in the code for). Thank you so much for doing this.

    Now I have to admit that this SQL stuff has me pretty frustrated. I’ve been able to understand the what, why, and how of nearly everything up to this point, but everything here has been pure Greek to me. Not only can I not get it to work, I can’t honestly say why I want it to work in the first place.

    So I guess before I even ask for troubleshooting tips, can you provide more information on why we’re doing this at all? I admittedly only have a vague understanding of what SQL Servers do, but I don’t see why we’d want to use it for game saves. And wouldn’t this require anyone playing the game to have SQL Server installed?

    Anyways, thanks again for everything you’re doing here. I’ve always wanted to pick up some programming knowledge but just never worked up the motivation prior to this guide. You’re the best!

    • Scott Lilly
      Scott Lilly April 18, 2018

      Thank you Bryce,

      In reality, you would not use SQL for a program like this. Especially because it would require the players to have SQL Server installed on their computers. I just added these lessons because several people were interested in learning the basics of SQL, and wanted to see how they could do that within the code for this game. You can easily delete (or comment out) the classes and functions for SQL and just use the XML version, if you don’t want to work with SQL right now.

      Let me know if you want to try to find out what isn’t working with the SQL code.

  15. Caleb M Bowers
    Caleb M Bowers November 4, 2018

    If you create a database can you delete the XML FILE.

    • Scott Lilly
      Scott Lilly November 4, 2018

      You should be able to delete the XML file (and you can comment out the code that handles saving/reading/creating the player with XML.

  16. Kartik
    Kartik April 11, 2023

    Can the code be used to save the game data of a unity project into sql?
    I have a game made in unity and I have to do a project of database management systems . So is this part of code common for all games or it will run only for your code?

    • Scott Lilly
      Scott Lilly April 11, 2023

      Hi Kartik,

      The basic principles should work to let you save and load C# objects into/from a SQL database. You’ll need to change the table and column names to match your program’s classes and their properties. But, the SqlConnection, SqlCommand, SqlParameter, etc. commands should all work the same way.

Leave a Reply

Your email address will not be published. Required fields are marked *