Fd_-Merah
Site Health Auditor
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
300 XP
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
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.
Step 6. Create a method to display the data in the DataGridView from the SQL Server database.
Step 7. Do the following codes for calling a method and establishing a connection between SQL server and C#.net.
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.
Step 10. Go back to the design view, double click the button to fire the click
of it and do the following codes for updating data in the database.
Output:
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
- 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.
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
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: