Connecting a MySQL table to a DataGridView control in C#

Introduction

Since I started coding in grade 7 or so, I have worked with many programming languages and so far C# remains my favourite. (Java coming behind as a close second.) So when a small program is to be written, I prefer to choose C#. But if that program involves working with databases we are in a bit of a trouble. C# is naturally built to work with Microsoft SQL. But what if we do not want to sacrifice the freedom that we have with MySQL?

Connecting MySQL to .NET is a tricky thing. And it is a precondition to what I am going to write here. I would suggest you to go through this example and get some experience in connecting MySQL to .NET. I am going to build on it. 

Data Grid View

DataGridView is a cool windows forms component that can give you a grid view of your database tables similar to what you see in MS Access.

Connecting with MS SQL

Connecting the DataGridView to MS SQL is a straight forward activity. It is called “Binding Data” to the control. This MSDN article gives a good explanation on that.

Connecting with MySQL

Let’s get down to the business now, shall we? First of all let’s create the database and a table in MySQL. Here I created a database called “classicalmusic” and inside the said database I created a table called “composers”. 

Next create a new “WindowsForms” project and add the DataGrid control to the form.

To make it look nicer, set the docking property to “Fill”.

Okay. That is all that we’d do with the GUI. Let’s go do some coding. But before that, make sure that you have referred the “MySQL.Data” component. If you haven’t, go back to the Codeproject example and see how it is done.

Import the MySqlClient at the top of the class.

using MySql.Data.MySqlClient;

Now let’s create the global variables to create a connection between the C# project and the Database.

        private string server;
        private string database;
        private string uid;
        private string password;
        private MySqlConnection connection;

Since this is only a test application we will initialize the connection in the Form_Load method. And do not forget to create a user named "username" with the password "password" at the “Privileges” tab of your database.

        private void Form1_Load(object sender, EventArgs e)
        {   
            server = "localhost";
            database = "ClassicalMusic";
            uid = "username";
            password = "password";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
            
        }

With the connection in place, we need a way to open it. For that, we will use the following method. (From the Codeproject example)

        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server. Contact administrator");
                        break;
                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                    default:
                        MessageBox.Show(ex.Message);
                        break;
                }
                return false;
            }
        }

Similarly, we need a way to close the database connection. To do that, we will use the following method. (From the Codeproject example)

        //Close connection
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

Loading the MySQL table to the DataGridView control

Let’s load the data from the composers table to the DataGrid now. Create a mySqlDataAdapter variable to load the data adapter.

private MySqlDataAdapter mySqlDataAdapter;

We will use the Form_Load method to do the data loading as well. Add the following code chunk under the previous bit of code that we added to the Form_Load method to open the connection and close it after usage.

            if (this.OpenConnection() == true)
            {
                //Business logic

                //close connection
                this.CloseConnection();
            }

First we have to initialize the mySqlDataAdapter. We will initialize it with a “Select” query to select all from the Composers table.

mySqlDataAdapter = new MySqlDataAdapter("select * from composers", connection);

Next we will create a DataSet to load the table which is held by the mySqlDataAdapter.

DataSet DS = new DataSet(); 

Now “fill” the DataSet with table connected to the mySqlDataAdapter with the following line of code.

mySqlDataAdapter.Fill(DS); 

Only thing left now is to assign the prepared data source to the DaraGridView control.

dataGridView1.DataSource = DS.Tables[0];

If you did everything correct, your Form_Load method. Should look like this.

        private void Form1_Load(object sender, EventArgs e)
        {   
            server = "localhost";
            database = "ClassicalMusic";
            uid = "username";
            password = "password";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);

            if (this.OpenConnection() == true)
            {
                mySqlDataAdapter = new MySqlDataAdapter("select * from composers", connection);
                DataSet DS = new DataSet();
                mySqlDataAdapter.Fill(DS);
                dataGridView1.DataSource = DS.Tables[0];

                //close connection
                this.CloseConnection();
            }
        }

Run your project and see how the MySQL table is displayed in the DataGridView.

Updating the MySQL table via the DataGridView control

You will notice that you can just type in the DataGridView and it will dynamically add rows, delete rows, alter cell contents, etc. But alas when you go and look at the MySQL table or reload your program, you will see that the changes are not reflected in the database. We need to fix this issue.

We have to select the best possible moment to update the MySQL table making that we do not submit half cooked data at the same time ensuring that all the valid changes are reflected in the database without any loss.

The best place to do this is the RowValidated event of the DataGridView control. Go to the Events section of the properties window for the DataGridView control and generate the code chunk for the RowValidated event.

We only have to commit the changes to the MySQL table. Committing all the data including the unchanged data is a waste of resources. So we use the following nifty bit of code to extract only the changes that we did.

DataTable changes = ((DataTable)dataGridView1.DataSource).GetChanges();

A RowValidated event can occur even when there were no changes. In that case the above variable will be null. We have to take precautions to avoid the NullReferenceException.

            if (changes != null)
            {
                
            }  

Remember, when we were loading the table to the DataGridView control we wrote the MySQL query to load the table to memory? One great thing about mySqlDataAdapter is the fact that it can generate the Update or Delete commands based on the Select command we used to create it with. To do this we need an instance of the MySqlCommandBuilder class created with our mySqlDataAdapter as a parameter.

MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);

Now we can use the instance of the MySqlCommandBuilder to create an Update command and assign it to our mySqlDataAdapter.

mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();

Using the said update command and the changes DataSet we created earlier, we can update our MySQL database.

mySqlDataAdapter.Update(changes);

Are we done? No! Why? Because we have to tell the DataGridView control that we have committed the changes it had. Otherwise next time when we ask for changes we will be in trouble. This is done by the following line of code.

((DataTable)dataGridView1.DataSource).AcceptChanges();

If you did everything correct, your RowValidated event handler. Should look like this.

        private void dataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)
        {
            DataTable changes = ((DataTable)dataGridView1.DataSource).GetChanges();

            if (changes != null)
            {
                MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
                mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
                mySqlDataAdapter.Update(changes);
                ((DataTable)dataGridView1.DataSource).AcceptChanges(); 
            }            
        }

Done and done! Run your code and see how the MySQL table gets updated.

And here is the line you scrolled down for; here are the links to download the sample code and the MySQLdatabase.

Posted in: .NET, C#, code, DataBase, DataGridView, DataSource, DataTable, events, Microsoft SQL, MySQL, MySqlCommandBuilder, mySqlDataAdapter, open source