Lesson 22.2 – Creating database tables from classes

Lesson Objectives

At the end of this lesson, you will know…

• Design database tables, from custom classes
• How to create a SQL database, using the SQL Server Management Studio
• How to create tables, using DDL (Data Definition Language SQL statements)
• The difference between DDL (Data Definition Language) and DML (Data Manipulation Language) SQL statements

Now, we will design and build the database, with its tables.

We’ll use the structure of the PlayerData.xml file to design our tables.

A typical PlayerData.XMl file looks like this:

<Player>
     <Stats>
           <CurrentHitPoints>10</CurrentHitPoints>
           <MaximumHitPoints>10</MaximumHitPoints>
           <Gold>170</Gold>
           <ExperiencePoints>79</ExperiencePoints>
           <CurrentLocation>2</CurrentLocation>
     </Stats>
     <InventoryItems>
           <InventoryItem ID="1" Quantity="1" />
           <InventoryItem ID="3" Quantity="21" />
           <InventoryItem ID="7" Quantity="1" />
           <InventoryItem ID="4" Quantity="4" />
           <InventoryItem ID="5" Quantity="3" />
           <InventoryItem ID="10" Quantity="1" />
           <InventoryItem ID="2" Quantity="6" />
     </InventoryItems>
     <PlayerQuests>
           <PlayerQuest ID="1" IsCompleted="True" />
           <PlayerQuest ID="2" IsCompleted="True" />
     </PlayerQuests>
</Player>

We can have an unlimited number of InventoryItem nodes. The same with PlayerQuest nodes. This is called a “one-to-many” relationship. One Player can have many InventoryItem/PlayerQuest objects. (Technically, this is a “one to zero-to-many” relationship, since it is possible to have zero items in the list properties.)

So, when we design our tables, we will have one table hold the Player stats (CurrentHitPoints, etc.). Then, we will create two more tables: one to hold the player’s inventory list, and the second to hold their quest list.

If you are not using Microsoft SQL Server, reading this steps should be useful (and might work). There are some additional comments at the end of the lesson that will summarize what you need to do for your environment.

Step 1: Start Microsoft SQL Server Management Studio.

You will see a popup screen, asking you to connect to the server. The server name should be filled in with your computer’s name. If it isn’t, you can fill in your computer’s name. You can also use “.” (a period/full stop, without the quotes), or “(local)”, to signify “the SQL engine on the current/local computer”.

Now you are connected to your SQL Server engine.

NOTE: If you don’t know your computer name, open the Control Panel, select Administrative Tools -> System Information -> and get the value from “System Name”.

Step 2: In the left-most section, is the Object Explorer. This is similar to the Solution Explorer in Visual Studio, except it holds all the objects (databases, users, etc.) of the SQL Server.

Click the “+” next to “Databases”. This shows you the databases that are currently running with this SQL engine. If this is a new installation of SQL Server, you will only see “System Databases” and “Database Snapshots”. When you add your own databases, you will see them here.

Normally, you create one database per program. That database will hold all the information that program needs. For larger programs, or if you are going to store a huge amount of data, you might have multiple databases. But for SuperAdventure, we only need one.

You can create the database for SuperAdventure by using the UI, or by using a script. We will use the UI.

A script is a set of SQL statements you run on a SQL Server. We will use them to create the tables, so you’ll see how they work. However, the script to create the database needs some information that is specific to your computer. So, we won’t use one for this step – because I don’t know exactly how your computer is set up.

Creating a new database with the UI

Right-click on the “Databases” folder icon and select “New Database…”. A screen will pop up.

Enter “SuperAdventure” in the Database Name textbox, and click the “OK” button. There are some more advanced options you can change before clicking “OK”, but we don’t need them for SuperAdventure. Usually, they will only be used with larger databases.

After you click the “OK” button, you should see the SuperAdventure database listed under the “Databases” folder.

Step 3: Click the “+”, next to the SuperAdventure database, then click the “+” next to “Tables”.

The tables are where you store data in a SQL database. Right now, there are only the built-in system tables. We need to add our custom tables. This is similar to how we created the classes in the Engine project.

To create a new table, you could right-click on “Tables”, select “New”, then “Table”. This would give you a way to create the database in the UI. But we will create the table using SQL statements/commands.

NOTE: There are two types of SQL statements: DDL and DML. DDL stands for “Data Definition Language”. These are the SQL commands you use to create, and modify, tables and their definitions (the structure of your database). DML stands for “Data Manipulation Language”. These are the commands you use to add, read, delete, and modify the data inside your database tables. Right now, we are using DDL statements, to create the structure of our database.

In the menu at the top, click on “New Query”. This will create a place for us to run our DDL SQL statements.

Inside the query section (the middle of the screen), paste in this SQL code:

USE [SuperAdventure]
GO
/****** Object: Table [dbo].[SavedGame]   Script Date: 2/2/2016 6:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SavedGame](
       [CurrentHitPoints] [int] NOT NULL,
       [MaximumHitPoints] [int] NOT NULL,
       [Gold] [int] NOT NULL,
       [ExperiencePoints] [int] NOT NULL,
       [CurrentLocationID] [int] NOT NULL
) ON [PRIMARY]
GO

This is what the different parts of the SQL statements mean:

USE [SuperAdventure]
GO

This lets SQL Server Management Studio know we want to work in the SuperAdventure database.

The ANSI_NULLS and QUOTED_IDENTIFIER lines deal with how SQL treats some of your commands. These are common settings that you probably won’t need to change, unless you do something special in your database.

The CREATE TABLE part is the important part. This is where we define the structure of the database.

Think of a database table as a spreadsheet. If you created a spreadsheet to store your expenses, you would probably have a column for the date you spent the money, another column for a description of what you bought, and a third column for the amount of money you spent. Each row on your spreadsheet would be an expense.

A database table works similarly.

In the “CREATE TABLE” command, we define the columns we want to store in this table.

Our table will be named “SavedGame”. The “[dbo]” means the table belongs to the “dbo” schema. A schema is a way to group your tables together, and “dbo” is the default DataBase Owner. Having more than one schema is something you will probably only do in big applications.

This table will have the five columns that hold the player’s stats (CurrentHitPoints, MaximumHitPoints, etc.). Each of these columns will hold integer values (the “[int]” part is where we define the column’s datatype). The “NOT NULL” means that we do not want to allow null/empty values in here. If we ever tried to set one of those values to NULL. SQL Server would raise an error.

The “ON [PRIMARY]” part means we want to store this table’s data in the primary file group. We only have one file group set up (the default when you create a database). You will probably only have more than one file group if you work with much larger databases.

Finally, click on the “! Execute”, in the menu, to run this set of SQL statements. You should see a message that says “Command(s) completed successfully.” If you right-click on the “Tables” folder (in the Object Explorer), and select “Refresh”, you should see the SavedGame table.

Click on “New Query”, paste in this SQL code, and execute this query to create the Inventory table:

USE [SuperAdventure]
GO
/****** Object: Table [dbo].[Inventory]   Script Date: 2/2/2016 6:20:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Inventory](
       [InventoryItemID] [int] NOT NULL,
       [Quantity] [int] NOT NULL
) ON [PRIMARY]
GO

Then, click “New Query” again, paste in this query, and execute it to create the Quest table:

USE [SuperAdventure]
GO
/****** Object: Table [dbo].[Quest]   Script Date: 2/2/2016 6:21:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Quest](
       [QuestID] [int] NOT NULL,
       [IsCompleted] [bit] NOT NULL
) ON [PRIMARY]
GO

If you refresh the “Tables” folder again, you should see the three tables we will use.

If you are not using Microsoft SQL Server

At the end of this lesson, you should have an empty database with the tables you will need for the next lesson. If you have a different SQL engine, you need to create a database named “SuperAdventure”, with these tables:

If your database engine does not have the ability to specify if the column should allow nulls, don’t worry – you don’t really need that. Our code will prevent that situation from happening, so it isn’t really necessary.

Also, any numeric datatype should be OK, if your database does not have an integer datatype (although, most of them should have it). In the Quest table, the IsCompleted is a boolean value, which is saved as a “bit” datatype in MS SQL Server. Your database may call their datatype something like “boolean”, or “true/false”.

Summary

For these tables, we will only ever have one row/record in the SavedGame table. When the player exits their game, we will overwrite the old SavedGame row/record.

The Inventory and Quest tables will have one row per inventory item/quest that the player has. That might be zero rows, or it could be dozens (if you modified the game to have many more quests and items).

For these tables, all our columns have an “int” datatype. There are many other datatypes SQL can store, but we don’t need to use them right now. If we need to store string values, we would create “varchar” columns. Boolean properties are stored in “bit” columns. .NET Datetime are stored in SQL Datetime datatype columns.

Here is a list of SQL Server datatypes, to give you an idea of what else is available.

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

Previous lesson: Lesson 22.1 – Installing MS SQL Server on your computer

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