A student reported an unusual bug in the game, and this lesson will cover how to fix it.
Bug Description
This bug happens if you are storing the saved game data in a SQL database – using the PlayerDataMapper.
If the player ends the game when they are at a location that has a quest, there will be an error the next time the player tries to start the game.
Bug Analysis
PlayerDataMapper.CreateFromDatabase reads the data from the SavedGame table first. This includes the value for the player’s current location.
On line 55, we were creating the Player object by calling Player.CreatePlayerFromDatabase, and passing in the CurrentLocation ID. After creating the Player object, we called the MoveTo() function, to set the player in their last location.
The MoveTo function noticed there was a quest at the location and gave the quest to the player.
Later, in PlayerDataMapper.CreateFromDatabase, we read the player’s quests from the Quest table, and add those quests to their quest list.
The bug happened because we were adding the same quest to the player’s quest list – once from moving to their last location, and once from the database list of quests they already had.
The duplicate quest caused the game to crash.
Bug Fix
To fix this, I commented out line 158 of Player.cs – the line moving the player to the location.
Next, in PlayerDataMapper.cs, I moved the declaration of the currentLocationID variable from line 52 to line 22. This is so it would be visible outside the “using” section from line 25 through 59 (in the original code).
Finally, on line 134, I set the player object’s CurrentLocation property. This is after we added the player’s quests from the database.
FIXED CODE
Player.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Xml;
namespace Engine
{
public class Player : LivingCreature
{
private int _gold;
private int _experiencePoints;
private Location _currentLocation;
public event EventHandler<MessageEventArgs> OnMessage;
public int Gold
{
get { return _gold; }
set
{
_gold = value;
OnPropertyChanged("Gold");
}
}
public int ExperiencePoints
{
get { return _experiencePoints; }
private set
{
_experiencePoints = value;
OnPropertyChanged("ExperiencePoints");
OnPropertyChanged("Level");
}
}
public int Level
{
get { return ((ExperiencePoints / 100) + 1); }
}
public Location CurrentLocation
{
get { return _currentLocation; }
set
{
_currentLocation = value;
OnPropertyChanged("CurrentLocation");
}
}
public Weapon CurrentWeapon { get; set; }
public BindingList<InventoryItem> Inventory { get; set; }
public List<Weapon> Weapons
{
get { return Inventory.Where(x => x.Details is Weapon).Select(x => x.Details as Weapon).ToList(); }
}
public List<HealingPotion> Potions
{
get { return Inventory.Where(x => x.Details is HealingPotion).Select(x => x.Details as HealingPotion).ToList(); }
}
public BindingList<PlayerQuest> Quests { get; set; }
public List<int> LocationsVisited { get; set; }
private Monster CurrentMonster { get; set; }
private Player(int currentHitPoints, int maximumHitPoints, int gold, int experiencePoints) : base(currentHitPoints, maximumHitPoints)
{
Gold = gold;
ExperiencePoints = experiencePoints;
Inventory = new BindingList<InventoryItem>();
Quests = new BindingList<PlayerQuest>();
LocationsVisited = new List<int>();
}
public static Player CreateDefaultPlayer()
{
Player player = new Player(10, 10, 20, 0);
player.Inventory.Add(new InventoryItem(World.ItemByID(World.ITEM_ID_RUSTY_SWORD), 1));
player.CurrentLocation = World.LocationByID(World.LOCATION_ID_HOME);
return player;
}
public static Player CreatePlayerFromXmlString(string xmlPlayerData)
{
try
{
XmlDocument playerData = new XmlDocument();
playerData.LoadXml(xmlPlayerData);
int currentHitPoints = Convert.ToInt32(playerData.SelectSingleNode("/Player/Stats/CurrentHitPoints").InnerText);
int maximumHitPoints = Convert.ToInt32(playerData.SelectSingleNode("/Player/Stats/MaximumHitPoints").InnerText);
int gold = Convert.ToInt32(playerData.SelectSingleNode("/Player/Stats/Gold").InnerText);
int experiencePoints = Convert.ToInt32(playerData.SelectSingleNode("/Player/Stats/ExperiencePoints").InnerText);
Player player = new Player(currentHitPoints, maximumHitPoints, gold, experiencePoints);
int currentLocationID = Convert.ToInt32(playerData.SelectSingleNode("/Player/Stats/CurrentLocation").InnerText);
player.CurrentLocation = World.LocationByID(currentLocationID);
if (playerData.SelectSingleNode("/Player/Stats/CurrentWeapon") != null)
{
int currentWeaponID = Convert.ToInt32(playerData.SelectSingleNode("/Player/Stats/CurrentWeapon").InnerText);
player.CurrentWeapon = (Weapon)World.ItemByID(currentWeaponID);
}
foreach (XmlNode node in playerData.SelectNodes("/Player/LocationsVisited/LocationVisited"))
{
int id = Convert.ToInt32(node.Attributes["ID"].Value);
player.LocationsVisited.Add(id);
}
foreach (XmlNode node in playerData.SelectNodes("/Player/InventoryItems/InventoryItem"))
{
int id = Convert.ToInt32(node.Attributes["ID"].Value);
int quantity = Convert.ToInt32(node.Attributes["Quantity"].Value);
for (int i = 0; i < quantity; i++)
{
player.AddItemToInventory(World.ItemByID(id));
}
}
foreach (XmlNode node in playerData.SelectNodes("/Player/PlayerQuests/PlayerQuest"))
{
int id = Convert.ToInt32(node.Attributes["ID"].Value);
bool isCompleted = Convert.ToBoolean(node.Attributes["IsCompleted"].Value);
PlayerQuest playerQuest = new PlayerQuest(World.QuestByID(id));
playerQuest.IsCompleted = isCompleted;
player.Quests.Add(playerQuest);
}
return player;
}
catch
{
// If there was an error with the XML data, return a default player object
return CreateDefaultPlayer();
}
}
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;
}
public void MoveTo(Location location)
{
if (PlayerDoesNotHaveTheRequiredItemToEnter(location))
{
RaiseMessage("You must have a " + location.ItemRequiredToEnter.Name + " to enter this location.");
return;
}
// The player can enter this location
CurrentLocation = location;
if (!LocationsVisited.Contains(CurrentLocation.ID))
{
LocationsVisited.Add(CurrentLocation.ID);
}
CompletelyHeal();
if (location.HasAQuest)
{
if (PlayerDoesNotHaveThisQuest(location.QuestAvailableHere))
{
GiveQuestToPlayer(location.QuestAvailableHere);
}
else
{
if (PlayerHasNotCompleted(location.QuestAvailableHere) &&
PlayerHasAllQuestCompletionItemsFor(location.QuestAvailableHere))
{
GivePlayerQuestRewards(location.QuestAvailableHere);
}
}
}
SetTheCurrentMonsterForTheCurrentLocation(location);
}
public void MoveNorth()
{
if (CurrentLocation.LocationToNorth != null)
{
MoveTo(CurrentLocation.LocationToNorth);
}
}
public void MoveEast()
{
if (CurrentLocation.LocationToEast != null)
{
MoveTo(CurrentLocation.LocationToEast);
}
}
public void MoveSouth()
{
if (CurrentLocation.LocationToSouth != null)
{
MoveTo(CurrentLocation.LocationToSouth);
}
}
public void MoveWest()
{
if (CurrentLocation.LocationToWest != null)
{
MoveTo(CurrentLocation.LocationToWest);
}
}
public void UseWeapon(Weapon weapon)
{
int damage = RandomNumberGenerator.NumberBetween(weapon.MinimumDamage, weapon.MaximumDamage);
if (damage == 0)
{
RaiseMessage("You missed the " + CurrentMonster.Name);
}
else
{
CurrentMonster.CurrentHitPoints -= damage;
RaiseMessage("You hit the " + CurrentMonster.Name + " for " + damage + " points.");
}
if (CurrentMonster.IsDead)
{
LootTheCurrentMonster();
// "Move" to the current location, to refresh the current monster
MoveTo(CurrentLocation);
}
else
{
LetTheMonsterAttack();
}
}
private void LootTheCurrentMonster()
{
RaiseMessage("");
RaiseMessage("You defeated the " + CurrentMonster.Name);
RaiseMessage("You receive " + CurrentMonster.RewardExperiencePoints + " experience points");
RaiseMessage("You receive " + CurrentMonster.RewardGold + " gold");
AddExperiencePoints(CurrentMonster.RewardExperiencePoints);
Gold += CurrentMonster.RewardGold;
// Give monster's loot items to the player
foreach (InventoryItem inventoryItem in CurrentMonster.LootItems)
{
AddItemToInventory(inventoryItem.Details);
RaiseMessage(string.Format("You loot {0} {1}", inventoryItem.Quantity, inventoryItem.Description));
}
RaiseMessage("");
}
public void UsePotion(HealingPotion potion)
{
RaiseMessage("You drink a " + potion.Name);
HealPlayer(potion.AmountToHeal);
RemoveItemFromInventory(potion);
// The player used their turn to drink the potion, so let the monster attack now
LetTheMonsterAttack();
}
public void AddItemToInventory(Item itemToAdd, int quantity = 1)
{
InventoryItem existingItemInInventory = Inventory.SingleOrDefault(ii => ii.Details.ID == itemToAdd.ID);
if (existingItemInInventory == null)
{
Inventory.Add(new InventoryItem(itemToAdd, quantity));
}
else
{
existingItemInInventory.Quantity += quantity;
}
RaiseInventoryChangedEvent(itemToAdd);
}
public void RemoveItemFromInventory(Item itemToRemove, int quantity = 1)
{
InventoryItem item = Inventory.SingleOrDefault(ii => ii.Details.ID == itemToRemove.ID && ii.Quantity >= quantity);
if (item != null)
{
item.Quantity -= quantity;
if (item.Quantity == 0)
{
Inventory.Remove(item);
}
RaiseInventoryChangedEvent(itemToRemove);
}
}
public string ToXmlString()
{
XmlDocument playerData = new XmlDocument();
// Create the top-level XML node
XmlNode player = playerData.CreateElement("Player");
playerData.AppendChild(player);
// Create the "Stats" child node to hold the other player statistics nodes
XmlNode stats = playerData.CreateElement("Stats");
player.AppendChild(stats);
// Create the child nodes for the "Stats" node
CreateNewChildXmlNode(playerData, stats, "CurrentHitPoints", CurrentHitPoints);
CreateNewChildXmlNode(playerData, stats, "MaximumHitPoints", MaximumHitPoints);
CreateNewChildXmlNode(playerData, stats, "Gold", Gold);
CreateNewChildXmlNode(playerData, stats, "ExperiencePoints", ExperiencePoints);
CreateNewChildXmlNode(playerData, stats, "CurrentLocation", CurrentLocation.ID);
if (CurrentWeapon != null)
{
CreateNewChildXmlNode(playerData, stats, "CurrentWeapon", CurrentWeapon.ID);
}
// Create the "LocationsVisited" child node to hold each LocationVisited node
XmlNode locationsVisited = playerData.CreateElement("LocationsVisited");
player.AppendChild(locationsVisited);
// Create an "LocationVisited" node for each item in the player's inventory
foreach (int locationID in LocationsVisited)
{
XmlNode locationVisited = playerData.CreateElement("LocationVisited");
AddXmlAttributeToNode(playerData, locationVisited, "ID", locationID);
locationsVisited.AppendChild(locationVisited);
}
// Create the "InventoryItems" child node to hold each InventoryItem node
XmlNode inventoryItems = playerData.CreateElement("InventoryItems");
player.AppendChild(inventoryItems);
// Create an "InventoryItem" node for each item in the player's inventory
foreach (InventoryItem item in Inventory)
{
XmlNode inventoryItem = playerData.CreateElement("InventoryItem");
AddXmlAttributeToNode(playerData, inventoryItem, "ID", item.Details.ID);
AddXmlAttributeToNode(playerData, inventoryItem, "Quantity", item.Quantity);
inventoryItems.AppendChild(inventoryItem);
}
// Create the "PlayerQuests" child node to hold each PlayerQuest node
XmlNode playerQuests = playerData.CreateElement("PlayerQuests");
player.AppendChild(playerQuests);
// Create a "PlayerQuest" node for each quest the player has acquired
foreach (PlayerQuest quest in Quests)
{
XmlNode playerQuest = playerData.CreateElement("PlayerQuest");
AddXmlAttributeToNode(playerData, playerQuest, "ID", quest.Details.ID);
AddXmlAttributeToNode(playerData, playerQuest, "IsCompleted", quest.IsCompleted);
playerQuests.AppendChild(playerQuest);
}
return playerData.InnerXml; // The XML document, as a string, so we can save the data to disk
}
private bool HasRequiredItemToEnterThisLocation(Location location)
{
if (location.DoesNotHaveAnItemRequiredToEnter)
{
return true;
}
// See if the player has the required item in their inventory
return Inventory.Any(ii => ii.Details.ID == location.ItemRequiredToEnter.ID);
}
private void SetTheCurrentMonsterForTheCurrentLocation(Location location)
{
// Populate the current monster with this location's monster (or null, if there is no monster here)
CurrentMonster = location.NewInstanceOfMonsterLivingHere();
if (CurrentMonster != null)
{
RaiseMessage("You see a " + CurrentMonster.Name);
}
}
private bool PlayerDoesNotHaveTheRequiredItemToEnter(Location location)
{
return !HasRequiredItemToEnterThisLocation(location);
}
private bool PlayerDoesNotHaveThisQuest(Quest quest)
{
return Quests.All(pq => pq.Details.ID != quest.ID);
}
private bool PlayerHasNotCompleted(Quest quest)
{
return Quests.Any(pq => pq.Details.ID == quest.ID && !pq.IsCompleted);
}
private void GiveQuestToPlayer(Quest quest)
{
RaiseMessage("You receive the " + quest.Name + " quest.");
RaiseMessage(quest.Description);
RaiseMessage("To complete it, return with:");
foreach (QuestCompletionItem qci in quest.QuestCompletionItems)
{
RaiseMessage(string.Format("{0} {1}", qci.Quantity,
qci.Quantity == 1 ? qci.Details.Name : qci.Details.NamePlural));
}
RaiseMessage("");
Quests.Add(new PlayerQuest(quest));
}
private bool PlayerHasAllQuestCompletionItemsFor(Quest quest)
{
// See if the player has all the items needed to complete the quest here
foreach (QuestCompletionItem qci in quest.QuestCompletionItems)
{
// Check each item in the player's inventory, to see if they have it, and enough of it
if (!Inventory.Any(ii => ii.Details.ID == qci.Details.ID && ii.Quantity >= qci.Quantity))
{
return false;
}
}
// If we got here, then the player must have all the required items, and enough of them, to complete the quest.
return true;
}
private void RemoveQuestCompletionItems(Quest quest)
{
foreach (QuestCompletionItem qci in quest.QuestCompletionItems)
{
InventoryItem item = Inventory.SingleOrDefault(ii => ii.Details.ID == qci.Details.ID);
if (item != null)
{
RemoveItemFromInventory(item.Details, qci.Quantity);
}
}
}
private void AddExperiencePoints(int experiencePointsToAdd)
{
ExperiencePoints += experiencePointsToAdd;
MaximumHitPoints = (Level * 10);
}
private void GivePlayerQuestRewards(Quest quest)
{
RaiseMessage("");
RaiseMessage("You complete the '" + quest.Name + "' quest.");
RaiseMessage("You receive: ");
RaiseMessage(quest.RewardExperiencePoints + " experience points");
RaiseMessage(quest.RewardGold + " gold");
RaiseMessage(quest.RewardItem.Name, true);
AddExperiencePoints(quest.RewardExperiencePoints);
Gold += quest.RewardGold;
RemoveQuestCompletionItems(quest);
AddItemToInventory(quest.RewardItem);
MarkPlayerQuestCompleted(quest);
}
private void MarkPlayerQuestCompleted(Quest quest)
{
PlayerQuest playerQuest = Quests.SingleOrDefault(pq => pq.Details.ID == quest.ID);
if (playerQuest != null)
{
playerQuest.IsCompleted = true;
}
}
private void LetTheMonsterAttack()
{
int damageToPlayer = RandomNumberGenerator.NumberBetween(0, CurrentMonster.MaximumDamage);
RaiseMessage("The " + CurrentMonster.Name + " did " + damageToPlayer + " points of damage.");
CurrentHitPoints -= damageToPlayer;
if (IsDead)
{
RaiseMessage("The " + CurrentMonster.Name + " killed you.");
MoveHome();
}
}
private void HealPlayer(int hitPointsToHeal)
{
CurrentHitPoints = Math.Min(CurrentHitPoints + hitPointsToHeal, MaximumHitPoints);
}
private void CompletelyHeal()
{
CurrentHitPoints = MaximumHitPoints;
}
private void MoveHome()
{
MoveTo(World.LocationByID(World.LOCATION_ID_HOME));
}
private void CreateNewChildXmlNode(XmlDocument document, XmlNode parentNode, string elementName, object value)
{
XmlNode node = document.CreateElement(elementName);
node.AppendChild(document.CreateTextNode(value.ToString()));
parentNode.AppendChild(node);
}
private void AddXmlAttributeToNode(XmlDocument document, XmlNode node, string attributeName, object value)
{
XmlAttribute attribute = document.CreateAttribute(attributeName);
attribute.Value = value.ToString();
node.Attributes.Append(attribute);
}
private void RaiseInventoryChangedEvent(Item item)
{
if (item is Weapon)
{
OnPropertyChanged("Weapons");
}
if (item is HealingPotion)
{
OnPropertyChanged("Potions");
}
}
private void RaiseMessage(string message, bool addExtraNewLine = false)
{
if (OnMessage != null)
{
OnMessage(this, new MessageEventArgs(message, addExtraNewLine));
}
}
}
}
PlayerDataMapper.cs
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;
int currentLocationID;
// 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"];
currentLocationID = (int)reader["CurrentLocationID"];
// Create the Player object, with the saved game values
player = Player.CreatePlayerFromDatabase(currentHitPoints, maximumHitPoints, gold,
experiencePoints, currentLocationID);
reader.Close();
}
// 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);
}
}
reader.Close();
}
// 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);
}
}
reader.Close();
}
// Read the rows/records from the LocationVisited table, and add them to the player
using (SqlCommand locationVisitedCommand = connection.CreateCommand())
{
locationVisitedCommand.CommandType = CommandType.Text;
locationVisitedCommand.CommandText = "SELECT * FROM LocationVisited";
SqlDataReader reader = locationVisitedCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int id = (int)reader["ID"];
// Add the item to the player's LocationsVisited property
player.LocationsVisited.Add(id);
}
}
reader.Close();
}
player.CurrentLocation = World.LocationByID(currentLocationID);
// 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();
}
}
// Delete existing LocationVisited rows
using (SqlCommand deleteLocationVisitedCommand = connection.CreateCommand())
{
deleteLocationVisitedCommand.CommandType = CommandType.Text;
deleteLocationVisitedCommand.CommandText = "DELETE FROM LocationVisited";
deleteLocationVisitedCommand.ExecuteNonQuery();
}
// Insert LocationVisited rows, from the player object
foreach (int locationVisitedID in player.LocationsVisited)
{
using (SqlCommand insertLocationVisitedCommand = connection.CreateCommand())
{
insertLocationVisitedCommand.CommandType = CommandType.Text;
insertLocationVisitedCommand.CommandText = "INSERT INTO LocationVisited (ID) VALUES (@ID)";
insertLocationVisitedCommand.Parameters.Add("@ID", SqlDbType.Int);
insertLocationVisitedCommand.Parameters["@ID"].Value = locationVisitedID;
insertLocationVisitedCommand.ExecuteNonQuery();
}
}
}
}
catch(Exception ex)
{
// We are going to ignore errors, for now.
}
}
}
}
If you have any problems applying this change, or notice any other bugs, please leave a comment below.