Creating Graphical Reports With Crystal Reports in .NET - The Database

(Page 2 of 7 )

Imagine we have a company called "WroxSellers". We need a report on monthly sales of any selected item between two given dates.

First we need to create our database. For the sake of simplicity, we have only three tables. Here are the three tables we need:



All the details about Items are stored in tblItems, and the details of Sales Persons are in tblSalesPersons. Whenever a salesman sells an item, he makes an entry to the system through our site. This activity will be stored in tblSales including the amount and the date.

Run the DBScript.sql code included in the download to create the tables on your own server.

CREATE TABLE [dbo].[tblItem] (
[ItemId] [int] NOT NULL ,
[Description] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[tblSalesPerson] (
[SalesPersonId] [int] NOT NULL ,
[UserName] [varchar] (50) NOT NULL ,
[Password] [varchar] (30) NOT NULL
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[tblSales] (
[SaleId] [int] IDENTITY (1, 1) NOT NULL ,
[SalesPersonId] [int] NOT NULL ,
[ItemId] [int] NOT NULL ,
[SaleDate] [datetime] NOT NULL ,
[Amount] [int] NOT NULL
) ON [PRIMARY]

GO



The code for the key constraints is shown below:

ALTER TABLE tblItem
ADD CONSTRAINT PK_ItemId
PRIMARY KEY (ItemId)
GO

ALTER TABLE tblSalesPerson
ADD CONSTRAINT PK_SalesPersonId
PRIMARY KEY (SalesPersonId)
GO

ALTER TABLE tblSales
ADD CONSTRAINT FK_ItemId
FOREIGN KEY (ItemId) REFERENCES tblItem(ItemId)
GO

ALTER TABLE tblSales
ADD CONSTRAINT FK_SalesPersonId
FOREIGN KEY (SalesPersonId) REFERENCES tblSalesPerson(SalesPersonId)
GO

Then run the script DBData.sql to populate the tables with some sample data.

The Middle Tier Component
Our component is a simple one. We will follow a simple design model, with two objects for every entity. For example, the tblItems table will have two classes, Item and Items. Item will be the record level representation of an item and will store the details of a particular item, while Items will be a table level representation and will contain the manipulation and adding methods.

So, here are our classes, whose objects will be in action.

• Item and Items
• SalesPerson and SalesPersons
• Sale and Sales

As the complete code is included in the attachment, we will just discuss the methods and properties available in these objects.

Item
Item contains two properties:

• ItemId
• Description

Items
This object has a single method. This method will return an Item object depending on the nItemId:

Public Function GetAllItems () As Collections.ArrayList

This method returns a collection of all Item objects.

SalesPerson
This object will have these three properties:

• SalesPersonId
• Name
• Password

SalesPersons
This object will have a single method. It returns the SalesPersonId of the sales person and matches the UserName and Password inputted against that in the database. If there is no match, the function returns zero.

Public Function ValidateUser (strUserName as String, strPassword as String) As Integer

Sale
This object has these five properties:

• SaleId
• SalesPersonId
• ItemId
• SaleDate
• Amount

Sales
This object has two methods. GetSales() returns a collection of Sales objects, depending on the received parameters.

Public Function GetSales (Optional nSaleId As Integer = 0, Optional nSalesPersonId As Integer = 0,
Optional nItemId As Integer = 0) As Collections.ArrayList

In addition to this method, AddSale() adds a new sale entry to the database.

Public Function AddSale (objSale As Sale)

In the download code you will find one more class called dbConnection. This class is used to store the database connection. It only has one method, GetdbConnection(), that is also shared (static) so that there is no need to instantiate this class. The GetdbConnection returns a connection object with connection details from the registry (we can also store such settings in web.config). You need to alter the registry according to your environment - details are in the class file as remarks.

Now add a new empty web project to the solution. Call it WroxSellers.

We need two pages for our site. The first authenticates our sales staff and allows then to enter records of sales, and the other enables us to select report parameters and view reports based on this data.

Next: Creating Web Pages >>
 

More C# Articles
More By Wrox Team

Comments On: Creating Graphical Reports With Crystal Reports in .NET

· Hello i want to download the code for following topic.Creating Graphical Reports...    · best one to know the technical details    · good one for starting..    · How can i download source of this article..    · best is to start lowr level to higher level;   >>> Post your comment now!