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]
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:
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:
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:
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.