CRUD Operations with C# WinForms(.Net Framework)

This is a complete 101 discussion specifically written for my students, but this would be helpful for anyone to get a headstart with the C# WinForms application.

Photo by Bret Kavanaugh on Unsplash

In 2020 Microsoft announced .Net Core for Windows Forms applications (WinForms) which is overwhelming right?. In past, the only option was to use Mono to develop WinForm applications for macOS. Well, the sad truth is, still the only option is to use Mono for macOS and Linux😔.

According to Olia Gavrysh, senior program manager at Microsoft, “ Windows Forms will remain Windows-only platform, so those features won’t be in Visual Studio for Mac”

But folks, don’t lose your hopes there are much more advantages of using .Net Core for the development. Now you might be wondering what the hell this guy is talking about? Isn't .Net Framework and .Net Core the same? The simple answer is No, they are different. Both .Net Framework and .Net Core are structures that applications are built upon(in the technical world we call it framework). Until Microsoft introduced .Net Core, back in 2016 we used the .Net Framework to build our desktop, mobile, and web apps. Thus, you could say .Net Core is a modernized version of .Net Framework. Yes, I agree with you, but it’s a complete rewrite. .Net Core is a cross-platform, open-source, scalable, lighter weight, service-oriented, much faster, and basically, it’s like Tesla Cybertruck. In simple terms more futuristic version with interesting features.

Source: https://dotnet.microsoft.com/download

Prerequisites: Visual Studio(I’m using Visual Studio 2019 version 16.10) needs to be installed. No prior c# experiace needed, but you should be able to create simple UI for our WinForms application by drag and drop. Finally, I prefer to install SQL Server Management Studio(SSMS) to create the database. (Not mandatory. By default Visual Studio comes with an inbuilt SQL server instance.) Now let’s dive right into it.

Let’s Get Our Hands Dirty

This is the first article of my C# series. Here we do the example with the widely known .Net Framework 4.7.2, and hopefully in the next article we can discuss WinForms with .Net Core.

Step 1: Design the UI

First I design a simple UI by using the Windows Form designer tool. I’m' pretty confident you all can make this easily without any support. This is simple drag-and-drop stuff. So, my UI is as follows,

As shown above I designed a simple Satellite tracking form using the WinForms designer tool. Here are the features we are going to implement today,

• In the top left-hand corner, we can search satellite by ID.
• We have Datagridview at the bottom to retrieve the data.
• Basically we collecting SatelliteID(int), SatelliteName(string), Longitude(float), Latitude(float), Elevation(float), and HealthStatus(string - Combobox) from the satellite.
• The clear all button is for clear all the fields.
• The insert button is for insert a new record.
• The update button is for update existing records.
• The delete button is for delete a record.

Step 2: Create a SQL database

In my database, I need two tables. One to store the Combobox attributes and the other is to store the rest of the data. So, I created a database called spacex and two tables, Satellite and Status.

create database spacex;
use spacex;
CREATE TABLE Satellites (
    SatelliteID int NOT NULL IDENTITY,
    SatelliteName varchar(300),
    Longitude float,
    Latitude float,
    Elevation float,
    HealthStatus varchar(300)
);
CREATE TABLE Status(
 ID int NOT NULL IDENTITY,
 HealthStatus varchar(300)
);
INSERT INTO Status(HealthStatus) Values('Good Condition'),('Avarage Condition'),('Poor Condition')

Create a stored procedure for performing CRUD, and 5 operation types are defined,

1. Insert

2. Update

3. Delete

4. Select a particular record

5. Select all

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spacex_pro
@SatelliteID int,
@SatelliteName varchar(300),
@Longitude float,
@Latitude float,
@Elevation float,
@HealthStatus varchar(300),
@OperationType int
AS
BEGIN
SET NOCOUNT ON;
IF @OperationType=1
BEGIN
INSERT INTO Satellites VALUES (@SatelliteName,@Longitude,@Latitude,@Elevation,@HealthStatus)
END
ELSE IF @OperationType=2
BEGIN
UPDATE Satellites SET SatelliteName=@SatelliteName , Longitude=@Longitude ,Latitude=@Latitude, Elevation=@Elevation, HealthStatus=@HealthStatus WHERE SatelliteID=@SatelliteID
END
ELSE IF @OperationType=3
BEGIN
DELETE FROM Satellites WHERE SatelliteID=@SatelliteID
END
ELSE IF @OperationType=4
BEGIN
SELECT * FROM Satellites WHERE SatelliteID=@SatelliteID
END
ELSE
BEGIN
SELECT * FROM Satellites
END
END

Step 3: Connect to the database and perform the operations

Connect to the SQL Server data source, the SQLClient library need to be importedusing System.Data.SQLClient;. But in some instances, we need to install the SQLClient NuGet package in order to use it. You can install the nugget package by,

• Right Click on your project file in the solution panel, and then click on the Manage NuGet Packages option.
• In the NuGet Package Manager window, Select the Browser Tab. Search for System.Data.SqlClient and Press enter
• Select the first option, System.Data.SqlClient by Microsoft Click on the install button

Installing the SQLClient NuGet package

To add the data source, go to the server explorer tab>Right click Data Connections>Add connection>Give your data source name(in my case it’s Microsoft SQL Server)>Copy the server name from your SSMS>Select your authentication type(in my case it’s Windows Authentication)>Select the database name>Click ok. Now Visual studio is connected to your data source. Finally to get the connection string you can click the added data connection and copy the connection string from the properties tab.

Find the connection string

Now we need to move to the Form_Load function(just double-click your form in the Design view) and add the connection string. The connection string is a basic string that specifies information about the data source, in our case SQL Server. Here is my Form_Load function,

cn = new SqlConnection(@"<Your_connecton_string_here>");
cn.Open();
//bind data in data grid view
GetAllSatelliteRecord();
//disable delete and update button on load
btnUpdate.Enabled = false;
btnDelete.Enabled = false;

Finally, we need to choose a connection for our Datagridview and ComboBox. The easiest way is to move to the Windows Form designer tool>Click the small arrow next to the Datagridview>Click choose data source>Click add a project data source>Select the database>Select dataset>Select the connection>select the table you want to display. (Use the same method to select the data source to ComboBox)

ComboBox Tasks selection

Step 4: Find, Save, Update, Delete, and ClearAll button functions

The last step is to write Find, Save, Update, Delete, and ClearAll button functions. Actually, it’s quite easy, we need to double click the button in UI mode and the function will be automatically created for us. The function body is the most crucial part. Here we need to write the actual logic.

Find function body:

Save function body:

Update function body:

Delete function body:

ClearAll function body:

Summary:

In this first article of my C# series, we discussed how to perform simple CRUD operations with the MsSQL database(Using stored procedure). In the future, I hope to discuss Entity Framework to link queries, change tracking, and updates. Most Importantly this is for learning purposes only, for real-world applications I do prefer .Net Core because of its long-term support. If you have any suggestions please feel free to drop a comment below.

c#.zip

Click Here to Download Project Files. Don’t forget to show some ❤️love❤️ with a clap👏👏👏. Thanks for reading!