Connect C# With MySQL

The purpose of this article is to show step-by-step how to connect C# to MySQL; I will be using MySQL Connect/Net for this. Later I will show how to execute INSERT, UPDATE, DELETE and SELECT commands; this will include only setting up the connection.

Getting Started

First make sure you have downloaded and installed the MySQL Connector/Net from the MySQL official website. In this article, I will use Connector/NET version 6.1.6.

I felt the need fpr this when I was developing a Windows form application and wanted to connect it to a database on my website. In this article I'm assuming that you have a cpanel on web and a database in it, you can also install MySQL on your system.

Our first step will be "Adding Reference and Creating the MySQL Connector DLL from the Project".

First we need to add a reference to "mysql"; we can do this by navigating to Solution Explorer and then right-click on the reference folder and then look for MySql.Data
under the .Net tab, as in:

CsharpSQL1.gif

In order to use the application on other computers that don't have the connector installed, we will have to create a DLL from the reference. To do so, we right-click the reference name in our project, and set the copy local to true in its properties:

CsharpSQL2.gif

Now we are done with adding the reference and now we can start our actual coding.

We will start by adding a namespce:

using MySql.Data.MySqlClient;

I have created a simple form with a button in it, our connection will be established when the button is clicked i.e. we will write our code in the OnClicked event of the button. My form looks somewhat like this:

CsharpSQL3.gif

Now comes the actual coding part.

We will have to create the connection string first.

To establish a connection, you must specify the computer you are connecting to, that has MySQL installed. To indicate the computer you are connecting to, use the Data Source, theDataSource, the Host, the Server, the Addr, the Address, or the Network Address attribute of the connection string. If you are connecting to a local database (installed in the same computer where the application is running), you can omit specifying the name of the computer. Otherwise, you can assign localhost to this attribute. To specify the database you want to connect to, the connection string includes an attribute named Database or Initial Catalog.

To specify the user name, use the User Id, the Uid, theUser name, or the Username attribute and for password use the PASSWORD or the PWD (remember that the attributes are not case-sensitive) attribute.

Here is a sample connection string:

string cs = @"server=YOUR_SERVER_NAME;userid=YOUR_USERNAME;password=YOUR_PASSWORD;database=DATABASENAME";

Now the code for the button clicked event:

1.png


That's all. Debug your application; I have added the connection open in the try/catch block so if the connection is established then it will show a message box saying "Connection Established".

CsharpSQL5.gif

Cheers.