In this article, I would like to explain CRUD operations in Windows applications using C#. In today's internet world, there are many companies/organizations using Windows applications for their intranet work.
First of all, I created a database InventoryDB and run the following script:
CREATE TABLE [dbo].[Customer](
[CustomerID] [bigint] IDENTITY(1,1) NOT NULL,
[CustomerFirstName] [varchar](50) NULL,
[CustomerLastName] [varchar](50) NULL,
[CustomerAddress1] [varchar](100) NULL,
[CustomerAddress2] [varchar](100) NULL,
[CustomerCity] [varchar](50) NULL,
[CustomerPin] [varchar](20) NULL,
[CustomerMobile1] [bigint] NULL,
[CustomerMobile2] [bigint] NULL,
[CustomerOffPhone] [varchar](20) NULL,
[CustomerWebSite] [varchar](50) NULL,
[CustomerCompanyName] [varchar](100) NULL,
[IsActive] [bit] NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_IsActive] DEFAULT ((1)) FOR [IsActive]
GO
CREATE PROCEDURE [dbo].[Customer_AddUpdate] (@CustomerID BIGINT=0,
@CustomerFirstName VARCHAR(50)=NULL,
@CustomerLastName VARCHAR(50)=NULL,
@CustomerAddress1 VARCHAR(100)=NULL,
@CustomerAddress2 VARCHAR(100)=NULL,
@CustomerCity VARCHAR(50)=NULL,
@CustomerPin VARCHAR(20)=NULL,
@CustomerMobile1 BIGINT=0,
@CustomerMobile2 BIGINT=0,
@CustomerOffPhone VARCHAR(20),
@CustomerWebSite VARCHAR(50)=NULL,
@CustomerCompanyName VARCHAR(100)=NULL,
@Result BIGINT output)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT CustomerID
FROM dbo.Customer (nolock)
WHERE CustomerID = @CustomerID)
BEGIN
UPDATE Customer
SET CustomerFirstName = @CustomerFirstName,
CustomerLastName = @CustomerLastName,
CustomerAddress1 = @CustomerAddress1,
CustomerAddress2 = @CustomerAddress2,
CustomerCity = @CustomerCity,
CustomerPin = @CustomerPin,
CustomerMobile1 = @CustomerMobile1,
CustomerMobile2 = @CustomerMobile2,
CustomerOffPhone = @CustomerOffPhone,
CustomerWebSite = @CustomerWebSite,
CustomerCompanyName = @CustomerCompanyName
WHERE CustomerID = @CustomerID
END
ELSE
BEGIN
INSERT INTO Customer
(CustomerFirstName,
CustomerLastName,
CustomerAddress1,
CustomerAddress2,
CustomerCity,
CustomerPin,
CustomerMobile1,
CustomerMobile2,
CustomerOffPhone,
CustomerWebSite,
CustomerCompanyName)
VALUES ( @CustomerFirstName,
@CustomerLastName,
@CustomerAddress1,
@CustomerAddress2,
@CustomerCity,
@CustomerPin,
@CustomerMobile1,
@CustomerMobile2,
@CustomerOffPhone,
@CustomerWebSite,
@CustomerCompanyName )
END
IF @@ERROR <> 0
BEGIN
SET @Result = 0
END
ELSE
BEGIN
IF @CustomerID = 0
BEGIN
SET @Result = @@IDENTITY
END
ELSE
BEGIN
SET @Result = 1
END
END
SET NOCOUNT OFF;
END
GO
CREATE PROCEDURE [dbo].[Customer_DeleteCustomerByID] (@CustomerId BIGINT,
@Result BIGINT output)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Customer
WHERE CustomerId = @CustomerId
IF @@ERROR <> 0
BEGIN
SET @Result = 0
END
ELSE
BEGIN
SET @Result = 1
END
SET NOCOUNT OFF;
END
GO
CREATE PROCEDURE [dbo].[Customer_GetAll]
AS
BEGIN
SET NOCOUNT ON;
Select
CustomerID
,CustomerFirstName
,CustomerLastName
,CustomerAddress1
,CustomerAddress2
,CustomerCity
,CustomerPin
,CustomerMobile1
,CustomerMobile2
,CustomerOffPhone
,CustomerWebSite
,CustomerCompanyName
FROM dbo.Customer (nolock) order by customerid desc
SET NOCOUNT OFF;
End
GO
CREATE PROCEDURE [dbo].[Customer_GetCustomerById] (@CustomerId BIGINT)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID,
CustomerFirstName,
CustomerLastName,
CustomerAddress1,
CustomerAddress2,
CustomerCity,
CustomerPin,
CustomerMobile1,
CustomerMobile2,
CustomerOffPhone,
CustomerWebSite,
CustomerCompanyName
FROM Customer
WHERE CustomerID = @CustomerId
AND IsActive = 1
SET NOCOUNT OFF;
END
CREATE PROCEDURE [dbo].[Customer_GetCustomerId]
(
@Result bigint output
)
AS
SET NOCOUNT ON;
SET @Result = (Select top 1 CustomerID from Customer where IsActive=1 Order By CustomerID Desc)
Select @Result
return;
SET NOCOUNT OFF;
GO
CREATE PROCEDURE [dbo].[Customer_SearchByNameCity]
(
@searchText varchar(100),
@type int=0
)
AS
BEGIN
SET NOCOUNT ON;
if @type=0
begin
Select * from Customer Where CustomerFirstName like '' + @searchText + '%'
end
if @type=1
begin
Select * from Customer Where CustomerLastName like '' + @searchText + '%'
end
if @type=2
begin
Select * from Customer Where CustomerCity like '' + @searchText + '%'
end
SET NOCOUNT OFF;
END
GO
Then , I created a solution using Visual Studio 2012 as in the following:
![New Project]()
![solution]()
Right-click on the solution and add a new project as in the following:
![added a new project]()
Right-click on the solution and add another project as in the following:
![added class Library]()
![class Library]()
![class Library BAL]()
Now, we have 4 projects in our solution. Build the WindowsFormsEntity class type library project and add a reference of this into the WindowsFormsDAL class type library project. Build the WindowsFormsDAL class and add a reference of the WindowsFormsEntity and WindowsFormsDAL classes into the WindowsFormsBAL project. Add a reference of WindowsFormsEntity, WindowsFormsDAL and WindowsFormsBAL class type library projects to our main Windows application WindowsFormsApp and build the solution.
Add the class Customer.cs in WindowsFormsEntity as in the following:
public class Customer
{
public Int64 CustomerID { get; set; }
public string CustomerFirstName { get; set; }
public string CustomerLastName { get; set; }
public string CustomerAddress1 { get; set; }
public string CustomerAddress2 { get; set; }
public string CustomerCity { get; set; }
public string CustomerPin { get; set; }
public string CustomerMobile1 { get; set; }
public string CustomerMobile2 { get; set; }
public string CustomerOffPhone { get; set; }
public string CustomerWebSite { get; set; }
public string CustomerCompanyName { get; set; }
public Boolean IsActive { get; set; }
}
I added a reference of the Microsoft.Practices.EnterpriseLibrary to the project WindowsFormsDAL to work with the database layer.
![added reference]()
Add a new class EventLog into the project WindowsFormsDAL to log any type of error occuring during the transaction. Also added a static class ConfigurationDAL to get the value of the ConnectionString from the app.config file of the WindowsFormsApp project. In the class CustomerDAL, there are 5 methods added to the add/update/retrieve/delete operations. Similarly, 5 methods are added to the project WindowsFormsBAL.
Added a new Windows form Form1.cs to the project WindowsFormsApp.
![]()
Drag and drop controls like Labels, TextBoxes, Buttons, ComboBox and DataGridView.
![Drag and drop controls]()
Added 2 datasets to work with Crystal Reports.
![Added 2 dataset]()
Create a TableAdapter and create a new connectionstring and the values are stored in the app.config file.
![Create a TableAdapter]()
Click on TableAdapter; the next screen will ask about Choose Your Data Connection as follows.
![Data Connection]()
To create a new connection, click on New Connection and the next screen will come as follows.
![New Connection]()
Enter server name, user name, password and database. Click on Test Connection to test it.
After a successful connection, the next screen will ask Choose a Command Type, Select Use existing procedures.
![Command Type]()
The next screen will be as follows.
![Next screen]()
Click on "Next" to choose the method to generate.
![Choose Method]()
Click on Finish.
![Finish]()
Finally our dataset is created for the report.
![dataset created for report]()
Added a new Crystal Reports report as follows.
![Added a new crystal report]()
Create a new Crystal Reports document as in the following:
![new Crystal Report Document]()
Click on "Next" and verify the username and password to connect with the SQL Server and then the verification screen will look as follows:
![SQL server]()
![Report]()
Click on Finish and the report is ready to display with fields.
![report with fields]()
Add a new form ReportForm.cs to the display report in a separate window.
Download the file and change the app.config as per your requirements.