CRUD Operation In C# Windows Application Using Store Procedure

Introduction

 
In this article, we are performing a CRUD operation in a C# Windows Form application using a store procedure. We create a store procedure with different types of operations, Then we call the store procedure in a Windows form application button. I hope you enjoy this article.
 
Step 1
 
Open Visual Studio. Here I am using Visual Studio 2019 and SQL Server Management Studio 2018.
 
Step 2
 
Click on the File menu then hover on the new option. Then click on Project, or you can use the shortcut key Ctrl + Shift +N.
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 3
 
Select Windows Form application and click on the Next button. If you cannot find the Windows Form application, you can use the search box or filter dropdowns.
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 4
 
On the next screen, you need to enter the following details and click on the create button.
  • Project Name - Your project name which is also your solution name.
  • Location - Select the file location where you want to save your project.
  • Framework - Select the .NET Framework version that you want to use. I prefer to use the latest version.
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 5
 
Now your project is created. Now you can see the designer page of your form. Create a design as per your requirement. Here I create the following simple design for a CRUD operation.
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 6
 
Now open your SQL Server Management Studio and create a table as per your requirement. Here I created a table with the following fields. If you don’t want to use SQL Server Management Studio, you can also use Visual Studio server explorer by adding a new database to your project.
 
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 7
 
Now your table is ready and we can create the store procedure for this CRUD operation. Following is the store procedure code.
  1. USE [Tutorials]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[EmployeeCrudOperation]    Script Date: 11/14/2020 6:02:30 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9. -- Author:      <Yogeshkumar Hadiya>   
  10. -- Description: <Perform crud operation on employee table>  
  11. -- =============================================  
  12. ALTER PROCEDURE [dbo].[EmployeeCrudOperation]   
  13.     -- Add the parameters for the stored procedure here  
  14.     @Employeeid int,  
  15.     @EmployeeName nvarchar(50),  
  16.     @EmployeeSalary numeric(18,2),  
  17.     @EmployeeCity nvarchar(20),  
  18.     @OperationType int   
  19.     --================================================  
  20.     --operation types   
  21.     -- 1) Insert  
  22.     -- 2) Update  
  23.     -- 3) Delete  
  24.     -- 4) Select Perticular Record  
  25.     -- 5) Selec All  
  26. AS  
  27. BEGIN  
  28.     -- SET NOCOUNT ON added to prevent extra result sets from  
  29.     -- interfering with SELECT statements.  
  30.     SET NOCOUNT ON;  
  31.       
  32.     --select operation  
  33.     IF @OperationType=1  
  34.     BEGIN  
  35.         INSERT INTO Employee VALUES (@EmployeeName,@EmployeeSalary,@EmployeeCity)  
  36.     END  
  37.     ELSE IF @OperationType=2  
  38.     BEGIN  
  39.         UPDATE Employee SET EmployeeName=@EmployeeName , EmployeeSalary=@EmployeeSalary ,EmployeeCity=@EmployeeCity WHERE EmployeeId=@Employeeid  
  40.     END  
  41.     ELSE IF @OperationType=3  
  42.     BEGIN  
  43.         DELETE FROM Employee WHERE EmployeeId=@Employeeid  
  44.     END  
  45.     ELSE IF @OperationType=4  
  46.     BEGIN  
  47.         SELECT * FROM Employee WHERE EmployeeId=@Employeeid  
  48.     END  
  49.     ELSE   
  50.     BEGIN  
  51.         SELECT * FROM Employee   
  52.     END  
  53.        
  54. END  
Code Explanation
  • First of all, here I declare five following parameters which we will pass from the C# code.

    • Employee Id
      Employee id will be used for select employee, delete an employee, and update employee record

    • Employee Name
      Employee name will be passed in the employee name column in the employee table

    • Employee City
      Employee City will be passed in employee city column in the employee table

    • Employee Salary
      Employee Salary will be passed in the employee salary column in the employee table

    • Operation Type
      Operation Type defines the type of operation which we want to perform. We will user 1 for Insert, 2 For Update, 3 For Delete , 4 for select single record and 5 for select all records.

  • Then we divide code by an if-else condition and perform the task as per the operation type parameter
Step 8
 
Now back to Visual Studio. Open Server Explorer and click on add database button. If you created a database in the project, then right-click on the database name and click on modify.
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 9
 
Enter the Server name, here I used the local server so I just enter local and click on refresh. Select the database that you want to use and click on the advance button.
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 10
 
Now you will see a new popup window, select connection string code. Close all popups.
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 11
 
Now double-click anywhere in your form to generate a Form_Load event, or you can generate it by going to the property window and clicking on the event icon (thunder icon) and select Form_Load event. Replace the following code with your event code and also import System.Data.SqlClient namespace. Here, I disable the update and delete button on a load we will enable that buttons when the user gets a single employee record by clicking on the find employee button.
  1. SqlConnection cn;  
  2. SqlCommand cmd;  
  3. SqlDataAdapter da;  
  4. SqlDataReader dr;  
  5. private void Form1_Load(object sender, EventArgs e)  
  6. {  
  7.     cn = new SqlConnection(@"Data Source=(local);Initial Catalog=Tutorials;Integrated Security=True");  
  8.     cn.Open();  
  9.     //bind data in data grid view  
  10.     GetAllEmployeeRecord();  
  11.   
  12.     //disable delete and update button on load  
  13.     btnUpdate.Enabled = false;  
  14.     btnDelete.Enabled = false;  
  15. }  
Step 12
 
Now we create a method to get all data from the table and set in data grid view. We will use this code many times, so we create a simple method for this. Following is the code to get all records from the table and set it in data grid view.
  1. private void GetAllEmployeeRecord()  
  2. {  
  3.     cmd = new SqlCommand("EmployeeCrudOperation", cn);  
  4.     cmd.CommandType = CommandType.StoredProcedure;  
  5.     cmd.Parameters.AddWithValue("@Employeeid", 0);  
  6.     cmd.Parameters.AddWithValue("@EmployeeName""");  
  7.     cmd.Parameters.AddWithValue("@EmployeeSalary", 0);  
  8.     cmd.Parameters.AddWithValue("@EmployeeCity""");  
  9.     cmd.Parameters.AddWithValue("@OperationType""5");  
  10.     da = new SqlDataAdapter(cmd);  
  11.     DataTable dt = new DataTable();  
  12.     da.Fill(dt);  
  13.     dataGridView1.DataSource = dt;  
  14.   
  15. }  
Code Explanation
  • First, we pass our Store Procedure name and Connection object in the SqlCommand object which we defined at the top of the page.
  • Define command type as Store Procedure
  • Pass all parameters with null and zero value and pass 5 (five) which is the Operation type to get all records from the Store procedure.
  • Initialize Command object to DataAdapter object
  • Create a new DataTable object and pass value from the data adapter object to the data table object by fill method.
  • Set the data table object to data grid view.
Step 13 
 
Now generate a method for saving by double-clicking on a save button and add the following code in the save button click event.
  1. private void Btnsave_Click(object sender, EventArgs e)  
  2. {  
  3.     if (txtempcity.Text != string.Empty  && txtempname.Text != string.Empty && txtempsalary.Text != string.Empty)  
  4.     {  
  5.         cmd = new SqlCommand("EmployeeCrudOperation", cn);  
  6.         cmd.CommandType = CommandType.StoredProcedure;  
  7.         cmd.Parameters.AddWithValue("@Employeeid", 0);  
  8.         cmd.Parameters.AddWithValue("@EmployeeName", txtempname.Text);  
  9.         cmd.Parameters.AddWithValue("@EmployeeSalary", txtempsalary.Text);  
  10.         cmd.Parameters.AddWithValue("@EmployeeCity", txtempcity.Text);  
  11.         cmd.Parameters.AddWithValue("@OperationType""1");  
  12.         cmd.ExecuteNonQuery();  
  13.         MessageBox.Show("Record inserted successfully.""Record Inserted", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  14.         GetAllEmployeeRecord();  
  15.         txtempcity.Text = "";  
  16.         txtempid.Text = "";  
  17.         txtempname.Text = "";  
  18.         txtempsalary.Text = "";  
  19.     }  
  20.     else  
  21.     {  
  22.         MessageBox.Show("Please enter value in all fields""Invalid Data", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  23.     }  
Code Explanation
  • First, we check that a user entered a value in all fields if not then show the message or else proceed.
  • Then same as get all record method pass parameter in store procedure but here we user ExecuteNonQuery method for insert into the table.
  • Then show a success message, next, a call function that we generated to get all data from the table and clear all text boxes.
Output 
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 14
 
Now generate a click event on find employee button to get a single employee record by passing its id and show data in another textbox. Add the following code in find button event.
  1. private void Btnfind_Click(object sender, EventArgs e)  
  2. {  
  3.     if (txtempid.Text != string.Empty)  
  4.     {  
  5.   
  6.         cmd = new SqlCommand("EmployeeCrudOperation", cn);  
  7.         cmd.CommandType = CommandType.StoredProcedure;  
  8.         cmd.Parameters.AddWithValue("@Employeeid", txtempid.Text);  
  9.         cmd.Parameters.AddWithValue("@EmployeeName""");  
  10.         cmd.Parameters.AddWithValue("@EmployeeSalary", 0);  
  11.         cmd.Parameters.AddWithValue("@EmployeeCity""");  
  12.         cmd.Parameters.AddWithValue("@OperationType""4");  
  13.         dr = cmd.ExecuteReader();  
  14.         if (dr.Read())  
  15.         {  
  16.             txtempname.Text = dr["EmployeeName"].ToString();  
  17.             txtempsalary.Text = dr["EmployeeSalary"].ToString();  
  18.             txtempcity.Text = dr["EmployeeCity"].ToString();  
  19.             btnUpdate.Enabled = true;  
  20.             btnDelete.Enabled = true;  
  21.         }  
  22.         else  
  23.         {  
  24.             MessageBox.Show("No record found with this id""No Data Found", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  25.         }  
  26.         dr.Close();  
  27.     }  
  28.     else  
  29.     {  
  30.         MessageBox.Show("Please enter employee id ""Error", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  31.     }  
  32. }  
Code Explanation
  • First of all, check that the user entered an employee ID
  • Then pass the employee ID and operation type parameter. All other parameters are null or zero
  • Then we call the ExecuteReader method of SQL Command and initialize data into SQL Data Reader object
  • Then we check if the data reader has data by the read() method
  • If the data reader has data then put that data in textbox and enable delete and update buttons, otherwise show a message that the employee ID was not found
  • In the last close data reader object.
Output
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 15
 
Now generate a click event on the update button by double-clicking on that and replace it with the following code. The code is the same as the insert code, but here we also check whether the employee ID is available or not.
  1. private void BtnUpdate_Click(object sender, EventArgs e)  
  2. {  
  3.     if (txtempcity.Text != string.Empty && txtempid.Text != string.Empty && txtempname.Text != string.Empty && txtempsalary.Text != string.Empty)  
  4.     {  
  5.         cmd = new SqlCommand("EmployeeCrudOperation", cn);  
  6.         cmd.CommandType = CommandType.StoredProcedure;  
  7.         cmd.Parameters.AddWithValue("@Employeeid", txtempid.Text);  
  8.         cmd.Parameters.AddWithValue("@EmployeeName", txtempname.Text);  
  9.         cmd.Parameters.AddWithValue("@EmployeeSalary", txtempsalary.Text);  
  10.         cmd.Parameters.AddWithValue("@EmployeeCity", txtempcity.Text);  
  11.         cmd.Parameters.AddWithValue("@OperationType""2");  
  12.         cmd.ExecuteNonQuery();  
  13.         MessageBox.Show("Record update successfully.""Record Updated", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  14.         GetAllEmployeeRecord();  
  15.         btnDelete.Enabled = false;  
  16.         btnUpdate.Enabled = false;  
  17.     }  
  18.     else  
  19.     {  
  20.         MessageBox.Show("Please enter value in all fields""Invalid Data", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  21.     }  
  22. }  
Output
 
CRUD Operation In C# Windows Application Using Store Procedure
 
Step 16
 
Now generate a click event on the delete button and replace the following code with that.
  1. private void BtnDelete_Click(object sender, EventArgs e)  
  2. {  
  3.     if (txtempid.Text != string.Empty)  
  4.     {  
  5.         DialogResult dialogResult = MessageBox.Show("Are you sure you want to delete this employee ? ""Delete Employee", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk);  
  6.         if (dialogResult == DialogResult.Yes)  
  7.         {  
  8.   
  9.             cmd = new SqlCommand("EmployeeCrudOperation", cn);  
  10.             cmd.CommandType = CommandType.StoredProcedure;  
  11.             cmd.Parameters.AddWithValue("@Employeeid", txtempid.Text);  
  12.             cmd.Parameters.AddWithValue("@EmployeeName""");  
  13.             cmd.Parameters.AddWithValue("@EmployeeSalary", 0);  
  14.             cmd.Parameters.AddWithValue("@EmployeeCity""");  
  15.             cmd.Parameters.AddWithValue("@OperationType""3");  
  16.             cmd.ExecuteNonQuery();  
  17.             MessageBox.Show("Record deleted successfully.""Record Deleted", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  18.             GetAllEmployeeRecord();  
  19.             txtempcity.Text = "";  
  20.             txtempid.Text = "";  
  21.             txtempname.Text = "";  
  22.             txtempsalary.Text = "";  
  23.             btnDelete.Enabled = false;  
  24.             btnUpdate.Enabled = false;  
  25.         }  
  26.     }  
  27.     else  
  28.     {  
  29.         MessageBox.Show("Please enter employee id""Invalid Data", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  30.     }  
  31. }  
Output
 
CRUD Operation In C# Windows Application Using Store Procedure
 

Conclusion

 
In this article, we performed a CRUD operation with a store procedure. If you have any questions or suggestions about this article, you can comment them below, and if you found this article helpful, please share it with your friends.