Connecting C# application with MySQL database

In this article I will explain how to connect C# application with MySQL database

Introduction

In this article we will see how to display data in a Windows Forms application from a MySQL database table

Step 1:

Install MySQL database driver (MySQL Connector/NET) for .NET. Download it from this link http://dev.mysql.com/downloads/connector/net.

Step 2:

Create a new Windows Forms Application

Step 3:

Add reference to MySQL.Data.dll using Add Reference dialog box. Right click on References in the Solution Explorer to open Add Reference dialog box. Select MySQL.Data.dll from the .Net tab. You can also add it using Browse tab by select it from its installed path. In my machine it’s installed at C:MySQLMySQL Connector Net 6.1.2AssembliesMySQL.Data.dll.

Also, Add reference to System.Configuration assembly so that we can access our connection string from App.config file.

Step 4:

Add an Application Configuration File “App.config” and add connection string for connecting MySQL database. Write following inside <configuration> tag in App.config:

&lt;connectionStrings&gt;
     &lt;add name=&quot;ConString&quot; connectionString=&quot;server=localhost; database=databaseName; user id=youruserid; password=yourpassword;&quot;/&gt;
&lt;/connectionStrings&gt;

Step 5:

Drag a DataGridView and a Button control on Form1

Add following two namespace in the Form1.cs code view file:

using MySql.Data.MySqlClient;
using System.Configuration;

Write following in the Button’s click event:

private void btnLoadData_Click(object sender, EventArgs e)
{
     string ConString = ConfigurationManager.ConnectionStrings[&quot;ConString&quot;].ConnectionString;
     MySqlConnection con = new MySqlConnection(ConString);
     string CmdString = &quot;SELECT * FROM customers&quot;;
     MySqlDataAdapter sda = new MySqlDataAdapter(CmdString, con);
     DataSet ds = new DataSet();
     sda.Fill(ds);
     dataGridView1.DataSource = ds.Tables[0].DefaultView;
}

MySql.Data.MySqlClient namespace provides similar classes like we have in System.Data.SqlClient for connecting SQL Server. It is simply appended with “My” in MySQL like SqlConnection becomes MySqlConnection.

Output:

On clicking on the Button, data is displayed in the DataGridView from Customers table in MySQL database

19 thoughts on “Connecting C# application with MySQL database

  1. ashok thapa

    In this line:
    dataGridView1.DataSource = ds.Tables[0].DefaultView;
    there is an error message showing:
    the name dataGridView1 doesnot exist in the current context

    Reply
    1. DEEPAK SHARMA

      Hi Bala,

      First, add reference to “System.Configuration”. Right click on References in the Solution Explorer and select Add Reference, then select “System.Configuration” and click OK.

      Second, you need to import “System.Configuration” namespace. Write “using System.Configuration;” at top

      Reply
  2. Sarah Said El Sayed

    Hi
    con = new MySqlConnection(“User Id=root;database=olcasheir;password=’dodo1311986′; charset=cp1256;”);
    this my line connection and i have problem Authentication for user root….access denied for user root@localhost mysql using password yes

    how can i see the database file inside my C# application.
    like sql sever database in server solutions?
    Can You help me ?

    Reply
    1. DEEPAK SHARMA

      Hi,

      Please remove single quotes(‘) that you have given around your password. This is causing the authentication error.

      To view your MySQL database in the Server Explorer you can right click on the “Data Connections” in the Server Explorer, click on “Add Connections”, then click on “Change” in front of “Data Source”, then select the “MySQL Database” and click OK, then fill authentication details and click OK to view your database inside the Server Explorer.

      Reply
  3. Abdullah Ansari

    Great to the Point Article…
    Was able to get my job Done in under 15 mins with the help of this article…
    Thanx a lot

    Reply
  4. sriniatha

    Hi Deepak,
    I was doing server based windows application using MYSQL, i want to know how to write connection string. (with server IP)
    Thanks for your help

    Reply
  5. Alexis Steeve Vara

    i really like that you help me with this code, i want to add a datagridview content to mysql database, and i’m trying to do this by putting these code

    string query;

    query = @”INSERT INTO TABLE fulldatamodify (numero_fichier, datetraitement, datetraitementnormal, heuretraitement, org) values (‘” + tbfilenumber.Text + “‘,'” + tbdate.Text + “‘,'” + tbdatefr.Text + “‘,'” + tbheure.Text + “‘,'” + dataGridView1.Rows[i].Cells[0].Value + “‘)”;
    cmd = new OdbcCommand(query, objConn);
    try
    {
    objConn.Open();
    OdbcDataReader dr;
    dr = cmd.ExecuteReader();

    }
    catch (Exception ex)
    {
    MessageBox.Show(“Error”,ex.Message);
    }

    it can’t insert i don’t know why but there is no error message but it didn’t work

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *