I See Sharp

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
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.

MySQL Administrator
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:

using System;
using MySql.Data.MySqlClient;

namespace ConsoleApplication1
{
class ConnectToMySQL
{
    [STAThread]
    static void Main(string[] args)
    {
        //create a MySQL connection with a query string
        MySqlConnection connection = new MySqlConnection("server=localhost;database=cs;uid=root;password=abcdaaa");

        //open the connection
        connection.Open();

        //close the connection
        connection.Close();
    }
}
}

Note that the format of the SQL query string is as follows:

server=$servername$;database=$databasename$;uid=$username$;password=$password$;

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:

       /// <summary>
     /// Generates a connection string for lazy bastards
     /// </summary>
     /// <param name="server">The name or IP of the machine where the MySQL server is running</param>
     /// <param name="databaseName">The name of the database (catalog)</param>
     /// <param name="user">The user id - root if there are no new users which have been created</param>
     /// <param name="pass">The user's password</param>
     /// <returns></returns>
     public static string CreateConnStr(string server, string databaseName, string user, string pass)
     {
         //build the connection string
         string connStr = "server=" + server + ";database=" + databaseName + ";uid=" +
             user + ";password=" + pass + ";";

         //return the connection string
         return connStr;
     }

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:


using System;
using MySql.Data.MySqlClient;

namespace ConsoleApplication1
{
   class ConnectToMySQL
   {
       [STAThread]
       static void Main(string[] args)
       {
           //generate the connection string
           string connStr = CreateConnStr("localhost", "cs", "root", "abcdaa");
          
           //create a MySQL connection with a query string
           MySqlConnection connection = new MySqlConnection(connStr);

           //open the connection
           connection.Open();

           //close the connection
           connection.Close();
       }

       /// <summary>
       /// Generates a connection string for lazy bastards
       /// </summary>
       /// <param name="server">The name or IP of the machine where the MySQL server is running</param>
       /// <param name="databaseName">The name of the database (catalog)</param>
       /// <param name="user">The user id - root if there are no new users which have been created</param>
       /// <param name="pass">The user's password</param>
       /// <returns></returns>
       public static string CreateConnStr(string server, string databaseName, string user, string pass)
       {
           //build the connection string
           string connStr = "server=" + server + ";database=" + databaseName + ";uid=" +
               user + ";password=" + pass + ";";

           //return the connection string
           return connStr;
       }
   }
}

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.

# posted by Rowy @ Tuesday, September 26, 2006 7 comments

I See Sharp

About Me

My Other Blog(s) and Site(s)

Friend sites and blogs

My Recent Posts

Tutorials

Best Articles

My C-Sharp Ideas

Archives

Blog Directories

Tuesday, September 26, 2006

Connecting MySQL and C#