Object-Oriented Report Development using Crystal Reports and ASP.NET 2.0

If you are new to Crystal report programming, I strongly suggest that you go through the following articles:

Programming Crystal Reports with ASP.NET 2.0

Working with Parameters with Crystal Reports and ASP.NET 2.0

If you are new to OOP-based database applications in Visual Basic 2005, I further suggest that you read the following article: 

Implementing OOP to Develop Database-Oriented Applications using VB.NET 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.

The DBHelper class for interacting with the database

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

    Return System.Configuration.ConfigurationManager.ConnectionStrings
("SampleDB").ConnectionString

  End Function

  Public Shared Sub SQLExecute(ByVal strSQL As String)

    Dim Conn As New SqlConnection(getConnectionString)

    Dim cmd As New SqlCommand(strSQL, Conn)

    With cmd

      .Connection.Open()

      .ExecuteNonQuery()

      .Connection.Close()

      .Dispose()

    End With

  End Sub

  Public Shared Function getRow(ByVal strSELECT As String) As DataRow

    Dim da As New SqlDataAdapter(strSELECT, getConnectionString)

    Dim dt As New DataTable

    da.Fill(dt)

    da.Dispose()

    If dt.Rows.Count = 0 Then Return Nothing Else Return dt.Rows(0) 'return only
first row

  End Function

  Public Shared Function getDataTable(ByVal strSELECT As String) As DataTable

    Dim Conn As New SqlConnection(getConnectionString)

    Dim da As New SqlDataAdapter(strSELECT, Conn)

    Dim dt As New DataTable

    da.Fill(dt)

    da.Dispose()

    Return dt

  End Function

  Public Shared Function getRowValue(ByVal strSELECT As String) As String

    Dim Conn As New SqlConnection(getConnectionString)

    Dim cmd As New SqlCommand(strSELECT, Conn)

    Dim value As String = ""

    With cmd

      .Connection.Open()

      value = .ExecuteScalar() & "" 'concatenating an empty string..to eliminate null
or nothing

      .Connection.Close()

      .Dispose()

    End With

    Return value

  End Function

  Public Shared Function SPgetDataTable(ByVal SPname As String) As DataTable

    Dim cmd As New SqlCommand(SPname, New SqlConnection
(getConnectionString))

    cmd.CommandType = CommandType.StoredProcedure

    Dim da As New SqlDataAdapter(cmd)

    Dim dt As New DataTable

    da.Fill(dt)

    da.Dispose()

    Return dt

  End Function

End Class

The web.config should have a ConnectionString entry as follows:

   <connectionStrings>

       <add name="SampleDB" connectionString="Data Source=.sqlexpress;initial
catalog=Northwind;user id=sa;password=eXpress2005"/>

   </connectionStrings>

Developing classes to hold information from the database

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.

Adding a Factory class to populate object collection

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 &= "          SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity)
AS OrderValue "

    sql &= "FROM      dbo.Orders INNER JOIN "

    sql &= "          dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID INNER JOIN "

    sql &= "          dbo.Employees ON dbo.Orders.EmployeeID =
dbo.Employees.EmployeeID INNER JOIN "

    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

    Dim rep As New ReportDocument

    rep.Load(Server.MapPath("SampleReport01.rpt"))

    rep.SetDataSource((New OrderFactory).GetOrdersListCollection)

    Me.CrystalReportViewer1.ReportSource = rep

    Me.CrystalReportViewer1.DataBind()

  End Sub

End Class

The most important statement to note from the above code is the following:

    rep.SetDataSource((New OrderFactory).GetOrdersListCollection)

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