Sameer Gaikwad CRUD Operations Using Stored Procedure In — mesameergaikwad

Sameer Gaikwad CRUD Operation SQL stored procedure mesameergaikwad

Follow me Sameer Gaikwad then follow the link below! 👉 💯day challenge 📩 Reach out to me on Twitter or Linkedin, or Blogspot if you want to discuss this further. :)

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. private DataTable FetchEmpDetails()
7. {
8. if (sqlCon.State == ConnectionState.Closed)
9. {
10. sqlCon.Open();
11. }
12. DataTable dtData = new DataTable();
13. sqlCmd = new SqlCommand(“spEmployee”, sqlCon);
14. sqlCmd.CommandType = CommandType.StoredProcedure;
15. sqlCmd.Parameters.AddWithValue(“@ActionType”, “FetchData”);
16. SqlDataAdapter sqlSda = new SqlDataAdapter(sqlCmd);
17. sqlSda.Fill(dtData);
18. return dtData;
19. }

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. private DataTable FetchEmpRecords(string empId)
23. {
24. if (sqlCon.State == ConnectionState.Closed)
25. {
26. sqlCon.Open();
27. }
28. DataTable dtData = new DataTable();
29. sqlCmd = new SqlCommand(“spEmployee”, sqlCon);
30. sqlCmd.CommandType = CommandType.StoredProcedure;
31. sqlCmd.Parameters.AddWithValue(“@ActionType”, “FetchRecord”);
32. sqlCmd.Parameters.AddWithValue(“@EmployeeId”, empId);
33. SqlDataAdapter sqlSda = new SqlDataAdapter(sqlCmd);
34. sqlSda.Fill(dtData);
35. return dtData;
36. }

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. private void ClearAllData()
6. {
7. btnSave.Text = “Save”;
8. textBoxEmp.Text = “”;
9. textBoxCity.Text = “”;
10. textBoxDept.Text = “”;
11. comboBoxGen.SelectedIndex = -1;
12. EmployeeId = “”;
13. dgvEmp.AutoGenerateColumns = false;
14. dgvEmp.DataSource = FetchEmpDetails();
15. }

Now, run the application to view the CRUD operations.

Follow me Sameer Gaikwad then follow the link below! Instagram stylish font bio source code formatted tool Sameer Gaikwad by mesameergaikwad

Online MCQ Quiz Questions and Answers Test — mesameergaikwad

#sameergaikwadbymesameergaikwad #sameergaikwadcrudoperation #sameergaikwadmvccrudoperation #sameergaikwadjoinsinsqlserver #sameergaikwadsqlstoredprocedure #sameergaikwadsqltolinq

Thank you for reading, reach out to me on Linkedin for feedback, and suggestions.

sameer gaikwad crud operation Stored Procedure — mesameergaikwad blog

Sameer Gaikwad CRUD Operations Using LINQ to SQL In ASP.NET

Sameer Gaikwad CRUD Operation Using LINQ to SQL CREATE, READ, UPDATE, DELETE ASP.NET

Sameer Gaikwad CRUD Operations Using LINQ To SQL In ASP.NET

Sameer Gaikwad CRUD Operations In ASP.NET MVC Using ADO.NET

Sameer Gaikwad CRUD Operation Using Stored Procedure In ASP.NET