I See Sharp: 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
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

Comments:

Just browsing the internet, very, very interesting blog.

# posted by

Freddie L Sirmans, Sr. : 8:14 AM, August 03, 2007

Sorry to bother. I'm having a problem with your code. I'd be very grateful if you could help me.
I added the reference MySql.Data and created a console application with the code you provided (with the modified connection string).
I get an error at the "connection.Open();" line: "Object reference not set to an instance of an object."
Do you have any idea why this is? It looks like it connects since if I use the wrong password or user or server it gives me a different error.

# posted by

Alex Oliveira : 1:15 PM, August 07, 2007

thanks this is stright forward,
simple snad,
easy to understand.
but please refine the comatable version of visual studio,because
I tried Visual Studio 2002 -- failed
however
Visual C# Exepress Edition succeded

# posted by

Anonymous : 2:55 PM, February 23, 2008

Many many thanx for the article. It is really helpful...

# posted by

CustomBrowser : 11:24 AM, October 23, 2008

I'm using your blog for my academic proejects, and find it very useful; Best regards.

# posted by

Positive vibes : 10:47 AM, August 25, 2009

Thanks a lot, but I have a problem.
My database is on a remote server. While I want to get connected, I receive a message like this:
"Access denied for user ...."
How can I solve this problem?

# posted by

Anonymous : 11:28 AM, December 04, 2010

Surely thanks for this code, for it has helped me a lot keep it up and may the Lord help you do more. hope to see more codes from u

# posted by

Anonymous : 4:13 PM, June 22, 2012

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#