HomeASP.NET Object-Oriented Report Development using C...
Object-Oriented Report Development using Crystal Reports and ASP.NET 2.0
This is the fourth article in a series focusing on programming with Crystal Reports and ASP.NET 2.0. In this article, we will focus on implementing an object-oriented approach for creating and developing Crystal Reports using ASP.NET 2.0 and Visual Basic 2005.
The entire solution (source code) for this article is available as a free download (in the form of a zip). The source code in this article has been developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows XP Professional Edition with Microsoft SQL Server 2005 Express Edition. I used the same version of Crystal Reports which comes with Visual Studio 2005 Professional Edition. I didn't really test any of the code in any other tools/IDEs/servers/editions/versions. If you have any problems, please feel free to post in the discussion area.
The approach for mixing business logic-based OOP with Crystal Reports
Almost every .NET application is object oriented. We develop several classes involving different OOPS concepts such as inheritance, polymorphism, and so on to make applications more scalable, readable and easier to maintain. Most of these classes fall into either business logic or data access layers.
Furthermore, we even implement a few types of patterns suggested by experts to solve the most common integration problems. The best example of this is Microsoft Enterprise Library. The pattern-oriented library is divided into several blocks and each block focuses on a particular type of module.
All of the above methodologies are quite familiar during .NET application development. But, coming to Crystal Report development, developers take advantage of different approaches to make it easier to maintain. Most of those approaches directly use databases as data sources.
When we maintain all our data access and business logic in the form of different classes, why can’t we use the same classes for report development as well? This approach would work well when we try to have reports embedded as part of our application, and this may not be suitable (or practical) to deploy with Crystal Servers.
I must also agree that performance would be an issue. Performance tuning a report is not simply related to the report. We may need to tune a database (or data) with different approaches to get the reports out more quickly. Most people use stored procedures, indexed views, and so on to improve the performance of reports.
Considering all of the above issues, I would like to present a simple approach which marries business logic classes with Crystal Reports. In my approach, I would like to introduce the following four classes:
DBHelper
Order
OrderCollection
OrderFactory
"DBHelper" is meant only to interact with databases. "Order" contains detailed information about a particular order from the "Northwind" database. "OrderCollection" simply maintains a set of orders. "OrderFactory" populates the "OrderCollection."
Please note that this approach may not be suitable for every application or report you develop. It totally depends on issues such as requirements, performance, maintenance, administration, scheduling, and so forth.
Let us observe the code of each of those classes before looking at report development.
I developed this class for simplicity. You can extend it to make it more robust by including several other features such as caching, stored procedure parameters, and so forth. If you are familiar with the Microsoft Data Access Block (part of Microsoft Enterprise Library), you can even include that, too.
Create a new web site and add a new class named "DBHelper." The following is the code which contains a few methods to carry out most important tasks related to the database:
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class DBHelper
Private Shared Function getConnectionString() As String
Information from the database must be contained in an object. Using the "Solution Explorer," add a new class named "Order.vb". This class is used to hold the information based on the fields selected.
The code for the "Order" class is as follows:
Imports Microsoft.VisualBasic
Public Class Order
Private _OrderID As Int32
Private _CustomerName As String
Private _EmployeeName As String
Private _OrderDate As Date
Private _OrderValue As Double
Public Property OrderID() As Int32
Get
Return _OrderID
End Get
Set(ByVal value As Int32)
_OrderID = value
End Set
End Property
Public Property CustomerName() As String
Get
Return _CustomerName
End Get
Set(ByVal value As String)
_CustomerName = value
End Set
End Property
Public Property EmployeeName() As String
Get
Return _EmployeeName
End Get
Set(ByVal value As String)
_EmployeeName = value
End Set
End Property
Public Property OrderDate() As Date
Get
Return _OrderDate
End Get
Set(ByVal value As Date)
_OrderDate = value
End Set
End Property
Public Property OrderValue() As Double
Get
Return _OrderValue
End Get
Set(ByVal value As Double)
_OrderValue = value
End Set
End Property
Public Sub New(ByVal oID As String, ByVal oCustomer As String, ByVal oEmployee As String, ByVal oOrderDate As String, ByVal oOrderValue As String)
Me.OrderID = oID
Me.CustomerName = oCustomer
Me.EmployeeName = oEmployee
Me.OrderDate = oOrderDate
Me.OrderValue = oOrderValue
End Sub
End Class
Add one more class (OrderCollection.vb), which holds a set of objects (collection) based on the above class. The code for the class is as follows:
Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Public Class OrderCollection
Inherits List(Of Order)
End Class
You need not write any code for the above class as it deals with generics in .NET 2.0.
This class mainly interacts with the "DBHelper" class to fetch information from the database and populates the collection objects. This class can also have DML operations like Insert, Update, Delete, and so forth. For the sake of clarity, I removed those methods.
Add a new class (OrderFactory.vb), which deals with business logic, and copy the following code:
Imports Microsoft.VisualBasic
Imports System.Data
Public Class OrderFactory
Public Function GetOrdersListCollection() As OrderCollection
Dim clnOrders As New OrderCollection
For Each dr As DataRow In GetOrdersList.Rows
clnOrders.Add(New Order(dr("OrderID"), dr("CustomerName"), dr ("EmployeeName"), dr("OrderDate"), dr("OrderValue")))
Next
Return clnOrders
End Function
Public Function GetOrdersList() As DataTable
Dim sql As String
sql = "SELECT dbo.Orders.OrderID, dbo.Customers.CompanyName AS CustomerName, dbo.Employees.LastName AS EmployeeName, dbo.Orders.OrderDate, "
sql &= " dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID "
sql &= "GROUP BY dbo.Orders.OrderID, dbo.Customers.CompanyName, dbo.Employees.LastName, dbo.Orders.OrderDate"
Return DBHelper.getDataTable(sql)
End Function
End Class
If you would like to work with stored procedures, create a stored procedure at SQL Server and work with "DBHelper.SPgetDataTable("<storedprocedurename>")". This is another professional approach with the best database performance in mind.
Designing, developing and binding Crystal Report to the Visual Basic 2005 business logic class
Once the classes are complete, add a new Crystal Report to the web site (SampleReport01.rpt). With the Standard Report expert, select the "Order" class available under ".NET Objects" and include all the fields. Report Designer gets opened with all the fields and you can format the report according to your requirements.
Drag a button and CrystalReportViewer control onto the web form. In the code behind, copy the following code:
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
CrystalReportViewer1_Navigate(Nothing, Nothing)
End Sub
Protected Sub CrystalReportViewer1_Navigate(ByVal source As Object, ByVal e As CrystalDecisions.Web.NavigateEventArgs) Handles CrystalReportViewer1.Navigate
I am creating an instance of "OrderFactory" and executing the "GetOrderListCollection" method. It returns the collection which gets bound to the report.
Finally, execute the application and hit the button to view the report.
I hope you enjoyed the article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com