CRUD Operation in C# Windows Form Application Using Store Procedure

Introduction

In this article we are perform crud operation in c# windows form application using store procedure. We create a store procedure with different types of operations and call that Store procedure in windows form application button click. I hope you will like this article.

Step 1

Open visual studio. Here I am using visual studio 2019 and SQL Server Management Studio 2018.

Step 2

Click on file menu then hover on new option then click on Project or you can also use short cut key Ctrl + Shift +N.

Step 3

Select window form application and click on next button. If you could not found windows form application you can use search box or also use filter dropdowns.

Step 4

In next screen you need to enter following details and click on create button.

• Project Name: Your project name which is also your solution name.
• Location: Select file location where you want to save your project.
• Framework: Select .Net Framework version which you want to use. I prefer use latest version.

Step 5

Now your project is created. Now you can see designer page of your form. Create design as per your requirement. Here I create following simple design for crud operation.

 

Step 6

Now open your SQL Server Management Studio and create table as per your requirement. Here I create table with following fields. If you don’t want to use SQL Server Management Studio you can also user visual studio Server explorer by adding new database in your project.

Step 7:

Now your table is ready and now we create store procedure for this crud operation. Following is the store procedure code.

Code Explanation

• First of all here I declare five following parameters which we will pass from c# code.
• Employee Id: Employee id will be used for select employee, delete employee and update employee record.
• Employee Name; Employee name will be pass in employee name column in employee table
• Employee City; Employee City will be pass in employee city column in employee table
• Employee Salary; Employee Salary will be pass in employee salary column in employee table
• Operation Type :Operation Type define 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 if else condition and perform task as per operation type parameter
  

Step 8

Now back to visual studio and open Server Explorer and click on add database button. If you create database in project then right click on database name and click on modify.

Step 9

Enter Server name, here I used local server so I just enter local and click on refresh. Select your database which you want to use and click on advance button.

Step 10:

Now you will see a new popup window select connection string code and close all popup.

Step 11

Now double click anywhere in your form for generate Form_Load event or you can also generate it by going to property window and click on event icon (thunder icon) and select Form_Load event. Replace following code with your event code and also import System.Data.SqlClient namespace. Here I disable update and delete button on load we will enable that buttons when user get single employee record by click on find employee button.

Step 12

Now we create a method for get all data from table and set in data grid view. We will use this code many time so we create a simple method for this. Following is the code for get all record from table and set in data grid view.

 

Code Explanation

• First we pass our Store Procedure name and Connection object in SqlCommand object which we define top of page.
• Define command type as Store Procedure
• Pass all parameter with null and zero value and pass 5 (five) which is Operation type for get all record from Store procedure.
• Initialize Command object to DataAdapter object
• Create new DataTable object and pass value from data adapter object to data table object by fill method.
• Set data table object to data grid view.

 

Step 13 

Now Generate method for save button click by double click on save button and add following code in save button click event.

OutPut

Code Explanation

• First we check that user enter value in all fields if not then show message else proceed.
• Then same as get all record method pass parameter in store procedure but here we user ExecuteNonQuery method for insert into table.
• Then show message success message then call function which we generate for get all data from table and clear all text box.

Step 14

Now generate click event on find employee button for get single employee record by passing its id and show data in other textbox. Add following code in find button event.

OutPut

Code Explanation

• First of all check that user enter employee id
• Then pass employee id and operation type parameter and all other parameter as null or zero
• Then we call ExecuteReader method of Sql Command and initialize data into SQL Data Reader object
• Then we check if data reader have data by read() method
• If data reader have data then put that data in textbox and enable delete and update buttons otherwise show message that employee id not found
• In last close data reader object.

Step 15:

Now generate click event on update button by double click on that and replace with following code. Code is same as insert code but here we also check that employee id is available or not.

OutPut

Step 16:

Now generate click event on delete button and replace following code with that.

  

OutPut

Conclusion

So in this article we perform crud operation with store procedure. If you have any question or suggestion in this article you can comment it and if you found this article help full share with your friends