How to Create a Windows Forms CRUD Application in C# Using Stored Procedures

Hello C# enthusiasts! 👋

In the world of software engineering, CRUD (Create, Read, Update, Delete) operations are fundamental for managing data. In this blog post, I will guide you through the process of building a basic Windows Form CRUD application using C#. Before we dive in, make sure you have Visual Studio and Microsoft SQL Server Management Studio installed as prerequisites.

A Windows Form application is designed to run on a computer and won’t operate within a browser, as doing so would classify it as a web application.

In this article, I will guide you through every step of the process, from creating the user interface to implementing the necessary functionality, all aimed at enhancing user interaction 😃.

Let’s start 🤓

1. Setting up the project

To initiate a new project, open the Visual Studio application on your computer and select the “Create New Project” option from the menu.

Next, choose the project type as “Windows Form App (.NET Framework)” and click on the “Next” button located at the bottom of the page.

As the next step, provide a name for your new project, select a location where you’d like to create the project, and then click on the “Create” button to proceed with the configuration.

If you’ve followed the above-mentioned steps correctly, you should now see the output displayed in Visual Studio as follows:

2. Adding controls to the form

The Form Designer is where you begin designing your Windows Forms application. In the Solution Explorer window, you can locate the application solution. The form application, named Form1.cs, will contain all the code for the Windows Forms application. The main program contains the startup code of the application.

It’s time to craft your user interface 🙃.

Navigate to the Toolbox section and drag and drop the required controls to your form. Modify the “Name” property of each control for proper identification. Whenever you need to alter the text on buttons or elsewhere, simply edit the “Text” property in the properties panel. Feel free to design your interface according to your preferences by adjusting the properties of each control.

3. Creating the database

To store and manage our data effectively, we’ll set up a database in Microsoft SQL Server. Open Microsoft SQL Server Management Studio, then right-click on the “Databases” folder and select “New Database”.

Provide an appropriate name for the database and then click the “OK” button.

Now it’s time to create the table. The application aims to create a student registration form. To initiate the creation of the student table, execute the following SQL command in SQL Server Management Studio.

CREATE TABLE StudentTable
(
 Full_name varchar(100)NOT NULL,
 Index_no nchar(10)NOT NULL,
 Aca_year nchar(5) NULL,
 Mobile int NULL,
 Gender varchar(6) NULL,
 DOB date NULL,
 Language varchar(50) NULL,
 PRIMARY KEY (Index_no)
)

You can view the table structure by right-clicking on the database and selecting “Design” as follows,

4. Connecting the database

To establish the connection between the Windows application and the SQL database, follow these steps:

Navigate to Server Explorer (if you can’t locate it, check under the “View” menu in the menu bar) and right-click on the data connection. Then, select “Add New Connection.”

Then, it will open a dialog box as follows. Firstly, choose “Microsoft SQL Server (SQL Client)” as the data source. Next, provide the server name. Then, select the database name from the dropdown menu as “WindowsFormsAppDb” and click the “OK” button.

Once connected to the database, the connection details will be visible. To retrieve the connection string, right-click on the database and select “Properties”. Then, copy the connection string for further reference.

Double-click on the form page and create a SqlConnection. In the connection object, store the connection string we copied previously.

 public partial class StudentReg : Form
    {
        public StudentReg()
        {
            InitializeComponent();
        }

        SqlConnection con = new SqlConnection("Data Source= ;Initial Catalog=WindowsFormAppDB;Integrated Security=True");
    }

5. Creating stored procedures

To create stored procedures for insert, read, update, delete, and search operations execute the following SQL command in the SQL server management studio.

Insert

Create proc SP_Student_Insert
@Full_name varchar(100),
@Index_no nchar(10),
@Aca_year nchar(5),
@Mobile int,
@Gender varchar(6),
@DOB date,
@Language varchar(50)
as 
begin
insert into StudentTable (Full_name, Index_no, Aca_year, Mobile, Gender, DOB, Language) 
values (@Full_name, @Index_no, @Aca_year, @Mobile, @Gender, @DOB, @Language)
end

Read

Create proc SP_Student_View
as 
begin
select * from StudentTable
end

Update

Create proc SP_Student_Update
@Full_name varchar(100),
@Index_no nchar(10),
@Aca_year nchar(5),
@Mobile int,
@Gender varchar(6),
@DOB date,
@Language varchar(50)
as 
begin
Update StudentTable set Full_name = @Full_name, Index_no = @Index_no, Aca_year = @Aca_year, Mobile = @Mobile, Gender =@Gender, DOB = @DOB, Language =@Language
end

Delete

Create proc SP_Student_Delete
@Index_no  nchar(10)
as 
begin
Delete StudentTable where Index_no = @Index_no
end

Search

Create proc SP_Student_Search
@Index_no  nchar(10)
as 
begin
Select * from StudentTable where Index_no = @Index_no
end

6. Implementing CRUD operations

COOl…😎 Now it’s time to implement the CRUD operation of your application.

Insert

Double-click on the insert button and within the function, follow the following steps. First, initialize variables to hold gender and selected languages. Determine the gender by checking the selected radio button, and identify the selected languages by checking the corresponding checkboxes. Then convert the selected languages into a comma-separated string. After that open the database connection and create a SqlCommand object with the stored procedure name (“dbo.SP_Student_Insert”). Next set the CommandType of the command to StoredProcedure and add parameters to the command for each input field. Finally, execute the command using the ExecuteNonQuery method and close the database connection.

Read

The “LoadAllRecords” function is responsible for fetching all student data from the database and displaying them in a DataGridView control within the application. In this function initialize a new instance of the SqlCommand class with the name of the stored procedure. Then create SqlDataAdapter by passing the SqlCommand object and instantiate a DataTable object. Use the fill method to populate the DataTable with the records. Finally set the DataSource property of DataGridView to populate DataTable.

Update

Right-clicking on the update button and within the function write the following code.

Delete

Double-click on the delete button and write the following code under the event handler method. When the user clicks on the delete button The code within the event handler method for the button click should execute. Firstly, establish a connection to the database. Subsequently, prompt the user with a confirmation message box, ensuring their intention to delete the data. If the user confirms, proceed to execute the stored procedure, passing the index number from the textbox as a parameter. Following this, close the database connection and display a success message to the user.

Search

When clicking on the “Search” button this function comes into play. As previous operations open a connection to the database and create an SQL command that calls a stored procedure, passing the index number entered in the search box. This command is set up to retrieve data matching the provided index number.

The “StudentReg” class should resemble the following.

Now you can perform CRUD operations in your Windows Forms application 🤩. The application will allow you to add, view, update, delete, and search student details from the connected database. The DataGridView will display the list of students retrieved from the database.

Conclusion

In conclusion, this article has provided a comprehensive, step-by-step guide for beginners in C# to develop straightforward Windows Form CRUD applications utilizing stored procedures. While there are multiple approaches to accomplish this task, and opportunities abound to refine these code snippets with best practices, the presented method serves as an ideal starting point for beginners. Encouraging experimentation and iteration, I invite readers to engage with the code, improve it, and try for excellence in their coding endeavors. Here’s to continual improvement and mastery in coding! 🤝

Cheers! 🥂

References

Create a Simple CRUD Windows Application In C#

CRUD Operations In Windows Applications Using C#