CRUD in Windows Application in C# With Crystal Reports

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.

Next Recommended Readings