Connecting to a MySQL Database in .NET

.NET is great and provides a ton of tools for working with databases and data sources. There are times though that a data source may not be inherently supported. In a case such as MySQL you may not see the MySQL connection options when creating binding sources or data sources for your project.

What do you do then?

Well, all is not lost! It is just a little extra work.

First, download a few tools which include the MySQL Connectors:

◉ https://dev.mysql.com/downloads/file/?id=492453
◉ https://dev.mysql.com/downloads/file/?id=491638
◉ https://dev.mysql.com/downloads/connector/net/#downloads

After you have downloaded the installation files, install them one by one. Make sure that if you are using Visual Studio, that you close it before installing.

After you have installed the tools for MySQL, open Visual Studio and create a Windows Forms project.

Select the BindingSource control from the toolbox, and double click it. In the properties window of the Binding Source, select the DataSource property and then select Add Project Data Source. This will open the Data Source Configuration Wizard.

Select Database, then select Next.

Select Dataset, then select Next.

Select the New Connection button.

Select the Change button.

Notice that MySQL Database is now present in the list, as shown in Figure 1.

Figure 1 – Change Data Source

Select MySQL Database from the list, then click OK, the Add Connection dialog box will look like Figure 2.

Figure 2 – Add Connection

Enter the Server name, Username and Password as requested by Figure 2, then click OK.

Choose your Database objects needed as shown in Figure 3.

Figure 3 – Database objects

Click Finish.

You are now able to connect with a MySQL database and work with it.

What if I do not want to use a Bindingsource, or even design view? What if I want to use only code?

Let's have a look.

To import the Data capabilities, you need to import the MySQL namespaces as well as the System.Data name space, here's how:

using MySql.Data.MySqlClient;

using System.Data;

You simply make use of the using statement in C#. Now you have access to all the functionality of the MySQLClient namespace

Create a connection object with the following code:

MySqlConnectionParameters connectionParameters = 

   new MySqlConnectionParameters("Server", "Database", "User", "Password");

string conStr = 

 "server=ServerName;port=PortNumber;database=DatabaseName;uid=Username;password=Password;TreatTinyAsBoolean=false";

To obtain data from a MySQL Stored Procedure, use the following code:

   MySqlConnection con = new MySqlConnection(conStr);

   MySqlDataAdapter adapter = new MySqlDataAdapter();

   MySqlCommand cmd;

   cmd = new MySqlCommand("CALL StoredProcedureName(@Parameter)", con);

   cmd.Parameters.AddWithValue("@Parameter", Parameter);

   cmd.Parameters.AddWithValue("@ToDate", ToDate);

   adapter.SelectCommand = cmd;

   DataSet ds = new DataSet();

   adapter.Fill(ds);