How to Retrieve and Update the Data Using C# and MySQL Database

How to Retrieve and Update the Data Using C# and MySQL Database

In this tutorial, I will teach you how to retrieve and update the data in the database using C#.net and MySQL Database. This an advance method for updating data in the database because I’m going to add the “Edit” button in the Datagridview.

Let’s begin:

• Create a database in the MySQL and name it “dbuser“.
• Create a query for adding table in the database that you have created.

 CREATE TABLE IF NOT EXISTS `tblusers` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `UNAME` varchar(40) NOT NULL,
 `UUSERNAME` varchar(40) NOT NULL,
 `UPASSWORD` varchar(90) NOT NULL,
 `UTYPE` varchar(30) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

• Open Microsoft Visual Studio 2008 and create new Windows Form Application. Do the Form as follows.

• Go to the Solution Explorer, click the “View Code”  to fire the code editor.

• Declare all the classes and variables that are needed.

Note: Add MySQL.Data.dll as your references to access mysql server library.

 //initialize sql connection
 MySqlConnection con = new MySqlConnection();
 //initialize all classes
 MySqlCommand cmd = new MySqlCommand();
 MySqlDataAdapter da = new MySqlDataAdapter();
 DataTable dt = new DataTable();

 //declaring variables
 string query;
 int res;
 int userid;

• Double click the Form and do the following codes for establishing a connection between MySLQ Database and C#.net.

 private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection string 
 con.ConnectionString = "server=localhost;user id=root;password=;Database=dbuser;";
 }

• Go back to the design view, double-click the “Load” button and do the following codes for Retrieving data in the database.

private void btnLoad_Click(object sender, EventArgs e)
 {
 try
 {
 con.Open();
 //create a query for retrieving data in the database.
 query = "SELECT ID,`UNAME` as Name, `UUSERNAME` as Username, `UPASSWORD` as Password, `UTYPE` as Type FROM `tblusers` ";
 //initialize new Sql commands
 cmd = new MySqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;
 //initialize new Sql data adapter
 da = new MySqlDataAdapter();
 //fetching query in the database.
 da.SelectCommand = cmd;
 //initialize new datatable
 dt = new DataTable();
 //refreshes the rows in specified range in the datasource. 
 da.Fill(dt);
//clearing the columns first
 dataGridView1.Columns.Clear();
 DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
 btn.Text = "Edit";
 btn.Name = "Action";
 btn.UseColumnTextForButtonValue = true;
 dataGridView1.DataSource = dt;
//add the button in the datagridview
 dataGridView1.Columns.Add (btn);
 userid = 0;
 }
 catch (Exception ex)
 {
 //catching error
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 con.Close();
 }
 }

• Go back to the design view again, double-click the dataGridview and do the following codes for passing the data in the DataGridView to the TextBoxes.

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
 {
 
 if (dataGridView1.CurrentCell.Value == "Edit")
 {
 userid =(Int32) (dataGridView1.CurrentRow.Cells[0].Value);
 txtName.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
 txtUsername .Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
 txtPassword .Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
 cboType .Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
 }

 }

• Go back to the design view again, double-click the “Save” button and do the following codes for updating data in the database.

private void btnSave_Click(object sender, EventArgs e)
 {
 try
 {
 con.Open();
 //create a query for updating data in the database.
 query = "Update tblusers SET `UNAME`='" + txtName.Text + "', `UUSERNAME`= '" + txtUsername.Text + 
 "', `UPASSWORD`='" + txtPassword.Text + "', `UTYPE`='" + cboType.Text + "' WHERE ID=" + userid;
 
 //initialize new Sql commands
 cmd = new MySqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;
//execute the data
 res = cmd.ExecuteNonQuery();
//checking the result of an executed command
 if (res > 0)
 {
 MessageBox.Show("User has been updated in the database.");
 userid = 0;
 }
 }
 catch (Exception ex)
 {
 //catching error
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 con.Close();
 }
 }

For all students who need a programmer for your thesis system or anyone who needs a source code in any programming languages. You can contact me @ :
Email – [email protected]
Mobile No. – 09305235027 – TNT

Download Source code : LoadAndUpdateMySQL