How to Update data Using C# and SQL Server Database

Submitted by janobe on Thursday, June 30, 2016 - 14:29.

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

/****** Object:  Table [dbo].[tbluser]    Script Date: 06/23/2016 08:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbluser](
        [ID] [INT] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](50) NULL,
        [UNAME] [nvarchar](50) NULL,
        [PASS] [nvarchar](MAX) NULL,
        [UTYPE] [NCHAR](20) NULL,
 CONSTRAINT [PK_tbluser] PRIMARY KEY CLUSTERED 
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) 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.

//initialized all classes
 SqlConnection strcon= new SqlConnection();
 SqlCommand cmd = new SqlCommand();
 SqlDataAdapter da = new SqlDataAdapter();
 DataTable dt = new DataTable();
 
//declare a variable for the query.
 string query;
 int user_id;

Step 6. Create a method to display the data in the DataGridView from the SQL Server database.

private void RetrieveData()
 {
 
 try
 {
 
 //set a query for retrieving data in the database.
 query =  "Select ID, Name, UNAME as 'Username',PASS,UTYPE as 'Type' FROM tbluser";
 
 //initialize new Sql commands
 cmd = new SqlCommand();
 //hold the data to be executed.
 cmd.Connection = strcon;
 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();
 
 }
 }

Step 7. 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#
 strcon.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=userdb;trusted_connection=true;";
 //Call a method for retrieving data in the database to the datagridview
 RetrieveData();
 
 }

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.

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 ();
 cbotype.Text = dataGridView1.CurrentRow.Cells["Type"].FormattedValue.ToString ();
 }
 catch(Exception ex)
 {
 MessageBox.Show(ex.Message);
 
 }
 }

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.

private void btnsave_Click(object sender, EventArgs e)
 {
 try
 {
 //opening connection
 strcon.Open();
 //create an insert query;
 query = "UPDATE tbluser SET NAME='" + txtname.Text + "',UNAME='" + txtuname.Text + "',PASS='" + txtpass.Text + "',UTYPE='" + cboType.Text + "' WHERE ID=" + user_id;
 //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();
 
 user_id =0;
 txtname.Text = "";
 txtuname.Text = "";
 txtpass.Text = "";
 cboType.Text = "Select";
 
 }
 else
 {
 MessageBox.Show("SQL QUERY ERROR");
 }
 //closing connection
 strcon.Close();
 
 }
 catch (Exception ex)//catch exception
 {
 //displaying error message.
 MessageBox.Show(ex.Message);
 }
 }
 

Output:

Add new comment

1040 views