Sign in to follow this  
dhammer

MySQL

Recommended Posts

I'm reaching out for help with MySQL. I'm just going to come right out and say, I'm a noob when it comes to databases in general and MySQL more specifically. I'm trying to learn though, my goal at the moment is to be able to create a MySQL database to be used in a MUD that i am making. I am writing the MUD in C# and at the moment i am having a lot of trouble with MySQL basics. If anyone has any pearls of wisdom on how i can start on my path towards this goal i'd appreciate it. I've been surfing the web and most things are either assuming i know basic consepts that i don't yet or just don't seem to answer my question. Thanks

Share this post


Link to post
Share on other sites
I appreciate that website it has a lot of great information, here is my follow up question though. Once i know how to create, delete, and manipulate my databases using the console commands how do i begin using C# to read and manipulate my databases?

Share this post


Link to post
Share on other sites
A good place where I once started on is Tizag's MySQL Tutorial. It will get you started on the basic functions like updating and inserting data. It also goes more advanced into joins (which I still have trouble getting myself), subqueries and grouping. The tutorials use PHP along with it, but for you all that matters is the SQL commands.

Also learn about things such as indexing and database normalization. They will help you create effective data structures in relational DB systems like MySQL.

Share this post


Link to post
Share on other sites
To be honest i opted for MySQL because i knew very little about the different versions of SQL and MySQL is what i heard of. What are the benefits of SQL Server Express and how is it different?

Share this post


Link to post
Share on other sites
Quote:
Original post by dhammer
To be honest i opted for MySQL because i knew very little about the different versions of SQL and MySQL is what i heard of. What are the benefits of SQL Server Express and how is it different?


MySQL is free. MS SQL Server is not.

MS SQL server is basically a proprietary storage engine. You are only limited to the Sybase-derived engine. Where as MySQL is an open-storage engine giving you multiple choices such as MyISAM, BerkleyDB and InnoDb and a few others.

The SQL Syntax is essentially the same with just a few differences.

As far as application development, you probably don't want to connect to any database directly. Use SOAP or some form of secure messaging to communicate over a network.

MS SQL does have the advantage for developing in the .NET environment because it does have a built in OLE container/connection wizard to connect to your database.

mySQL IMHO has worked great for all of the small projects i've developed.

Share this post


Link to post
Share on other sites
MS SQL Server Express is pretty darn near free, if not actually free. Its advantages include built-in integration with Visual Studio (if you're using that toolchain) and a very full-functionality SQL query engine.

For C# projects using under a couple hundred megs of data, I usually use SQLite through the absolutely fantastic System.Data.SQLite library, which gives SQLite the exact same integration advantages that SQL Server Express has. The advantage of SQLite is that there's no server to administer--your database is simply held in a single file or in memory. This makes administrative tasks like backups and moving the database to a new server absolutely trivial. SQLite is, in its current incarnation, extremely fast for small (less than 1GB) data sets. However, it does not perform well with multiple connections to the database--but your typical MUD will be running only one instance of the app, so that won't be a problem.

Share this post


Link to post
Share on other sites
I would think it would be just as beneficial to find a wrapper around something lightwheight if you are using a database for game info storage.

A while ago, when I was working with wxWidgets, I used SQLite for storage. It was fast, and quite easy with the wrapper I had found.

Good luck!
FlyingIsFun1217

Share this post


Link to post
Share on other sites
I thank you all for the information. It seems that there are some advantages as far as the speed, integration with VS, and saving to a single file rather than a server but none of you have said that MySQL is slow, I installed a component which allows me to use MySQL.Data.MySQLClient which has worked well so far, possibly similar to System.Data.SQLite and even though the single file feature seems like a great thing the mention of it not being free sways me back to MySQL. I am trying to make this game with little to no $, it is meant as a learning experience and not too much more.

Again, i appreciate your inputs but with my current information i think that i will stay with MySQL. Please post more reasons for me to switch if you feel it has a large enough benefit.

Share this post


Link to post
Share on other sites
Quote:
Original post by dhammer
...even though the single file feature seems like a great thing the mention of it not being free sways me back to MySQL. I am trying to make this game with little to no $, it is meant as a learning experience and not too much more.

...

Please post more reasons for me to switch if you feel it has a large enough benefit.

SQLite and System.Data.SQLite are absolutely free in every sense--more free than MySQL (you can distribute it without source, etc.) as SQLite is in the public domain.

Share this post


Link to post
Share on other sites
Yes! Just download and install the System.Data.SQLite library, add a reference to the assembly to your project (right-click the project, click Add Reference, go to the .NET tab, and find System.Data.SQLite), and start using the statements directly in your code. As an example, here's how you would open an existing database (or create it if it didn't exist):


void OpenDB() {
if (!System.IO.File.Exists(Filename))
CreateDB();

m_Connection = new SQLiteConnection("Data Source=" + Filename + ";");
m_Connection.Open();
}

void CreateDB() {
if (System.IO.File.Exists(Filename))
System.IO.File.Delete(Filename);

m_Connection = new SQLiteConnection("Data Source=" + Filename + ";New=True;");
m_Connection.Open();
//Create all the tables for your database here.
}



A simple query against the database might look like this:

SQLiteCommand cmd = new SQLiteCommand("delete from Include where FileId=@p0", m_Connection);
cmd.Parameters.Add(new SQLiteParameter("@p0", iFileId));
cmd.ExecuteNonQuery();



Getting the results of select queries is a bit more complicated, but not too bad. You use the SQLiteDataReader class that's returned from ExecuteReader. Don't forget to Close() the SQLiteDataReader, or your next query will fail. Only one reader can be open per database at a time (for any ADO.NET data connection).

SQLiteCommand cmd = new SQLiteCommand("select Name from Variable where ClassId = @p0 order by upper(Name)", m_Connection);
cmd.Parameters.Add(new SQLiteParameter("@p0", iClassId));
SQLiteDataReader r = cmd.ExecuteReader();

List<string> ret = new List<string>();
while (r.Read())
ret.Add((string)r["Name"]);

r.Close();
return (ret);

Share this post


Link to post
Share on other sites
-Download the MySQL server from www.mysql.com
-Install and configure an instance of the server using the configuration wizard which will start after you install or from Start Menu -> Programs -> MySQL 5.0 -> MySQL Config Wizard
-Once the server is installed it should auto-start.
-If on the server computer you can use the MySQL Command Line Client from the start menu (if you checked the box during install to add it) or Start -> Run -> cmd which will open the command prompt

If you are using the MySQL Command Line Client, you should be prompted to enter a password, this default logs you in as root.
If you are using the command prompt continue with the instructions.

-Logging into the mysql server requires that you bring command prompt down to 'C:\', use cd .. to move back 1 directory, cd ..\.. with as many instances of .. for each step if you want to do it in a single command line.
-Once at 'C:\' (or whatever your harddrive letter is) type mysql -h localhost -u root -p
-This is assuming you want to connect to yourself (substitute an ip for localhost if you are connecting to a remote host), that you are logging in as root (substitute a username for root), and that you have a password (remove -p from the end if there is no password, it might work either way but it will skip the prompt for a pw if you remove it).
-Then you will get a prompt saying something like "Enter Password:"
-Enter your password now.

Share this post


Link to post
Share on other sites
Quote:
Original post by dhammer
So is SQLite only for locally stored databases or does it also have features to connect to a remote source?

It only accesses locally-stored databases.

This is not perfect for all situations, but if you're programming a MUD, you're going to want to program your own server application as well as the client application. Allowing client applications to directly access the database opens up a whole host (no pun intended) of security/cheating issues.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this