Hey there, I'm Rowan. I'm currently studying Software Engineering at UTM, and I'm also an IT Entrepreneur. This is my programming blog - you'll find loads of C# stuff in here, as well as MySQL and C tutorials with source. Comments are most welcome.
I too, am a bug within Māyā.
My Other Blog(s) and Site(s)
Friend sites and blogs
My Recent Posts
My C-Sharp Ideas
Tuesday, September 26, 2006
Connecting MySQL and C#
I wrote an article on how you can connect MySQL to C# a while back. I thought it'd be interesting to give you guys a "better" article on how this can be done, with more theory and examples.
MySQL is quite cool if you don't have huge amounts of data to handle - and it's free. To better understand this tutorial, you'll need:
1. MySQL Server
2. MySQL Administrator
3. MySQL Connector .net
MySQL Server is the real thing. It's the core of the database and is accessible after install through the command line. The MySQL server, by default, is a service running in the background. If you cannot connect to the MySQL server, check whether it's running in your services list.
The MySQLAdministrator can be freely downloaded at the MySQL website. It helps you create and manage databases as well as tables with a graphical user interface. Yes. No console and commands bullshit - a few clicks and you'll be rolling with your own database and tables. If you're having problems to connect to the MySQL Administrator, note that:
The Server Host should be set to the machine-name or IP where your MySQL Server is located (if you installed it on your own pc, it's localhost).
The Username is root
The Password is the password you specified during install. Blank if you didn't specify any or wasn't even asked for one and...
The Port is 3306, unless you specified something else during install.
The MySQL Connector .Net
This tool is also freely available for downloading from the MySQL website. It's a DLL that you should add as reference in your C# project. The DLL is located under your %programfiles%\MySQL\MySQL Connector Net 1.0.4\bin\.NET 1.1\MySql.Data.dll, unless you specified something else during install.
Your First C# Program That Uses MySQL
Right. You've installed the MySQL server, the MySQL Administrator and the MySQL Connector. What you need to do is create a new console application, and add a reference to MySQL's connector (normally located under %programfiles%\MySQL\MySQL Connector Net 1.0.4\bin\.NET 1.1\MySql.Data.dll).
Opening and closing the connection is easy, now that you've added the reference:
Note that the format of the SQL query string is as follows:
Where $servername$ is the name of the machine where your MySQL Server is running (localhost if it's on your own machine) and $databasename$ the name of your database (or catalog, as some call it), $username$ a user which is allowed to access the database you selected and $password$ the password of the user. For the dickheads who are too lazy to write their own connection strings, here goes a method which should generate good connection strings for you:
This method generates a connection string that you can use in the MySQLConnection object constructor. The console application, with the new added method to generate the Connection String now looks like this:
However, all we're doing is to connect and disconnect from the MySQL server without adding or taking out data. If you want to know more about how to do this, read this article.
Now, for those who want to deal with single inverted commas in MySQL and block SQL injection attacks, here are a few solutions for you:
1. Replace all single inverted commas by two single inverted commas.
2. Replace all single inverted commas by a slash and then the inverted comma.
3. Convert the inverted comma (and any other character that is not "normal") into its HTML-ASCII equivalent before adding it into the database.