Adding SQL to SuperAdventure
When we add the SQL Server code to SuperAdventure, we will check to see if the database exists. If it doesn’t, the program will continue to use the XML file to save the player’s game data.
This way, people can still use the program if they don’t want to install SQL Server.
Which SQL database engine?
For these SQL lessons, I will use Microsoft SQL Server 2014.
If you do not have, or cannot install, Microsoft SQL Server, you can use one of the other SQL database engines available. Two other popular SQL database engines are: PostgreSQL and MySQL.
If you use one of the other SQL database engines, there will be a few small differences. I will try to mention the areas that work different from Microsoft SQL Server. If you see a problem, please leave a comment below the lesson. I will try to find an answer to get your SQL engine working.
Preparing to install Microsoft SQL Server
It’s best to install SQL Server before installing Visual Studio. However, if you are at this point in the lessons, you already Visual Studio installed. So, we will install SQL Server now.
Before installing Microsoft SQL Server, you need to install version 3.5 of the .NET Framework. You even need to do it if you have a newer version installed. SQL Server has some specific things it needs from version 3.5.
The video below shows how to install .NET 3.5.
Or, you can open your Control Panel, go to Programs and Features, select “Turn Windows features on or off”, and check the box for “.NET Framework 3.5 (includes .NET 2.0 and 3.0)”. This will try to install the 3.5 Framework from your Windows installation disks, or from the Internet.
Microsoft SQL Server installation video
Here is a video that shows how to install Microsoft SQL Server 2014.
Parts of SQL Server
There are usually two things you install, when you install any SQL program – the database engine and the management tool.
The database engine is the program that runs in the background. It’s always running, but it does not have a user interface. This is what your program uses to save and retrieve data.
The management tool has a user interface. It lets you easily manage your databases. Microsoft named theirs, “Microsoft SQL Server Management Studio”. This is the program we will use to create the tables in our database. If you use a different SQL engine, they will probably have their own management program.
Next lesson: Lesson 22.2 – Creating database tables from classes
Previous lesson: Lesson 21.4 – Completing the trading screen
All lessons: Learn C# by Building a Simple RPG Index
Hey,
Do I have to install SQL server?
Can’t I just create data tables with visual studio?
Try this:
In the Visual Studio menu, select Tools -> Connect to Database…
Select “Microsoft SQL Server Database File”
Click “Continue”
Enter “SuperAdventure” for the database name
Select “Use Windows Authentication”
Click “OK” and create the database
This should create your database.
From the Visual Studio menu, select View -> Server Explorer, and you should see your database in “Data Connections”.
To add tables, right-click on the “Tables” folder, and select “Add New Table”.
You can create the tables by copying the scripts into the “T-SQL” area, or by manually entering them in the [Design] area.
Please let me know if that works for you, or not.
Yes, it works 🙂 thanks a lot
You’re welcome. The final lessons for SQL should be available Thursday night (US/Houston time)
Hello, I have a little question and sorry if my english is bad.
After I updated all windows think from Windows Update I cannot seen this: “.NET Framework 3.5 (includes .NET 2.0 and 3.0)” in my Windows Features. By the way I have Windows 7.
What should I do? the single think .NET i see is: “Microsoft .NET Framework 3.5.1
Thx in advance.
.NET Framework is an updated version of 3.5. So, it should work. Did you try to install SQL Server? If you tried to install SQL Server, did you see any errors?
I instaled postgreSQL (i cant instal microsoft sql). And no error. I need to do more than in this tutorial https://www.youtube.com/watch?v=1GlQpflJZTU or i can go to your next lesson?
If you use PostgreSQL, you will need to make some changes in the next lessons.
To connect to the database, you might need a different connection string, or to use Npgsql (http://www.npgsql.org/). The scripts to create the tables, and insert/read/delete records in the database (Lessons 22.2 and 22.3) might also be different. If you have problems with those, please tell me, and I will try to help.
Everything is fine for moment but when i copy-paste the code from 22.2 in postgresql I put it in “SQL” expansion (because i cant see anything else) and after that I cannot rename my table or to create it.
Try using this code, and tell me if it works. If it does not work, please take a screenshot, so I can see the problem.
CREATE TABLE SavedGame(CurrentHitPoints int NOT NULL,
MaximumHitPoints int NOT NULL,
Gold int NOT NULL,
ExperiencePoints int NOT NULL,
CurrentLocationID int NOT NULL
);
thanx you so much it work now, but it is a problem if instead of CurrentHitPoints, MaximumHitPoints my program change automaticaly in lower case all upper case? Sorry for many question but I am a tottaly beginer.
Are you talking about the column names, in the SQL database? If yes, for SQL databases, a column named “CurrentHitPoints” is the same as a column named “CURRENTHITPOINTS” or “currenthitpoints”. SQL is not like C#, where the difference would create a problem.
I’m not sure, but why do we need SQL why just don’t we leave the last method of savings ?
You don’t need to use SQL. You can use the XML file. But, if you want to learn the basics of how to use SQL, this lesson will show you how to connect the game to a SQL database.
If I use the database now for saving and then move the game to another computer (not the project, but the complete game for playing, like here my friend, check this out), am I still able to save the game as normal (to the database) or I have to install the database to that friend’s computer as well ?
Your friend would need to install SQL Server on their computer. But, in lesson 22.3 (when we start connecting the game to the database), the code is written to use the XML file if it cannot find the database.
Honestly, I skipped this 3 lessons about saving game to the database and when I got to lesson 24.1, the game stop working properly. The main problem was database, I delete it from code, then vendor start working strangly, he was in every location and cause only problems. So I decide give it a shot, return back and try the database, but after your response, I don’t know.
The database is super useful for managing data like save game, but if SQL Server need to be installed on every pc on which the game will be played, it’s just turn to useless as I look at it. Maybe there is the feature which force user to install SQL server before play the game, but I rather be thinking about different solution.
It was always my curiosity, how the save game are managed in popular games and I really thought on database as the main option. I saw some Indie games using XML, it’s really easy to cheat, if the game is saved in XML, so that option is not good for bigger games.
I only know about 3 types, how to save game: XML, DB and Binary, but binary is something I never try, only hear about it. It’s hard to decide this things, if you know so very little about this. As I mention before, I’m working in PHP for years and always using MySQL, for me it’s the best choice, but for C#, I don’t really know.
If the vendor shows up in every location, there is probably another problem that is not related to the database.
For this small game, you would not want to require SQL Server. You would probably only use data files, or a simple database option like SQLite. I wrote that code because several people wanted to see how to use SQL Server. You would probably only use SQL Server for this game if you made it into a website game, and wanted to store data for many players.
One way to make it more difficult to cheat with an XML file is to use base64-encoding on the XML string. This is not high-security, but it will prevent casual players from modifying the saved game file. If you wanted more security, you could encrypt the file or create your own special format (for example, the first three bytes of the files are the player’s level, bytes 4-24 holds their name, etc.). But, that takes a lot of work.
I don’t know why he shows up everywhere, I checked the code and everything seems alright. It’s coded only in townsquare, but maybe I’ll be able to found, why he shows everywhere, as soon as the window with the game does start to appear, but I really need your help in this.
Yeah, I know that the game so small can be stored like this, but if I choose to use SQLite, then again, I need to install SQLite to every pc, where the game would be played, am I ? and the reason why I choose to learn C# is not coding for website, but Windows and maybe Android app/games, but it’s harder than I thought it will be.
Yeah, I know base64, it’s an option, no doubt about it.. maybe I give it a shot, when I learn at least the simple things in C# and if I would be capable of make something similar to your game.
SQLite does not need a separate installation for a server – like SQL Server does. You include the SQLite library (DLLs) in your program when you write/compile it. That library knows how to save and load data without a separate program. But, if you are only storing a small amount of data, for a single user, you probably only want to use data files with XML or JSON. In programming, you usually want to do the simplest thing that works. Don’t make the program more complex unless it absolutely must be more complex.
I replied to your other comment with a suggestion for the game not starting. Let me know if that does not fix the problem.