Alex9409
Cloud Infrastructure Coder
Divine
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
300 XP
In this tutorial, I will teach you how to create a CRUD application using C#. This simple application will help you how to organize your codes and make it into group of functions that perform a task together. See the procedure below.
Creating Database
Create a database named it “dbpeople”
Execute the following query for creating table.
Creating Application
Step 1
Open Microsoft Visual Studio 2015 and create a new windows form application.
Step 2
Do the form just like shown below.
Step 3
Open the code view and add a name space to access MySQL Libraries.
Step 4
Write these codes for the connection between C# and MySQL Database. After that, declare and initialize all the variables and classes that are needed.
Step 5
Create a method for retrieving data in the database.
Step 6
Create a method for saving data in the database.
Step 7
Create a method for updating data in the database.
Step 8
Create a method for deleting data in the database.
Step 9
These codes are for saving data in the database when the button is clicked.
Step 10
These codes are for retrieving data in the database when the button is clicked.
Step 11
These codes are for updating data in the database when the button is clicked.
Step 12
These codes are for deleting data in the database when the button is clicked.
Step 13
These codes are for passing data from datagridview to the textboxes when the row of the datagridview is clicked
Note: The database file is included inside the folder.
The complete source code is included. You can download it and run it on your computer
For any questions about this article. You can contact me @
Email – [email protected]
Mobile No. – 09305235027 – TNT
Or feel free to comment below.
Download
Creating Database
Create a database named it “dbpeople”
Execute the following query for creating table.
- CREATE
TABLE
`dbpeople`
.`tblperson`
(
`PersonID`
INT
NOT
NULL
AUTO_INCREMENT
,
`FNAME`
VARCHAR
(
90
)
NOT
NULL
,
`LNAME`
VARCHAR
(
90
)
NOT
NULL
,
`ADDRESS`
VARCHAR
(
255
)
NOT
NULL
,
PRIMARY KEY
(
`PersonID`
)
)
ENGINE
=
InnoDB
;
Creating Application
Step 1
Open Microsoft Visual Studio 2015 and create a new windows form application.
Step 2
Do the form just like shown below.
Step 3
Open the code view and add a name space to access MySQL Libraries.
- using
MySql.Data.MySqlClient
;
Step 4
Write these codes for the connection between C# and MySQL Database. After that, declare and initialize all the variables and classes that are needed.
- MySqlConnection con =
new
MySqlConnection(
"server=localhost;user id=root;password=;database=dbpeople;sslMode=none"
)
;
- MySqlCommand cmd;
- MySqlDataAdapter da;
- DataTable dt;
- string
sql;
- string
peopleid;
- int
result;
Step 5
Create a method for retrieving data in the database.
- private
void
loadData(
)
- {
- try
- {
- sql =
"SELECT PersonID, `FNAME` as 'Firstname', `LNAME` as 'Lastname', `ADDRESS` as 'Address' FROM `tblperson`"
;
- con.
Open
(
)
;
- cmd =
new
MySqlCommand(
)
;
- cmd.
Connection
=
con;
- cmd.
CommandText
=
sql;
- da =
new
MySqlDataAdapter(
)
;
- da.
SelectCommand
=
cmd;
- dt =
new
DataTable(
)
;
- da.
Fill
(
dt)
;
- dtg_list.
DataSource
=
dt;
- txt_address.
Clear
(
)
;
- txt_fname.
Clear
(
)
;
- txt_lname.
Clear
(
)
;
- btn_delete.
Enabled
=
false
;
- btn_update.
Enabled
=
false
;
- btn_save.
Enabled
=
true
;
- }
catch
(
Exception ex)
- {
- MessageBox.
Show
(
ex.
Message
)
;
- }
- finally
- {
- con.
Close
(
)
;
- da.
Dispose
(
)
;
- }
- }
Step 6
Create a method for saving data in the database.
- private
void
saveData(
)
- {
- try
- {
- sql =
"INSERT INTO `tblperson` (`FNAME`, `LNAME`, `ADDRESS`) VALUES ('"
+
txt_fname.
Text
+
"','"
+
txt_lname .
Text
+
"','"
+
txt_address .
Text
+
"')"
;
- con.
Open
(
)
;
- cmd =
new
MySqlCommand(
)
;
- cmd.
Connection
=
con;
- cmd.
CommandText
=
sql;
- result =
cmd.
ExecuteNonQuery
(
)
;
- if
(
result >
0
)
- {
- MessageBox.
Show
(
"Data has been saved in the database."
, "Save"
)
;
- }
- else
- {
- MessageBox.
Show
(
"Failed to execute the query"
, "error"
)
;
- }
- }
- catch
(
Exception ex)
- {
- MessageBox.
Show
(
ex.
Message
)
;
- }
- finally
- {
- con.
Close
(
)
;
- }
- }
Step 7
Create a method for updating data in the database.
- private
void
updateData(
)
- {
- try
- {
- sql =
"UPDATE `tblperson` SET `FNAME`='"
+
txt_fname.
Text
+
"', `LNAME`='"
+
txt_lname.
Text
+
"', `ADDRESS` ='"
+
txt_address.
Text
+
"' WHERE PersonID="
+
peopleid;
- con.
Open
(
)
;
- cmd =
new
MySqlCommand(
)
;
- cmd.
Connection
=
con;
- cmd.
CommandText
=
sql;
- result=
cmd.
ExecuteNonQuery
(
)
;
- if
(
result >
0
)
- {
- MessageBox.
Show
(
"Data has been updated in the database."
, "Update"
)
;
- }
- else
- {
- MessageBox.
Show
(
"Failed to execute the query"
, "error"
)
;
- }
- }
- catch
(
Exception ex)
- {
- MessageBox.
Show
(
ex.
Message
)
;
- }
- finally
- {
- con.
Close
(
)
;
- }
- }
Step 8
Create a method for deleting data in the database.
- private
void
deleteData(
)
- {
- try
- {
- sql =
"DELETE FROM `tblperson` WHERE PersonID="
+
peopleid;
- con.
Open
(
)
;
- cmd =
new
MySqlCommand(
)
;
- cmd.
Connection
=
con;
- cmd.
CommandText
=
sql;
- result=
cmd.
ExecuteNonQuery
(
)
;
- if
(
result >
0
)
- {
- MessageBox.
Show
(
"Data has been deleted in the database."
, "Delete"
)
;
- }
- else
- {
- MessageBox.
Show
(
"Failed to execute the query"
, "error"
)
;
- }
- }
- catch
(
Exception ex)
- {
- MessageBox.
Show
(
ex.
Message
)
;
- }
- finally
- {
- con.
Close
(
)
;
- }
- }
Step 9
These codes are for saving data in the database when the button is clicked.
- private
void
btn_save_Click(
object
sender, EventArgs e)
- {
- saveData(
)
;
- loadData(
)
;
- }
Step 10
These codes are for retrieving data in the database when the button is clicked.
- private
void
btn_retrieve_Click(
object
sender, EventArgs e)
- {
- loadData(
)
;
- }
Step 11
These codes are for updating data in the database when the button is clicked.
- private
void
btn_update_Click(
object
sender, EventArgs e)
- {
- updateData(
)
;
- loadData(
)
;
- }
Step 12
These codes are for deleting data in the database when the button is clicked.
- private
void
btn_delete_Click(
object
sender, EventArgs e)
- {
- deleteData(
)
;
- loadData(
)
;
- }
Step 13
These codes are for passing data from datagridview to the textboxes when the row of the datagridview is clicked
- private
void
dtg_list_Click(
object
sender, EventArgs e)
- {
- peopleid =
dtg_list.
CurrentRow
.
Cells
[
0
]
.
Value
.
ToString
(
)
;
- txt_fname.
Text
=
dtg_list.
CurrentRow
.
Cells
[
1
]
.
Value
.
ToString
(
)
;
- txt_lname .
Text
=
dtg_list.
CurrentRow
.
Cells
[
2
]
.
Value
.
ToString
(
)
;
- txt_address .
Text
=
dtg_list.
CurrentRow
.
Cells
[
3
]
.
Value
.
ToString
(
)
;
- btn_delete.
Enabled
=
true
;
- btn_update.
Enabled
=
true
;
- btn_save.
Enabled
=
false
;
- }
Note: The database file is included inside the folder.
The complete source code is included. You can download it and run it on your computer
For any questions about this article. You can contact me @
Email – [email protected]
Mobile No. – 09305235027 – TNT
Or feel free to comment below.
Download
You must upgrade your account or reply in the thread to view hidden text.