Sunday, February 3, 2013

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.

18 comments:

I keep getting "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" everytime I make a change and it calls mySqlDataAdapter.Update .... any ideas?

Nice article. Unfortunately, I can't use it because I have no idea why some website named 4shared.com is wanting to download an executable to my computer. Silly. Why put the code in a large text box or provide a link to a text file or ZIP file or anything simple?

How do I hide the first column, that contains table primary key? I don't want to show it in the dgw.

This error occurred highlighting this line: mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();

ERROR:
An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code

Additional information: The DataAdapter.SelectCommand property needs to be initialized.

How would I parameterize the query and update the GridView when I change an input?

This comment has been removed by the author.

An Answer for:
This error occurred highlighting this line: mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();

ERROR:
An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code

Additional information: The DataAdapter.SelectCommand property needs to be initialized.

_____________________________________________

SET PRIMARY KEY FIELD FOR TABLE


Thank you.
But my code occur concurrency exception like above people's comments.
I checked my table in database, the table has field include primary key.
how to solve this problem?

my table property is below

CREATE TABLE tblData
(
ScheduleName VARCHAR(255),
StepNo INT(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
Type VARCHAR(32) NOT NULL ,
Volt FLOAT(11) unsigned NOT NULL ,
Current FLOAT(11) unsigned NOT NULL ,
cutoff_time TIME(3 ),
modified_time TIMESTAMP(2)
);

some data inserted by below query
insert into tbldata values ("my_schedule", NULL, "charge" , 1.23, 45.8, "11:22:33.456" , NOW () );

This comment has been removed by the author.

Instructions to Solve C# MySQL Connection Problem through MySQL Backup Database
At Cognegic we will help you to take care of your C# MySQL association issue and furthermore enable you to recognize the root to cause behind your specialized issues. Here we give MySQL Enterprise Backup support and MySQL Remote Support to recoup from any blackouts. We have quite a while of experience and devoted specialized specialists who resolve to tackle your issue and make you blunder free. Along these lines, don't sit tight for some other help organization essentially contact to our MySQL Remote Service and get the best help.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Get in Touch with MySQL Technical Support for Common Errors When Using MySQL Programs | Cognegic
At whatever point we are working with MySQL there might be odds of confronting some kind specialized blunders or glitches. These sorts of issues forestall you to work with your MySQL. Possibly you don't know that you will discover numerous specialized issues some of them are: "Secret key fizzled when entered intuitively", "Correspondence blunders and prematurely ended associations", "Table defilement issues" and some more. Ensure; in the event that you communicate with these issues than without quite a bit of stretch rapidly connect with Cognegic's MySQL Remote Support and MySQL Remote Service.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Post a Comment