Skip to main content

How to Create CRUD Application in C#


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.

  1. 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.
psFORMCRUD

Step 2

Do the form just like shown below.
CRUDFORM2

Step 3

Open the code view and add a name space to access MySQL Libraries.

  1. 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.

  1.  
  2. MySqlConnection con = new MySqlConnection("server=localhost;user id=root;password=;database=dbpeople;sslMode=none");
  3. MySqlCommand cmd;
  4. MySqlDataAdapter da;
  5. DataTable dt;
  6. string sql;
  7. string peopleid;
  8. int result;

Step 5

Create a method for retrieving data in the database.

  1. private void loadData()
  2. {
  3. try
  4. {
  5. sql = "SELECT PersonID, `FNAME` as 'Firstname', `LNAME` as 'Lastname', `ADDRESS` as 'Address' FROM `tblperson`";
  6. con.Open();
  7. cmd = new MySqlCommand();
  8. cmd.Connection = con;
  9. cmd.CommandText = sql;
  10. da = new MySqlDataAdapter();
  11. da.SelectCommand = cmd;
  12. dt = new DataTable();
  13. da.Fill(dt);
  14. dtg_list.DataSource = dt;
  15.  
  16.  
  17. txt_address.Clear();
  18. txt_fname.Clear();
  19. txt_lname.Clear();
  20.  
  21. btn_delete.Enabled = false;
  22. btn_update.Enabled = false;
  23. btn_save.Enabled = true;
  24.  
  25.  
  26.  
  27. } catch (Exception ex)
  28. {
  29. MessageBox.Show(ex.Message);
  30. }
  31. finally
  32. {
  33. con.Close();
  34. da.Dispose();
  35. }
  36.  
  37. }

Step 6

Create a method for saving data in the database.

  1. private void saveData()
  2. {
  3. try
  4. {
  5. sql = "INSERT INTO `tblperson` (`FNAME`, `LNAME`, `ADDRESS`) VALUES ('" + txt_fname.Text + "','" + txt_lname .Text + "','" + txt_address .Text + "')";
  6. con.Open();
  7. cmd = new MySqlCommand();
  8. cmd.Connection = con;
  9. cmd.CommandText = sql;
  10. result = cmd.ExecuteNonQuery();
  11.  
  12. if (result > 0)
  13. {
  14. MessageBox.Show("Data has been saved in the database.", "Save");
  15. }
  16. else
  17. {
  18. MessageBox.Show("Failed to execute the query" , "error");
  19. }
  20.  
  21. }
  22. catch (Exception ex)
  23. {
  24. MessageBox.Show(ex.Message);
  25. }
  26. finally
  27. {
  28. con.Close();
  29. }
  30.  
  31. }

Step 7

Create a method for updating data in the database.

  1. private void updateData()
  2. {
  3. try
  4. {
  5. sql = "UPDATE `tblperson` SET `FNAME`='" + txt_fname.Text + "', `LNAME`='" + txt_lname.Text + "', `ADDRESS` ='" + txt_address.Text + "' WHERE PersonID=" + peopleid;
  6. con.Open();
  7. cmd = new MySqlCommand();
  8. cmd.Connection = con;
  9. cmd.CommandText = sql;
  10. result=cmd.ExecuteNonQuery();
  11. if (result > 0)
  12. {
  13. MessageBox.Show("Data has been updated in the database.", "Update");
  14. }
  15. else
  16. {
  17. MessageBox.Show("Failed to execute the query", "error");
  18. }
  19.  
  20. }
  21. catch (Exception ex)
  22. {
  23. MessageBox.Show(ex.Message);
  24. }
  25. finally
  26. {
  27. con.Close();
  28. }
  29.  
  30. }

Step 8

Create a method for deleting data in the database.

  1. private void deleteData()
  2. {
  3. try
  4. {
  5. sql = "DELETE FROM `tblperson` WHERE PersonID=" + peopleid;
  6. con.Open();
  7. cmd = new MySqlCommand();
  8. cmd.Connection = con;
  9. cmd.CommandText = sql;
  10. result=cmd.ExecuteNonQuery();
  11. if (result > 0)
  12. {
  13. MessageBox.Show("Data has been deleted in the database.", "Delete");
  14. }
  15. else
  16. {
  17. MessageBox.Show("Failed to execute the query", "error");
  18. }
  19.  
  20. }
  21. catch (Exception ex)
  22. {
  23. MessageBox.Show(ex.Message);
  24. }
  25. finally
  26. {
  27. con.Close();
  28. }
  29.  
  30. }

Step 9

These codes are for saving data in the database when the button is clicked.

  1. private void btn_save_Click(object sender, EventArgs e)
  2. {
  3. saveData();
  4. loadData();
  5. }

Step 10

These codes are for retrieving data in the database when the button is clicked.

  1.  
  2. private void btn_retrieve_Click(object sender, EventArgs e)
  3. {
  4. loadData();
  5. }

Step 11

These codes are for updating data in the database when the button is clicked.

  1. private void btn_update_Click(object sender, EventArgs e)
  2. {
  3. updateData();
  4. loadData();
  5. }

Step 12

These codes are for deleting data in the database when the button is clicked.

  1. private void btn_delete_Click(object sender, EventArgs e)
  2. {
  3. deleteData();
  4. loadData();
  5. }

Step 13

These codes are for passing data from datagridview to the textboxes when the row of the datagridview is clicked

  1. private void dtg_list_Click(object sender, EventArgs e)
  2. {
  3. peopleid = dtg_list.CurrentRow.Cells[0].Value.ToString();
  4. txt_fname.Text = dtg_list.CurrentRow.Cells[1].Value.ToString();
  5. txt_lname .Text = dtg_list.CurrentRow.Cells[2].Value.ToString();
  6. txt_address .Text = dtg_list.CurrentRow.Cells[3].Value.ToString();
  7.  
  8. btn_delete.Enabled = true;
  9. btn_update.Enabled = true;
  10. btn_save.Enabled = false;
  11.  
  12. }

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.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.