How to Create CRUD Application in C#

Submitted by janobe on Friday, November 30, 2018 - 17:03.

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.

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.

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 – jannopalacios@gmail.com Mobile No. – 09305235027 – TNT Or feel free to comment below.

Add new comment

4052 views