tag:blogger.com,1999:blog-46784968125698889712024-03-19T03:43:33.644-07:00Source libreNisansa Dilushanhttp://www.blogger.com/profile/16474627584324864110noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-4678496812569888971.post-1995661047164783142013-02-03T05:02:00.001-08:002013-12-19T01:02:53.756-08:00Connecting a MySQL table to a DataGridView control in C#<h2>
Introduction</h2>
<div class="MsoNormal" style="text-align: justify;">
<span lang="EN-GB">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? <o:p></o:p></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span lang="EN-GB"><br /></span></div>
<div class="MsoNormal" style="text-align: justify;">
<span lang="EN-GB">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 <a href="http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL">this example</a>
and get some experience in connecting MySQL
to .NET. I am going to build on it. <o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br /></span></div>
<div class="MsoNormal">
<span lang="EN-GB"></span></div>
<h2>
<span lang="EN-GB">Data Grid View</span></h2>
<div class="MsoNormal">
<span lang="EN-GB"><a href="http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.aspx">DataGridView</a>
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.<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-OUGt71rBGEs/UQ5ECx93liI/AAAAAAAAD8I/uN_H974QnAU/s1600/0_DataGrid.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="212" src="http://1.bp.blogspot.com/-OUGt71rBGEs/UQ5ECx93liI/AAAAAAAAD8I/uN_H974QnAU/s320/0_DataGrid.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<a name='more'></a><br />
<h2>
Connecting with MS SQL</h2>
<div class="MsoNormal">
<span lang="EN-GB">Connecting the
DataGridView to MS SQL is a straight forward activity. It is called “Binding
Data” to the control. <a href="http://msdn.microsoft.com/en-us/library/fbk67b6z.aspx">This MSDN article</a>
gives a good explanation on that.<o:p></o:p></span></div>
<h2>
<span lang="EN-GB">Connecting with MySQL</span></h2>
<div class="MsoNormal">
<span lang="EN-GB">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 “<b><i>classicalmusic</i></b>”
and inside the said database I created a table called “<b><i>composers</i></b>”. <o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-m52RoBuzL4Q/UQ5EcNZ52rI/AAAAAAAAD8Q/VLR3F3MBWu4/s1600/1_DB.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="156" src="http://2.bp.blogspot.com/-m52RoBuzL4Q/UQ5EcNZ52rI/AAAAAAAAD8Q/VLR3F3MBWu4/s320/1_DB.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
</div>
<div class="MsoNormal">
<span lang="EN-GB">Next create
a new “WindowsForms” project and add the DataGrid control to the form.<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-62b0_jkDvVo/UQ5EnmzBU-I/AAAAAAAAD8Y/n1k87QXGK4Y/s1600/2_Control.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="277" src="http://1.bp.blogspot.com/-62b0_jkDvVo/UQ5EnmzBU-I/AAAAAAAAD8Y/n1k87QXGK4Y/s320/2_Control.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
</div>
<div class="MsoNormal">
<span lang="EN-GB">To make it
look nicer, set the docking property to “Fill”.<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-DsQPM8aZ0m0/UQ5ExCUfGMI/AAAAAAAAD8g/bcivbXlydW8/s1600/3_docking.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-DsQPM8aZ0m0/UQ5ExCUfGMI/AAAAAAAAD8g/bcivbXlydW8/s1600/3_docking.png" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
</div>
<div class="MsoNormal">
<span lang="EN-GB">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 “<b>MySQL.Data</b>” component. If you haven’t, go back to the <a href="http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL">Codeproject
example</a> and see how it is done.<o:p></o:p></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-q7Xb3vKNi70/UQ5FCFk9BmI/AAAAAAAAD8o/d-6BiD2-hoA/s1600/4_Reference.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://2.bp.blogspot.com/-q7Xb3vKNi70/UQ5FCFk9BmI/AAAAAAAAD8o/d-6BiD2-hoA/s320/4_Reference.png" width="233" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
</div>
<div class="MsoNormal">
<span lang="EN-GB">Import the MySqlClient
at the top of the class.<o:p></o:p></span></div>
<div class="MsoNormal">
<pre class="brush: csharp">using MySql.Data.MySqlClient;
</pre>
</div>
<div class="MsoNormal">
<div style="text-align: justify;">
<span lang="EN-GB">Now let’s create
the global variables to create a connection between the C# project and the
Database. <o:p></o:p></span></div>
<div class="MsoNormal">
<pre class="brush: csharp"> private string server;
private string database;
private string uid;
private string password;
private MySqlConnection connection;
</pre>
</div>
<div class="MsoNormal" style="text-align: justify;">
<span lang="EN-GB"><span lang="EN-GB">Since this is only a test application we will
initialize the connection in the </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Form_Load</span><span lang="EN-GB"> method. And do not forget to create
a user named </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">"username" </span><span lang="EN-GB">with the password </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">"password"<span style="color: #a31515;"> </span></span><span lang="EN-GB">at the </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">“Privileges”</span><span lang="EN-GB"> tab
of your database.</span></span><br />
<div class="MsoNormal">
<pre class="brush: csharp"> 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);
}
</pre>
</div>
With the connection
in place, we need a way to open it. For that, we will use the following method.
(From the Codeproject example)<br />
<div class="MsoNormal">
<pre class="brush: csharp"> //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;
}
}
</pre>
</div>
<br />
<div class="MsoNormal">
<span lang="EN-GB">Similarly,
we need a way to close the database connection. To do that, we will use the
following method. (From the Codeproject example)<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp"> //Close connection
private bool CloseConnection()
{
try
{
connection.Close();
return true;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
</pre>
</div>
<h3>
<span lang="EN-GB">Loading the MySQL table to the DataGridView control</span></h3>
<span lang="EN-GB">Let’s load
the data from the<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"> composers </span>table to the<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"> DataGrid </span>now. Create a <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">mySqlDataAdapter</span> variable to load the data
adapter.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">private MySqlDataAdapter mySqlDataAdapter;
</pre>
</div>
<span lang="EN-GB">We will use
the <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Form_Load</span> method to do the data loading as well. Add the following code chunk under the
previous bit of code that we added to the <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Form_Load</span> method to open the connection
and close it after usage.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp"> if (this.OpenConnection() == true)
{
//Business logic
//close connection
this.CloseConnection();
}
</pre>
</div>
<span lang="EN-GB">First we have
to initialize the <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">mySqlDataAdapter</span>. We will initialize it with a “Select”
query to select all from the <b><i>Composers</i></b> table. <o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">mySqlDataAdapter = new MySqlDataAdapter("select * from composers", connection);
</pre>
</div>
<span lang="EN-GB">Next we
will create a <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">DataSet </span>to load the table which is held by the <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">mySqlDataAdapter</span>.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">DataSet DS = new DataSet();
</pre>
</div>
<span lang="EN-GB">Now “fill”
the <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">DataSet</span> with table connected to the <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">mySqlDataAdapter</span> with the following line of
code.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">mySqlDataAdapter.Fill(DS);
</pre>
</div>
<span lang="EN-GB">Only thing
left now is to assign the prepared data source to the DaraGridView control. <o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">dataGridView1.DataSource = DS.Tables[0];
</pre>
</div>
<span lang="EN-GB">If you did
everything correct, your <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Form_Load</span> method. Should look like
this.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp"> 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();
}
}
</pre>
</div>
<span lang="EN-GB">Run your
project and see how the MySQL table is displayed in the DataGridView. <o:p></o:p></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-OUGt71rBGEs/UQ5ECx93liI/AAAAAAAAD8I/uN_H974QnAU/s1600/0_DataGrid.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="212" src="http://1.bp.blogspot.com/-OUGt71rBGEs/UQ5ECx93liI/AAAAAAAAD8I/uN_H974QnAU/s320/0_DataGrid.png" width="320" /></a></div>
<span lang="EN-GB"><br /></span>
<br />
<h3>
<span lang="EN-GB">Updating
the MySQL table via the DataGridView
control</span></h3>
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.<br />
<div class="MsoNormal">
<span lang="EN-GB"><br /></span></div>
<div class="MsoNormal">
<span lang="EN-GB">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.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB"><br /></span></div>
<div class="MsoNormal">
<span lang="EN-GB">The best
place to do this is the <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">RowValidated</span> 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 <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">RowValidated</span> event.</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjk6unR7mRDGRn1dnH4cyyH2lAYAbnvqQUScjOsKaymoWhjqhu847WADGWU6DaBX6z78YvMwnDx5_ccmNdzsyrkRpcqBQ6RCJHNuHDGao-LE8hyphenhyphenY4846HUgjCFn2GO2JrK-FwQbB_yNn_M/s1600/5_RowValidated_Event.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjk6unR7mRDGRn1dnH4cyyH2lAYAbnvqQUScjOsKaymoWhjqhu847WADGWU6DaBX6z78YvMwnDx5_ccmNdzsyrkRpcqBQ6RCJHNuHDGao-LE8hyphenhyphenY4846HUgjCFn2GO2JrK-FwQbB_yNn_M/s320/5_RowValidated_Event.png" width="174" /></a></div>
<br />
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.<br />
<div class="MsoNormal">
<pre class="brush: csharp">DataTable changes = ((DataTable)dataGridView1.DataSource).GetChanges();</pre>
</div>
<div class="MsoNormal">
<span lang="EN-GB">A </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">RowValidated</span> <span lang="EN-GB">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 </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">NullReferenceException</span><span lang="EN-GB">.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp"> if (changes != null)
{
}
</pre>
</div>
<span lang="EN-GB">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<span lang="EN-GB" style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"> </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">mySqlDataAdapter</span> is the fact that it can generate
the <b>Update</b> or <b>Delete</b> commands based on the <b>Select </b>command
we used to create it with. To do this we need an instance of the <span style="color: #2b91af; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">MySqlCommandBuilder</span> class created with our <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">mySqlDataAdapter</span>
as a parameter.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
</pre>
</div>
<span lang="EN-GB">Now we can
use the instance of the<span lang="EN-GB" style="color: #2b91af; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"> </span><span style="color: #2b91af; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">MySqlCommandBuilder</span> to create an <b>Update</b> command and assign it to our <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">mySqlDataAdapter</span>.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
</pre>
</div>
<span lang="EN-GB">Using the
said update command and the <i>changes </i>DataSet we created earlier, we can
update our MySQL database.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">mySqlDataAdapter.Update(changes);</pre>
</div>
<div class="MsoNormal">
<span lang="EN-GB">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.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp">((DataTable)dataGridView1.DataSource).AcceptChanges();
</pre>
</div>
<span lang="EN-GB">If you did
everything correct, your <span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">RowValidated</span> event handler. Should look
like this.<o:p></o:p></span><br />
<div class="MsoNormal">
<pre class="brush: csharp"> 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();
}
}
</pre>
</div>
<span lang="EN-GB">Done and
done! Run your code and see how the MySQL table gets updated. <o:p></o:p></span><br />
<span lang="EN-GB"><br /></span>
<span lang="EN-GB">And here is
the line you scrolled down for; here are the links to download the <a href="http://www.4shared.com/rar/g172JbsN/DataGridTest.html">sample code</a> and the <a href="http://www.4shared.com/document/OLAXBPqL/classicalmusic.html">MySQLdatabase</a>.<o:p></o:p></span></div>
</div>
</div>
</div>
</div>
</div>
Nisansa Dilushanhttp://www.blogger.com/profile/16474627584324864110noreply@blogger.com20Ambalangoda, Sri Lanka6.232158 80.0571139999999566.1690205 79.976432999999957 6.2952955 80.137794999999954tag:blogger.com,1999:blog-4678496812569888971.post-73821268115153914872013-01-26T00:43:00.001-08:002013-01-26T03:21:05.309-08:00Source libre - Introduction <table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="http://2.bp.blogspot.com/-7Cd1s38XEGU/UQOLUz5ljLI/AAAAAAAAD40/uLlOJ5MIRmc/s1600/open_source_business_software_249.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" src="http://2.bp.blogspot.com/-7Cd1s38XEGU/UQOLUz5ljLI/AAAAAAAAD40/uLlOJ5MIRmc/s1600/open_source_business_software_249.png" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><br /></td></tr>
</tbody></table>
<div style="text-align: justify;">
Since long, people have asked me why I do not write a tech blog. The answer was; “Because I do not have time”. The same reason is behind my beloved <a href="http://satahan.blogspot.com/">classical music</a> blog not getting a new post for a long time. That problem is still there. But I thought it is high time that start writing techy stuff.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
This blog will have small code snippets that I had to come up with to solve minor issues in projects that I am involved in. I will not be writing about complicated things here. Only small solutions for small problems. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Let us see how it goes.
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Oh, I almost forgot; "Source libre" means "open source" in French. Why French? Because French is a cool language. Why open source? Well, let us not talk about it, shall we?<br />
<br />
(Image <a href="http://www.itdonut.co.uk/sites/default/files/open_source_business_software_249.png" style="font-size: 13px; text-align: center;">Sauce</a>)</div>
<br />
<br />Nisansa Dilushanhttp://www.blogger.com/profile/16474627584324864110noreply@blogger.com0