CRUD Operations Using Stored Procedure In Sameer Gaikwad - mesameergaikwad

 Sameer Gaikwad CRUD Operation SQL stored procedure mesameergaikwad

This article was originally published on mesameergaikwad as CRUD Operations Using Stored Procedure In Sameer Gaikwad - mesameergaikwad.

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 “WinFormCRUD”. Delete the default form “Form1” and add a new form named “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. 

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 the 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 the 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 the "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 past employee’s data from the database and shows them 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.