CRUD Operations Using Stored Procedure In Winforms For Beginner In C#

Introduction

 

In this application, we will learn to perform Create, Read, Update and Delete (CRUD) operations using the stored procedure from the database. If the programmer has a basic knowledge of C# and Visual Studio, then he/she will not face any difficulty during the program execution.

 

First, we have to create a table and for CURD operation, create a stored procedure.

 

Create Table and Stored Procedure

 

Open SQL Server to create a database with any suitable name and then create a table and a stored procedure for the CRUD operations.

 

Table Structure 

CREATE TABLE [dbo].[tblEmployee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](20) NOT NULL,
    [City] [varchar](20) NOT NULL,
    [Department] [varchar](20) NOT NULL,
    [Gender] [varchar](6) NOT NULL
)

Stored Procedure

CREATE PROCEDURE spEmployee
(
    @EmployeeId INT = NULL,
    @Name VARCHAR(20) = NULL,
    @City VARCHAR(20) = NULL,
    @Department VARCHAR(20) = NULL,
    @Gender VARCHAR(6) = NULL,
    @ActionType VARCHAR(25)
)
AS
BEGIN
    IF @ActionType = 'SaveData'
    BEGIN
        IF NOT EXISTS (SELECT * FROM tblEmployee WHERE EmployeeId=@EmployeeId)
        BEGIN
            INSERT INTO tblEmployee (Name,City,Department,Gender)
            VALUES (@Name,@City,@Department,@Gender)
        END
        ELSE
        BEGIN
            UPDATE tblEmployee SET Name=@Name,City=@City,Department=@Department,
            Gender=@Gender WHERE EmployeeId=@EmployeeId
        END
    END
    IF @ActionType = 'DeleteData'
    BEGIN
        DELETE tblEmployee WHERE EmployeeId=@EmployeeId
    END
    IF @ActionType = 'FetchData'
    BEGIN
        SELECT EmployeeId AS EmpId,Name,City,Department,Gender FROM tblEmployee
    END
    IF @ActionType = 'FetchRecord'
    BEGIN
        SELECT EmployeeId AS EmpId,Name,City,Department,Gender FROM tblEmployee
        WHERE EmployeeId=@EmployeeId
    END
END

Create WinFormCRUD Application

 

After successfully creating these, now let us move to the Windows application.

 

Open Visual Studio and create a Windows application named as “WinFormCRUD”. Delete the default form “Form1” and add a new form named as “FormEmployee”. Design the form like the image given below.

 

 

CRUD Operations

 

After designing the Employee form, now, we will do the data save, update, fetch, and delete operations. For that, we need to import the following namespaces. 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

Before going to the CRUD operation, we have to set the connection to the database.

 

For that, we will use the conString variable like below.

string conString = "Data Source=.; Initial Catalog=DemoTest; User Id=sa; Password=password;";

Here, Data Source is your server name, Initial Catalog is your database name, and User Id & Password are your login credentials for login to the SQL Server. Now, initialize the connection inside the page constructor. 

public FormEmployee()
{
    InitializeComponent();
    sqlCon = new SqlConnection(conString);
    sqlCon.Open();
}

And also, declare these at class label.

SqlConnection sqlCon;
SqlCommand sqlCmd;
string EmployeeId = "";

Fetching all records

 

Now, we have to bind data from the database to the DataGridView inside the Page Load event of the page. Because when the page is loaded, all records present in the database will show in DataGridView. By using the FetchEmpDetails() method, we will retrieve the data from the database. 

private void FormEmployee_Load(object sender, EventArgs e)
{
    dgvEmp.AutoGenerateColumns = false; // dgvEmp is DataGridView name
    dgvEmp.DataSource = FetchEmpDetails();
}

private DataTable FetchEmpDetails()
{
    if (sqlCon.State == ConnectionState.Closed)
    {
        sqlCon.Open();
    }
    DataTable dtData = new DataTable();
    sqlCmd = new SqlCommand("spEmployee", sqlCon);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.Parameters.AddWithValue("@ActionType", "FetchData");
    SqlDataAdapter sqlSda = new SqlDataAdapter(sqlCmd);
    sqlSda.Fill(dtData);
    return dtData;
}

Save records to Database

 

When the Save Button is clicked, the btnSave_Click event handler is executed. Here, it will first check whether all fields are empty or not. If any field is empty, then it will show a message to fill that field. After successfully checking the fields, the values are fetched from their respective TextBoxes and then passed to the SQL Stored Procedure for inserting the record into the database. If records are successfully inserted, then it will show the "Record Saved Successfully !!!" message; if not, then it shows "Please Try Again !!!" message. 

private void btnSave_Click(object sender, EventArgs e)
{
    if (string.IsNullOrWhiteSpace(textBoxEmp.Text))
    {
        MessageBox.Show("Enter Employee Name !!!");
        textBoxEmp.Select();
    }
    else if (string.IsNullOrWhiteSpace(textBoxCity.Text))
    {
        MessageBox.Show("Enter Current City !!!");
        textBoxCity.Select();
    }
    else if (string.IsNullOrWhiteSpace(textBoxDept.Text))
    {
        MessageBox.Show("Enter Department !!!");
        textBoxDept.Select();
    }
    else if (comboBoxGen.SelectedIndex <= -1)
    {
        MessageBox.Show("Select Gender !!!");
        comboBoxGen.Select();
    }
    else
    {
        try
        {
            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            DataTable dtData = new DataTable();
            sqlCmd = new SqlCommand("spEmployee", sqlCon);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue("@ActionType", "SaveData");
            sqlCmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
            sqlCmd.Parameters.AddWithValue("@Name", textBoxEmp.Text);
            sqlCmd.Parameters.AddWithValue("@City", textBoxCity.Text);
            sqlCmd.Parameters.AddWithValue("@Department", textBoxDept.Text);
            sqlCmd.Parameters.AddWithValue("@Gender", comboBoxGen.Text);
            int numRes = sqlCmd.ExecuteNonQuery();
            if (numRes > 0)
            {
                MessageBox.Show("Record Saved Successfully !!!");
                ClearAllData();
            }
            else
                MessageBox.Show("Please Try Again !!!");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error:- " + ex.Message);
        }
    }
}

Update records

 

To update a record, we have to double click on a record in DataGridView. When double clicked on a record of DataGridView, the DataGridView’s CellDoubleClick event handler is triggered and it first fetches the EmployeeId of the particular record and calls the FetchEmpRecords methods to fetch the particular employee data where we have to pass the EmployeeId as a parameter. The FetchEmpRecords() method fetches the pass employee’s data from the database and shows in their respective fields. To update that record, click on the Update button and it will update the record. 

private void dgvEmp_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
    if (e.RowIndex >= 0)
    {
        btnSave.Text = "Update";
        EmployeeId = dgvEmp.Rows[e.RowIndex].Cells[0].Value.ToString();
        DataTable dtData = FetchEmpRecords(EmployeeId);
        if (dtData.Rows.Count > 0)
        {
            EmployeeId = dtData.Rows[0][0].ToString();
            textBoxEmp.Text = dtData.Rows[0][1].ToString();
            textBoxCity.Text = dtData.Rows[0][2].ToString();
            textBoxDept.Text = dtData.Rows[0][3].ToString();
            comboBoxGen.Text = dtData.Rows[0][4].ToString();
        }
        else
        {
            ClearAllData(); // For clear all control and refresh DataGridView data.
        }
    }
}

private DataTable FetchEmpRecords(string empId)
{
    if (sqlCon.State == ConnectionState.Closed)
    {
        sqlCon.Open();
    }
    DataTable dtData = new DataTable();
    sqlCmd = new SqlCommand("spEmployee", sqlCon);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.Parameters.AddWithValue("@ActionType", "FetchRecord");
    sqlCmd.Parameters.AddWithValue("@EmployeeId", empId);
    SqlDataAdapter sqlSda = new SqlDataAdapter(sqlCmd);
    sqlSda.Fill(dtData);
    return dtData;
}

Deleting records

 

If we want to delete a record, then double click on that particular record. After retrieving the data, click on the Delete button. When the Delete button is clicked, the btnDelete_Click event handler is triggered. It will first check if EmployeeId is empty or not. If it is not empty, then it calls the Stored Procedure and deletes the record from the database and calls the ClearAllData() method. 

private void btnDelete_Click(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(EmployeeId))
    {
        try
        {
            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            DataTable dtData = new DataTable();
            sqlCmd = new SqlCommand("spEmployee", sqlCon);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue("@ActionType", "DeleteData");
            sqlCmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
            int numRes = sqlCmd.ExecuteNonQuery();
            if (numRes > 0)
            {
                MessageBox.Show("Record Deleted Successfully !!!");
                ClearAllData();
            }
            else
            {
                MessageBox.Show("Please Try Again !!!");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error:- " + ex.Message);
        }
    }
    else
    {
        MessageBox.Show("Please Select A Record !!!");
    }
}

Clear all controls

 

To clear all fields, click on the Clear button. It will clear all fields and rebind the DataGridView again by calling the ClearAllData() method. 

private void btnClear_Click(object sender, EventArgs e)
{
    ClearAllData();
}

private void ClearAllData()
{
    btnSave.Text = "Save";
    textBoxEmp.Text = "";
    textBoxCity.Text = "";
    textBoxDept.Text = "";
    comboBoxGen.SelectedIndex = -1;
    EmployeeId = "";
    dgvEmp.AutoGenerateColumns = false;
    dgvEmp.DataSource = FetchEmpDetails();
}

Now, run the application to view the CRUD operations.

 

I hope this is useful for all readers. Happy Coding!