How to Update data Using C# and SQL Server Database

In my previous tutorial, I tackled about how to load data in C#.Net and SQL Server Management Studio Express. So, if you have already mastered it? This time, I'm going to teach you how to update in the database in C#.Net and SQL Server Management Studio Express. This process is so simple that you could easily follow. And with this method, you can update the data in the database at a time.

Let’s get started:

Step 1. Create a database and name it “userdb”. Step 2. Do the following query to create a table in the database that you have created. After that, insert a data depending on your desire
  1. /****** Object: Table [dbo].[tbluser] Script Date: 06/23/2016 08:36:33 ******/
  2. SET ANSI_NULLS ON
  3. GO
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6. CREATE TABLE [dbo].[tbluser](
  7. [ID] [INT] IDENTITY(1,1) NOT NULL,
  8. [Name] [nvarchar](50) NULL,
  9. [UNAME] [nvarchar](50) NULL,
  10. [PASS] [nvarchar](MAX) NULL,
  11. [UTYPE] [NCHAR](20) NULL,
  12. CONSTRAINT [PK_tbluser] PRIMARY KEY CLUSTERED
  13. (
  14. [ID] ASC
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  16. ) ON [PRIMARY]
Step 3. Open Microsoft Visual Studio 2008 and create new Windows Form Application. Then, do the Form as shown below. fig1 Step 4. Go to the Solution Explorer, hit the “View Code” to display the code editor. fig2 Step 5. Declare all the classes and variables that are needed.
Note: Put using System.Data.SqlClient; above the namespace to access SQL server library.
  1. //initialized all classes
  2. SqlConnection strcon= new SqlConnection();
  3. SqlCommand cmd = new SqlCommand();
  4. SqlDataAdapter da = new SqlDataAdapter();
  5. DataTable dt = new DataTable();
  6.  
  7. //declare a variable for the query.
  8. string query;
  9. int user_id;
Step 6. Create a method to display the data in the DataGridView from the SQL Server database.
  1. private void RetrieveData()
  2. {
  3.  
  4. try
  5. {
  6.  
  7. //set a query for retrieving data in the database.
  8. query = "Select ID, Name, UNAME as 'Username',PASS,UTYPE as 'Type' FROM tbluser";
  9.  
  10. //initialize new Sql commands
  11. cmd = new SqlCommand();
  12. //hold the data to be executed.
  13. cmd.Connection = strcon;
  14. cmd.CommandText = query;
  15. //initialize new Sql data adapter
  16. da = new SqlDataAdapter();
  17. //fetching query in the database.
  18. da.SelectCommand = cmd;
  19. //initialize new datatable
  20. dt = new DataTable();
  21. //refreshes the rows in specified range in the datasource.
  22. da.Fill(dt);
  23. //set the data that to be display in the datagridview
  24. dataGridView1.DataSource = dt;
  25.  
  26. //Hidding the column pass for the security used
  27. dataGridView1.Columns["PASS"].Visible = false;
  28.  
  29. }
  30. catch (Exception ex)
  31. {
  32. MessageBox.Show(ex.Message);
  33. }
  34. finally
  35. {
  36. da.Dispose();
  37.  
  38. }
  39. }
Step 7. Do the following codes for calling a method and establishing a connection between SQL server and C#.net.
  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. //connection between sql server to c#
  4. strcon.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=userdb;trusted_connection=true;";
  5. //Call a method for retrieving data in the database to the datagridview
  6. RetrieveData();
  7.  
  8. }
Step 8. 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. fig3 Step 9. Set up the following codes for passing the data in the DataGridView to the TextBoxes when double-clicked.
  1. private void dataGridView1_DoubleClick(object sender, EventArgs e)
  2. {
  3. //diplay the specific data from the datagridview to the textbox
  4. try
  5. {
  6. userid = Int32.Parse( dataGridView1.CurrentRow.Cells["Id"].FormattedValue.ToString ());
  7. txtname.Text = dataGridView1.CurrentRow.Cells["Name"].FormattedValue.ToString ();
  8. txtuname.Text = dataGridView1.CurrentRow.Cells["Username"].FormattedValue.ToString ();
  9. txtpass.Text = dataGridView1.CurrentRow.Cells["PASS"].FormattedValue.ToString ();
  10. cbotype.Text = dataGridView1.CurrentRow.Cells["Type"].FormattedValue.ToString ();
  11. }
  12. catch(Exception ex)
  13. {
  14. MessageBox.Show(ex.Message);
  15.  
  16. }
  17. }
Step 10. Go back to the design view, double click the button to fire the click event handler of it and do the following codes for updating data in the database.
  1. private void btnsave_Click(object sender, EventArgs e)
  2. {
  3. try
  4. {
  5. //opening connection
  6. strcon.Open();
  7. //create an insert query;
  8. query = "UPDATE tbluser SET NAME='" + txtname.Text + "',UNAME='" + txtuname.Text + "',PASS='" + txtpass.Text + "',UTYPE='" + cboType.Text + "' WHERE ID=" + user_id;
  9. //it holds the data to be executed.
  10. cmd.Connection = con;
  11. cmd.CommandText = query;
  12. //execute the data.
  13. int result = cmd.ExecuteNonQuery();
  14. //validate the result of the executed query.
  15. if (result > 0)
  16. {
  17. MessageBox.Show("Data has been updated in the SQL database");
  18. //Call a method for retrieving data in the database to the datagridview
  19. Retrieve_Data();
  20.  
  21. user_id =0;
  22. txtname.Text = "";
  23. txtuname.Text = "";
  24. txtpass.Text = "";
  25. cboType.Text = "Select";
  26.  
  27. }
  28. else
  29. {
  30. MessageBox.Show("SQL QUERY ERROR");
  31. }
  32. //closing connection
  33. strcon.Close();
  34.  
  35. }
  36. catch (Exception ex)//catch exception
  37. {
  38. //displaying error message.
  39. MessageBox.Show(ex.Message);
  40. }
  41. }
  42.  

Output:

Output

Add new comment