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.
Step 4. Go to the Solution Explorer, hit the “
View Code” to display the code editor.
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
//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
//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.
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: