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, 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:

 

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

 

 

Explanation of the PlayerDataMapper class

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

 

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.

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:

 

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:

 

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:

 

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:

 

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:

 

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:

 

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

Get it from GitHub: https://gist.github.com/ScottLilly/7d28882a8e8a4c49b03f

Or DropBox: https://www.dropbox.com/sh/hyokg5jbhae72ke/AAC2JXZLPNgQrw-X3liqPYAZa?dl=0

 

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

28 thoughts on “Lesson 22.3 – Creating the SQL to save and load the saved game data

  1. 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.

    1. 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: https://github.com/ScottLilly/SQLHydra, 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. Scott,

    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.

  3. 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.

    Thanks.

    1. 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. 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.

    Thanks.

     

    1. 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. 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 stackoverflow.com, 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.

    1. Interesting.

      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. 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:

     

  7. 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 🙁

  8. 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?

    1. 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.

      Thanks!

  9. 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
    HResult=-2147467261
    Message=Object reference not set to an instance of an object.
    Source=Engine
    StackTrace:
    at Engine.Player.HasRequiredItemToEnterThisLocation(Location location) in H:\Game Dev\Code\SuperAdventure\Engine\Player.cs:line 162
    InnerException:

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

      1. LINK REMOVED FOR PRIVACY

        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!

        1. 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. Hi!

    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. 🙂

    1. 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” 🙂

  11. 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).

    ——————————
    ADDITIONAL INFORMATION:

    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: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

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

    1. 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 (http://www.thewindowsclub.com/open-windows-services) 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.

  12. 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”;

Leave a Reply

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