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