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

In this article, we will learn how to do Create, Read, Update and Delete (CRUD) operations using stored procedure.

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 
  1. CREATE TABLE [dbo].[tblEmployee](  
  2.     [EmployeeId] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [varchar](20) NOT NULL,  
  4.     [City] [varchar](20) NOT NULL,  
  5.     [Department] [varchar](20) NOT NULL,  
  6.     [Gender] [varchar](6) NOT NULL  
  7. )  
Stored Procedure
  1. CREATE PROCEDURE spEmployee  
  2. (  
  3.     @EmployeeId INT = NULL,  
  4.     @Name VARCHAR(20) = NULL,  
  5.     @City VARCHAR(20) = NULL,  
  6.     @Department VARCHAR(20) = NULL,  
  7.     @Gender VARCHAR(6) = NULL,  
  8.     @ActionType VARCHAR(25)  
  9. )  
  10. AS  
  11. BEGIN  
  12.     IF @ActionType = 'SaveData'  
  13.     BEGIN  
  14.         IF NOT EXISTS (SELECT * FROM tblEmployee WHERE EmployeeId=@EmployeeId)  
  15.         BEGIN  
  16.             INSERT INTO tblEmployee (Name,City,Department,Gender)  
  17.             VALUES (@Name,@City,@Department,@Gender)  
  18.         END  
  19.         ELSE  
  20.         BEGIN  
  21.             UPDATE tblEmployee SET Name=@Name,City=@City,Department=@Department,  
  22.             Gender=@Gender WHERE EmployeeId=@EmployeeId  
  23.         END  
  24.     END  
  25.     IF @ActionType = 'DeleteData'  
  26.     BEGIN  
  27.         DELETE tblEmployee WHERE EmployeeId=@EmployeeId  
  28.     END  
  29.     IF @ActionType = 'FetchData'  
  30.     BEGIN  
  31.         SELECT EmployeeId AS EmpId,Name,City,Department,Gender FROM tblEmployee  
  32.     END  
  33.     IF @ActionType = 'FetchRecord'  
  34.     BEGIN  
  35.         SELECT EmployeeId AS EmpId,Name,City,Department,Gender FROM tblEmployee   
  36.         WHERE EmployeeId=@EmployeeId  
  37.     END  
  38. 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 Using Stored Procedure In Winforms For Beginner In C#
 

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. 
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. 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.
  1. 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. 
  1. public FormEmployee()  
  2. {  
  3.     InitializeComponent();  
  4.     sqlCon = new SqlConnection(conString);  
  5.     sqlCon.Open();  
  6. }  
And also, declare these at class label.
  1. SqlConnection sqlCon;  
  2. SqlCommand sqlCmd;  
  3. 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. 
  1. private void FormEmployee_Load(object sender, EventArgs e)  
  2. {  
  3.     dgvEmp.AutoGenerateColumns = false// dgvEmp is DataGridView name  
  4.     dgvEmp.DataSource = FetchEmpDetails();  
  5. }  
  6.   
  7. private DataTable FetchEmpDetails()  
  8. {  
  9.     if (sqlCon.State == ConnectionState.Closed)  
  10.     {  
  11.         sqlCon.Open();  
  12.     }  
  13.     DataTable dtData = new DataTable();              
  14.     sqlCmd = new SqlCommand("spEmployee", sqlCon);  
  15.     sqlCmd.CommandType = CommandType.StoredProcedure;  
  16.     sqlCmd.Parameters.AddWithValue("@ActionType""FetchData");  
  17.     SqlDataAdapter sqlSda = new SqlDataAdapter(sqlCmd);  
  18.     sqlSda.Fill(dtData);  
  19.     return dtData;  
  20. }  

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. 
  1. private void btnSave_Click(object sender, EventArgs e)  
  2. {  
  3.     if (string.IsNullOrWhiteSpace(textBoxEmp.Text))  
  4.     {  
  5.         MessageBox.Show("Enter Employee Name !!!");  
  6.         textBoxEmp.Select();  
  7.     }  
  8.     else if (string.IsNullOrWhiteSpace(textBoxCity.Text))  
  9.     {  
  10.         MessageBox.Show("Enter Current City !!!");  
  11.         textBoxCity.Select();  
  12.     }  
  13.     else if (string.IsNullOrWhiteSpace(textBoxDept.Text))  
  14.     {  
  15.         MessageBox.Show("Enter Department !!!");  
  16.         textBoxDept.Select();  
  17.     }  
  18.     else if (comboBoxGen.SelectedIndex <= -1)  
  19.     {  
  20.         MessageBox.Show("Select Gender !!!");  
  21.         comboBoxGen.Select();  
  22.     }  
  23.     else  
  24.     {  
  25.         try  
  26.         {                      
  27.             if (sqlCon.State == ConnectionState.Closed)  
  28.             {  
  29.                 sqlCon.Open();  
  30.             }  
  31.             DataTable dtData = new DataTable();  
  32.             sqlCmd = new SqlCommand("spEmployee", sqlCon);  
  33.             sqlCmd.CommandType = CommandType.StoredProcedure;  
  34.             sqlCmd.Parameters.AddWithValue("@ActionType""SaveData");  
  35.             sqlCmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);  
  36.             sqlCmd.Parameters.AddWithValue("@Name", textBoxEmp.Text);  
  37.             sqlCmd.Parameters.AddWithValue("@City", textBoxCity.Text);  
  38.             sqlCmd.Parameters.AddWithValue("@Department", textBoxDept.Text);  
  39.             sqlCmd.Parameters.AddWithValue("@Gender", comboBoxGen.Text);  
  40.             int numRes = sqlCmd.ExecuteNonQuery();  
  41.             if (numRes > 0)  
  42.             {  
  43.                 MessageBox.Show("Record Saved Successfully !!!");  
  44.                 ClearAllData();  
  45.             }  
  46.             else  
  47.                 MessageBox.Show("Please Try Again !!!");  
  48.         }  
  49.         catch (Exception ex)  
  50.         {  
  51.             MessageBox.Show("Error:- " + ex.Message);  
  52.         }  
  53.     }  
  54. }  

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. 
  1. private void dgvEmp_CellDoubleClick(object sender, DataGridViewCellEventArgs e)  
  2. {  
  3.     if (e.RowIndex >= 0)  
  4.     {  
  5.         btnSave.Text = "Update";  
  6.         EmployeeId = dgvEmp.Rows[e.RowIndex].Cells[0].Value.ToString();  
  7.         DataTable dtData = FetchEmpRecords(EmployeeId);  
  8.         if (dtData.Rows.Count > 0)  
  9.         {  
  10.             EmployeeId = dtData.Rows[0][0].ToString();  
  11.             textBoxEmp.Text = dtData.Rows[0][1].ToString();  
  12.             textBoxCity.Text = dtData.Rows[0][2].ToString();  
  13.             textBoxDept.Text = dtData.Rows[0][3].ToString();  
  14.             comboBoxGen.Text = dtData.Rows[0][4].ToString();  
  15.         }  
  16.         else  
  17.         {  
  18.             ClearAllData(); // For clear all control and refresh DataGridView data.  
  19.         }  
  20.     }  
  21. }  
  22.   
  23. private DataTable FetchEmpRecords(string empId)  
  24. {  
  25.     if (sqlCon.State == ConnectionState.Closed)  
  26.     {  
  27.         sqlCon.Open();  
  28.     }  
  29.     DataTable dtData = new DataTable();  
  30.     sqlCmd = new SqlCommand("spEmployee", sqlCon);  
  31.     sqlCmd.CommandType = CommandType.StoredProcedure;  
  32.     sqlCmd.Parameters.AddWithValue("@ActionType""FetchRecord");  
  33.     sqlCmd.Parameters.AddWithValue("@EmployeeId", empId);  
  34.     SqlDataAdapter sqlSda = new SqlDataAdapter(sqlCmd);  
  35.     sqlSda.Fill(dtData);  
  36.     return dtData;  
  37. }  

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. 
  1. private void btnDelete_Click(object sender, EventArgs e)  
  2. {  
  3.     if (!string.IsNullOrEmpty(EmployeeId))  
  4.     {  
  5.         try  
  6.         {  
  7.             if (sqlCon.State == ConnectionState.Closed)  
  8.             {  
  9.                 sqlCon.Open();  
  10.             }  
  11.             DataTable dtData = new DataTable();  
  12.             sqlCmd = new SqlCommand("spEmployee", sqlCon);  
  13.             sqlCmd.CommandType = CommandType.StoredProcedure;  
  14.             sqlCmd.Parameters.AddWithValue("@ActionType""DeleteData");  
  15.             sqlCmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);  
  16.             int numRes = sqlCmd.ExecuteNonQuery();  
  17.             if (numRes > 0)  
  18.             {  
  19.                 MessageBox.Show("Record Deleted Successfully !!!");  
  20.                 ClearAllData();  
  21.             }  
  22.             else  
  23.             {  
  24.                 MessageBox.Show("Please Try Again !!!");  
  25.             }  
  26.         }  
  27.         catch (Exception ex)  
  28.         {  
  29.             MessageBox.Show("Error:- " + ex.Message);  
  30.         }  
  31.     }  
  32.     else  
  33.     {  
  34.         MessageBox.Show("Please Select A Record !!!");  
  35.     }  
  36. }  

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. 
  1. private void btnClear_Click(object sender, EventArgs e)  
  2. {  
  3.     ClearAllData();  
  4. }  
  5.   
  6. private void ClearAllData()  
  7. {  
  8.     btnSave.Text = "Save";  
  9.     textBoxEmp.Text = "";  
  10.     textBoxCity.Text = "";  
  11.     textBoxDept.Text = "";  
  12.     comboBoxGen.SelectedIndex = -1;  
  13.     EmployeeId = "";  
  14.     dgvEmp.AutoGenerateColumns = false;  
  15.     dgvEmp.DataSource = FetchEmpDetails();  
  16. }  
Now, run the application to view the CRUD operations.
 
I hope this is useful for all readers. Happy Coding!