Updating Data in C# and SQL Server

In this tutorial, I will teach you how to update data in the database in C#.Net and SQL Server Manangement Studio Express. With this, you can update data in the database that’s in the DataGridView.

Let’s begin:

• Create a database and name it “userdb”. Then, insert data depending on your desire.
• Open Microsoft Visual Studio and create new Windows Form Application.
• Do the Form as follows.

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

• Declare all the classes and variables that are needed.

Note: Put using System.Data.SqlClient; above the namespace to access sql server library.

//initialized all classes
 SqlConnection con = new SqlConnection();
 SqlCommand cmd = new SqlCommand();
 SqlDataAdapter da = new SqlDataAdapter();
 DataTable dt = new DataTable();

//declare a variable for the query.
 string query;
 int userid;

• Create a method for retrieving data in the SQL database to DataGridView.

private void Retrieve_Data()
 {

 try
 {

 //create a query for retrieving data in the database.
 query = "SELECT ID as 'Id',NAME as 'Name',UNAME as 'Username',PASS, UROLE as 'Role' FROM tbluser";
 //initialize new Sql commands
 cmd = new SqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;
 //initialize new Sql data adapter
 da = new SqlDataAdapter();
 //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);
 //set the data that to be display in the datagridview
 dataGridView1.DataSource = dt;

 //Hidding the column pass for the security used
 dataGridView1.Columns["PASS"].Visible = false;

 }
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 
 }
 }

• Go back to the design view, double click the Form and do the following codes for calling a method and establishing a connection between SQL server and C#.net.

private void Form1_Load(object sender, EventArgs e)
 {
 //connection between sql server to c#
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=cruddb;trusted_connection=true;";
 //Call a method for retrieving data in the database to the datagridview
 Retrieve_Data();

 }

• Go back to the design view, click the DataGridView and go to properties. In the properties, select the “Events” just like a lightning symbol and double click the “DoubleClick” event handler.

• Do the following codes for passing the data in the DataGridView to the TextBoxes.

private void dataGridView1_DoubleClick(object sender, EventArgs e)
 {
 //diplay the specific data from the datagridview to the textbox 
 try
 {
 userid = Int32.Parse( dataGridView1.CurrentRow.Cells["Id"].FormattedValue.ToString ());
 txtname.Text = dataGridView1.CurrentRow.Cells["Name"].FormattedValue.ToString ();
 txtuname.Text = dataGridView1.CurrentRow.Cells["Username"].FormattedValue.ToString ();
 txtpass.Text = dataGridView1.CurrentRow.Cells["PASS"].FormattedValue.ToString ();
 cboRole.Text = dataGridView1.CurrentRow.Cells["Role"].FormattedValue.ToString ();
 }
 catch(Exception ex)
 {
 MessageBox.Show(ex.Message);

 }
 }

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

private void btnsave_Click(object sender, EventArgs e)
 {
 try
 {
 //opening connection
 con.Open();
 //create an insert query;
 query = "UPDATE tbluser SET NAME='" + txtname.Text + "',UNAME='" + txtuname.Text + "',PASS='" + txtpass.Text + "',UROLE='" + cboRole.Text + "' WHERE ID=" + userid;
 //it holds the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;
 //execute the data.
 int result = cmd.ExecuteNonQuery();
 //validate the result of the executed query.
 if (result > 0)
 {
 MessageBox.Show("Data has been updated in the SQL database");
 //Call a method for retrieving data in the database to the datagridview
 Retrieve_Data();

 userid =0;
 txtname.Text = "";
 txtuname.Text = "";
 txtpass.Text = "";
 cboRole.Text = "Select";

 }
 else
 {
 MessageBox.Show("SQL QUERY ERROR");
 }
 //closing connection
 con.Close();

 }
 catch (Exception ex)//catch exeption
 {
 //displaying error message.
 MessageBox.Show(ex.Message);
 }
 }

Output:

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

Download Sourcecode