Example: ASP. NET Crystal Report tutorial (1)

A report is an indispensable part of an application. A good report can help people intuitively grasp the data and play an important role in decision-making. So what if we can implement reports more quickly and efficiently? This article uses a three-tier ASP. NET Program as an example to describe how to use crystalreport to create a report. It introduces a lot of ASP. NET Crystal Report techniques.

In this example, the application we imagine is to prepare a report for a sales department. The manager can view the sales situation within a certain period of time, shows the sales trend in the form of a list or line chart. We will use SQLServer 2000 as the database, use VB. NET to write the middle layer logic layer, and the front-end presentation layer uses C #.

Before introducing the ASP. NET Crystal Report tutorial, let's take a look at the database structure.

Among them, the tbitem table stores the goods ordered in each order, tbsales stores each order, and tblsalesperson is the salesperson table, which stores each salesman of the publishing house.

Next, use SQLServer 2000 to create these tables. The table structure is as follows:

 
 

  
  
CREATETABLE[dbo].[tblItem](  
  
  
[ItemId][int]NOTNULL,  
  
  
[Description][varchar](50)NOTNULL  
  
  
)ON[PRIMARY]  
  
  
CREATETABLE[dbo].[tblSalesPerson](  
  
  
[SalesPersonId][int]NOTNULL,  
  
  
[UserName][varchar](50)NOTNULL,  
  
  
[Password][varchar](30)NOTNULL  
  
  
)ON[PRIMARY]  
  
  
CREATETABLE[dbo].[tblSales](  
  
  
[SaleId][int]IDENTITY(1,1)NOTNULL,  
  
  
[SalesPersonId][int]NOTNULL,  
  
  
[ItemId][int]NOTNULL,  
  
  
[SaleDate][datetime]NOTNULL,  
  
  
[Amount][int]NOTNULL  
  
  
)ON[PRIMARY] 
 
 

Use the following code to create constraints between tables.

 
 

  
  
ALTERTABLEtblItem  
  
  
ADDCONSTRAINTPK_ItemId  
  
  
PRIMARYKEY(ItemId)  
  
  
GO  
  
  
ALTERTABLEtblSalesPerson  
  
  
ADDCONSTRAINTPK_SalesPersonId  
  
  
PRIMARYKEY(SalesPersonId)  
  
  
GO  
  
  
ALTERTABLEtblSales  
  
  
ADDCONSTRAINTFK_ItemId  
  
  
FOREIGNKEY(ItemId)REFERENCEStblItem(ItemId)  
  
  
GO  
  
  
ALTERTABLEtblSales  
  
  
ADDCONSTRAINTFK_SalesPersonId  
  
  
FOREIGNKEY(SalesPersonId)REFERENCEStblSalesPerson(SalesPersonId)  
  
  
GO 
 
 

Creating an intermediate logic layer in ASP. NET Crystal Report tutorial

In the intermediate logic layer component, we create two classes for each table. For example, for tblItems tables, create item and items classes. The Item Class records the details of each sold Item, while the items table records all sold items and there is a way to add them. In this way, the following six classes exist:

Item and Items
SalesPerson and SalesPersons
Sale and Sales

Next, let's take a look at the attributes in each class:

Item class

Includes the following attributes:
ItemId: item id number)
Description of the product)
Items

There is a way to return an item object based on the item number

PublicFunctionGetAllItems () AsCollections. ArrayList

SalesPerson

This class has the following three attributes:
SalesPersonId salesperson ID)
Name)
Password)

SalesPersons

There is a way to verify whether the salesperson's login is correct in the database based on the username and password entered by the salesperson during login. If yes, zero is returned.

PublicFunctionValidateUser (strUserNameasString, strPasswordasString) AsInteger

Sale

The following five attributes are available:
SaleId
SalesPersonId
ItemId
SaleDate
Amount

Sales

There are two methods. getsales returns the set of sales Objects Based on the input parameters.

PublicFunctionGetSales (OptionalnSaleIdAsInteger = 0, OptionalnSalesPersonIdAsInteger = 0, OptionalnItemIdAsInteger = 0) AsCollections. ArrayList

There is also an addsales Method for adding an order

PublicFunctionAddSale (objSaleAsSale)