C# and MySQL ADO.NET with Crystals

13May 2010

---

This tutorial has been developed exclusively for personal use by Syed Nazrul Hassan B.Sc Physics (Hons)
on 13
th
Day of May 2010
This tutorial comprizes of topics like INSERTING,RETRIEVING,DELETING,UPDATING,DATA BINDING
TO
CONTROLS,TRANSACTIONS in MySQL With ADO.NET using Visual C# 2005 a component language of
Visual Studio
2005.
This tutorial has been checked for errors but the author does not take any responsibility of loss of data or
something of kind
which may occur while implementing of following codes.However any suggestion for improving,modifying,the
source code
will be thankfully acknowleged,appreciated and accepted
The software version of front end and backend used are described as below
1-Visual C# 8.0 a component language of Visual Studio 2005 also called as Visul C# 2005
2-MySQL 5.1.41 Community database server
3-MySQL Connector.NET 6.1.3
4-MySQL GUI Tools 5.0 r 17
Visual C# 8.0 or Visual Studio 2005 is a registered software from Microsoft Corporation whose identity should
not be
mislead and MySQL is a registered trademark of MySQL Labs AB which also should not be Mislead
The MySQL or related Products can be downloaded from the MySQL official website www.mysql.com
Download section
which is spread across about 56 locations in 6 continents
Enjoy and have Happy Programming
FORM 1
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
using CrystalDecisions.CrystalReports.Engine;
namespace C_Sharp_ADO.NET_with_MySQL
{
public partial class Form1 : Form
{
MySqlDataAdapter da;
DataSet ds;
private static string connStr = "server=localhost;user=root;database=test;port=3306;password=root;";
MySqlConnection conn = new MySqlConnection(connStr);
public Form1()
{
InitializeComponent();
}
/*ADDING A RECORD TO DATABSE*/
private void button1_Click(object sender, EventArgs e)
{

---

if (textBox3.Text == "") { MessageBox.Show("Enter Name", "C# ADO.NET MySQL",
MessageBoxButtons.OK,
MessageBoxIcon.Information); }
else if (textBox4.Text == "")
{ MessageBox.Show("Enter Age", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Information); }
else if (comboBox1.Text == "")
{ MessageBox.Show("Select Qualification", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Information); }
else
{
MySqlConnection conn = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
string constr;
constr = "server=127.0.0.1;pwd=root;uid=root;database=test;";
try
{
conn = new MySqlConnection(constr);
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO STUD(NAME,AGE,QUALIFICATION) VALUES('" +
textBox3.Text +
"'," + textBox4.Text + ",'" + comboBox1.Text + "')";
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("DATA INSERTED", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Information);
textBox1.Clear(); textBox2.Clear(); textBox3.Clear();
textBox4.Clear(); comboBox1.ResetText();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
/*ADDING ITEMS TO COMBO BOX WHEN THE FORM LOADS AT RUNTIME*/
private void Form1_Load(object sender, EventArgs e)
{
comboBox1.Items.Add("Under Graduate");
comboBox1.Items.Add("Graduate");
comboBox1.Items.Add("Post Graduate");
comboBox1.Items.Add("Doctrate");

}
/*ADDING ITEMS(ENTIRE COLOUMN FROM DATABASE) TO COMBO BOX USING DATA READER
*/
private void Form1_Load(object sender, EventArgs e)
{
MySqlConnection conn1 = new MySqlConnection();
MySqlCommand cmd1 = new MySqlCommand();

---

string constr1 = "server=127.0.0.1;uid=root;pwd=root;database=test;";
conn1 = new MySqlConnection(constr1); try
{
conn1.Open();
cmd1.Connection = conn1; cmd1.CommandText = "SELECT * FROM STUD ";
MySqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{

comboBox1.Items.Add(dr1.GetString(1));
}
conn1.Close();

}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/*VIEW DATABASE RECORD USING DATA READER*/
private void button2_Click(object sender, EventArgs e)
{
textBox3.Clear(); textBox4.Clear(); comboBox1.ResetText();
if (textBox2.Text == "")
{ MessageBox.Show("Enter Roll No", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Question); }
else
{
MySqlConnection conn = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
string constr = "server=127.0.0.1;uid=root;pwd=root;database=test;";
conn = new MySqlConnection(constr);
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM STUD WHERE ROLL=" + textBox2.Text + "";
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
textBox3.Text = dr.GetValue(1).ToString();
textBox4.Text = dr.GetValue(2).ToString();
comboBox1.Text = dr.GetValue(3).ToString();
}
conn.Close();
if (textBox3.Text == "" && textBox4.Text == "" && comboBox1.Text == "")
{
MessageBox.Show("SEARCH NOT FOUND", "C# ADO.NET MySQL",
MessageBoxButtons.OK,
MessageBoxIcon.Asterisk);
textBox2.Clear();
}

---

}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
} }//END ELSE
}
/*VIEW DATABASE RECORD USING DATA ADAPTER AND DATA SET*/
private void button3_Click(object sender, EventArgs e)
{
textBox3.Clear(); textBox4.Clear(); comboBox1.ResetText();
if (textBox2.Text == "")
{ MessageBox.Show("Enter Roll No", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Question); }
else
{
MySqlDataAdapter da;
DataSet ds;
string connStr = "server=localhost;user=root;database=test;port=3306;password=root;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
string sql = "SELECT * FROM STUD WHERE ROLL=" + textBox2.Text + "";
da = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
ds = new DataSet();
da.Fill(ds, "STUD");

textBox3.DataBindings.Clear();
textBox4.DataBindings.Clear();

textBox3.DataBindings.Add(new Binding("Text", ds, "STUD.NAME"));
textBox4.DataBindings.Add(new Binding("Text", ds, "STUD.AGE"));

comboBox1.DataSource=ds.Tables["STUD"];
comboBox1.DisplayMember = "QUALIFICATION";
comboBox1.ValueMember = "QUALIFICATION";
if (textBox3.Text == "" && textBox4.Text == "" && comboBox1.Text == "")
{
MessageBox.Show("SEARCH NOT FOUND", "C# ADO.NET MySQL",
MessageBoxButtons.OK,
MessageBoxIcon.Asterisk);
textBox2.Clear();
}

}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}//END ELSE
}
Note – In the following code the Dataset ,Data Adapter and Connection string has been written in the global

---

section to
enable Adding Deleting Modifying and Selecting of records in Dataset followed by writing the changes to
original Data
Source or Data Base corresponding to changes made in Dat Set by using the Update method of Data Adapter.
Previously the
Fill method of Data Adapter is used to fill the Data Set with Required Table Data to work upon
/*VIEW DATABASE RECORD IN DATA GRID VIEW*/
private void button6_Click(object sender, EventArgs e)
{
try
{
string sql = "SELECT * FROM STUD ";
da = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da); ds = new DataSet();
da.Fill(ds, "STUD");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "STUD";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

}
/*UPDATING DATABASE RECORD*/
private void button4_Click(object sender, EventArgs e)
{
if (textBox2.Text == "")
{ MessageBox.Show("Enter Roll No to Update", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Question); }
else
{
MySqlConnection conn = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
string constr;
constr = "server=127.0.0.1;uid=root;pwd=root;database=test;";
try
{
conn = new MySqlConnection(constr);
conn.Open();
cmd.Connection = conn;
cmd.CommandText = " UPDATE STUD SET NAME='" + textBox3.Text + "',AGE=" +
textBox4.Text +
",QUALIFICATION='" + comboBox1.Text + "'WHERE ROLL=" + textBox2.Text + "";
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("DATABASE UPDATED", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
textBox2.Clear(); textBox3.Clear();
textBox4.Clear(); comboBox1.ResetText();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{

---

MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}//END ELSE
}
/*DELETE DATABASE RECORD*/
private void button5_Click(object sender, EventArgs e)
{
if (textBox2.Text == "")
{ MessageBox.Show("Enter Roll No to Delete", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Question); }
else
{
MySqlConnection conn = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
string constr;
constr = "server=127.0.0.1;uid=root;pwd=root;database=test;"; try
{
conn = new MySqlConnection(constr);
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM STUD WHERE ROLL=" + textBox2.Text + "";
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("RECORD DELETED", "C# ADO.NET MySQL", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
textBox2.Clear(); textBox3.Clear();
textBox4.Clear(); comboBox1.ResetText();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}//END ELSE
}
/*CLEAR THE TEXT BOX AND COMBO BOX*/
private void button9_Click(object sender, EventArgs e)
{
textBox1.Clear(); textBox2.Clear(); textBox3.Clear(); textBox4.Clear();
comboBox1.ResetText();
textBox5.Clear(); textBox6.Clear();
ds.Tables["STUD"].Clear(); /*CLEAR THE DATA SET TO CLEAR GRID VIEW SELECTION*/
}
In the above code we could have written simply ds.Tables.Clear(); but it will clear every table residing within
dataset so to
avoid it we provide the specific name of the table
/*VIEW DATABASE RECORD USING DATA ADAPTER AND DATA SET IN MULTILINE TEXT
BOX*/
private void button7_Click(object sender, EventArgs e)
{
textBox1.Clear();
MySqlDataAdapter da;

---

DataSet ds;
string connStr = "server=localhost;user=root;database=test;port=3306;password=root;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
string sql = "SELECT * FROM STUD ";
da = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
ds = new DataSet();
da.Fill(ds, "STUD");
foreach (DataRow CurrentRow in ds.Tables["STUD"].Rows)
{
foreach (DataColumn CurrentColumn in ds.Tables["STUD"].Columns)
{
textBox1.Text += CurrentRow[CurrentColumn].ToString()
+ '\t' + '\n';
}
textBox1.Text += System.Environment.NewLine;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

}
/*VIEW DATABASE INFORMATION DIRECTLY IN TEXT BOX AFTER RETRIEVING FROM
DATABASE*/
private void button8_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
string constr = "server=127.0.0.1;uid=root;pwd=root;database=test;";
conn = new MySqlConnection(constr);
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT VERSION() ";
object a = cmd.ExecuteScalar();
string c = " MySQL ";
textBox1.Text = c + a.ToString();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

/*UPDATING DATA SET USING DATA ADAPTER AND WRITING CHANGES BACK TO
DATABASE*/
For this following code to work you must declare the dataset in the global section else it would givre an error

---

private void button10_Click(object sender, EventArgs e)
{
da.Update(ds,"STUD");
}
/*NAVIGATING OVER ALL TABLES IN DATASET*/
private void button11_Click(object sender, EventArgs e)
{
textBox1.Clear();
MySqlDataAdapter da;
DataSet ds;
string connStr = "server=localhost;user=root;database=test;port=3306;password=root;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
string sql = "SELECT * FROM STUD ";
da = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
ds = new DataSet();
da.Fill(ds, "STUD");
foreach (DataTable CurrentTable in ds.Tables)
{
foreach (DataRow CurrentRow in CurrentTable.Rows)
{
foreach (DataColumn CurrentColumn in CurrentTable.Columns) {
textBox1.Text += CurrentRow[CurrentColumn].ToString()
+ '\t' + '\n';
}
textBox1.Text += System.Environment.NewLine;
}

}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

}
/*INSERTING RECORD IN MORE THAN ONE TABLE CALLED AS TRANSACTION*/
private void button12_Click(object sender, EventArgs e)
{
string myConnString = "Server=127.0.0.1;Uid=root;Pwd=root;Database=test;";
MySqlConnection myConnection = new MySqlConnection(myConnString);
myConnection.Open();
MySqlCommand myCommand = myConnection.CreateCommand();
MySqlTransaction myTrans;
// Start a local transaction
myTrans = myConnection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try

---

{
myCommand.CommandText = "INSERT INTO STUD(NAME,AGE,QUALIFICATION)
VALUES('" +
textBox3.Text + "'," + textBox4.Text + ",'" + comboBox1.Text + "')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into STUDDETAILS VALUES (" + textBox5.Text + ",'" +
textBox6.Text + "')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
MessageBox.Show("Both records are written to database.");
}
catch (Exception ex)
{
try
{
myTrans.Rollback();
}
catch (MySqlException exp)
{
if (myTrans.Connection != null)
{
MessageBox.Show("An exception of type " + exp.GetType() +
" was encountered while attempting to roll back the transaction.");
}
} MessageBox.Show("An exception of type " + ex.GetType() +
" was encountered while inserting the data.");
MessageBox.Show("Neither record was written to database.");
}
finally
{
myConnection.Close();
textBox1.Clear(); textBox2.Clear();
}

}

}
}
FORM 2
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
using CrystalDecisions.CrystalReports.Engine;
namespace C_Sharp_ADO.NET_with_MySQL
{
public partial class Form2 : Form
{

---