HomeASP.NET Working with ADO.NET Datasets and .NET Obj...
Working with ADO.NET Datasets and .NET Objects using Crystal Reports and ASP.NET 2.0
This is the third article in a series focusing on programming with Crystal Reports with ASP.NET 2.0. In this article, we will focus on working with Crystal Reports with data sources that include ADO.NET datasets and .NET objects.
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.
Adding a strongly typed dataset to the project: designer
Before proceeding further, open the "SampleWebSite01" website (available as a download through the first article in this series) and add a new dataset as follows:
Using the "Solution Explorer," right click on the project name (SampleWebSite01) and click "Add New Item."
In the templates, select "Dataset," provide "Orders.xsd" as the "Name" and click on "Add."
Click "Yes" when it asks for confirmation of placing the dataset in "App_Code" folder.
When it opens the TableAdapter Configuration Wizard, cancel it.
Using ToolBox, select DataTable.
Place a new DataTable on the designer and rename DataTable to "Orders."
Right click on the "Orders" datatable, go to Add -> Column and give the column name as "OrderID"
Similarly, add OrderDate, CustomerID and EmployeeID.
Using the Properties window, modify the data types of the columns respectively.
Once the designing is finished, right click on the designer and go to "View Code." Make the necessary changes so that the code looks similar to the following:
Adding a Crystal Report with data source as a strongly typed dataset
Add a new Crystal Report (named "DatasetDemo01.rpt") and select "Standard report" as the expert wizard. When choosing the data, select "Orders" in ADO.NET Dataset and click "Next."
In the fields screen, select all available fields to "fields to display."
Click "Next" and finally click "Finish."
Report Designer gets opened and you can format it according to your requirements.
Once the dataset and Crystal reports are created, add a new web form (DatasetDemo01.aspx) to the project, and then drag and drop a button and CrystalReportViewer Control.
Make sure that the source behind looks like the following:
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 dt As New Orders.OrdersDataTable
Dim ad As New SqlDataAdapter("SELECT TOP 200 OrderID, OrderDate, CustomerID, EmployeeID FROM orders", "Data Source=.sqlexpress;initial catalog=Northwind;user id=sa; password=eXpress2005")
ad.Fill(dt)
Dim rep As New ReportDocument
rep.Load(Server.MapPath("DatasetDemo01.rpt"))
rep.SetDataSource(CType(dt, DataTable))
Me.CrystalReportViewer1.ReportSource = rep
Me.CrystalReportViewer1.DataBind()
End Sub
End Class
You may have to modify the connection string so that it connects to your server. Finally, set the start page as DatasetDemo01.aspx, execute the application and hit the button to view the report.
In previous sections, a strongly typed dataset is created (using Visual Studio Designer) and finally assigned as the data source to Crystal Reports. Now, we will start working on only custom classes (acting as .NET Objects) and assign them as a data source to Crystal Reports.
Close the previous project and create a new website. Using the "Solution Explorer," add a new class named "Order.vb." This class simply works as an entity class holding a single row of the Orders table. For our demonstration, I took only four fields into consideration. The code for the "Order" class is as follows:
Imports Microsoft.VisualBasic
Public Class Order
Private _OrderID As Int32
Private _OrderDate As Date
Private _CustomerID As String
Private _EmployeeID As String
Public Property OrderID() As Int32
Get
Return _OrderID
End Get
Set(ByVal value As Int32)
_OrderID = 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 CustomerID() As String
Get
Return _CustomerID
End Get
Set(ByVal value As String)
_CustomerID = value
End Set
End Property
Public Property EmployeeID() As String
Get
Return _EmployeeID
End Get
Set(ByVal value As String)
_EmployeeID = value
End Set
End Property
Public Sub New(ByVal oID As String, ByVal oDate As Date, ByVal oCustomerID As String, ByVal oEmployeeID As String)
In previous section, we added a class which holds only one row of information from the "Orders" table (but with few fields). Now, we are adding one more class to hold a set of rows from the "Orders" table based on the "Order" entity class. This class must be named "Orders.vb" and must inherit from the "CollectionBase" class (part of the .NET Framework Class Library).
Add a new class named "Orders.vb" and copy the following code:
Imports Microsoft.VisualBasic
Public Class Orders
Inherits CollectionBase
Public Sub add(ByVal e As Order)
List.Add(e)
End Sub
Public Sub delete(ByVal e As Order)
List.Remove(e)
End Sub
Public Sub delete(ByVal index As Integer)
If index < 0 Or index > List.Count - 1 Then
Throw New Exception("Invalid index to remove")
End If
List.RemoveAt(index)
End Sub
Default Public Overridable ReadOnly Property Item(ByVal index As Integer) As Order
Get
Return CType(Me.List(index), Order)
End Get
End Property
End Class
It is basically a collection class which holds a group of objects related to the "Order" class.
Once the classes are complete, add a new crystal report to the web site (Sample01.rpt) and select the "Order" class available under ".NET Objects" (Fig 07).
Add it as selected table and click next, add all fields and finally click finish. Report Designer opens with all the fields and you can format the report according to your requirements.
Add a new web form (Sample01.aspx) and drag a button and CrystalReportViewer control onto the web form. In the code behind, copy the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Partial Class Sample01
Inherits System.Web.UI.Page
Public Shared Function getList() As Orders
Dim dt As New DataTable
Dim ad As New SqlDataAdapter("SELECT TOP 200 OrderID, OrderDate, CustomerID, EmployeeID FROM orders", "Data Source=.sqlexpress;initial catalog=Northwind;user id=sa; password=eXpress2005")
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("Sample01.rpt"))
rep.SetDataSource(getList())
Me.CrystalReportViewer1.ReportSource = rep
Me.CrystalReportViewer1.DataBind()
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
CrystalReportViewer1_Navigate(Nothing, Nothing)
End Sub
End Class
Finally, execute the application and hit the button to view the report.
Of all of the above code, the following is the most important part to understand:
Once the DataAdapter gives the datatable, I am converting the datatable to "Orders" collection by creating an "Order" object for each row available. Once the collection is populated, we assign the same as the data source to the Crystal Report.
I hope you enjoyed the article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com
ASP.NET 
/ 23 
ASP.NET Export to Excel and Word using VB.NET and C#
