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)