Reporting from the DataReader

Many people process data using the DataReader class and they want to run reports from it as well. Crystal Reports gives you the ability to do this. The DataReader class is another one of the many data types that can be passed to the SetDataSource() method.

Using the DataReader as a data source has some limitations to it that make it more difficult to use than the other data sources. They are as follows:

The data must be returned from a method that uses the IDataReader interface.

The method must be Shared (Visual Basic) or static (C#).

The data provider must be the OleDB .NET data provider.

The method must be part of a separate class library that is compiled into an assembly.

With the exception of the last bullet point, these requirements are all fairly easy to implement. The last bullet point is the most restrictive of the four listed. It says that you can’t use a DataReader that is in the same project that the report is in. You have to create a separate library project and compile it into an assembly before Crystal Reports can reference it. In other words, if you want to report from a DataReader, you have to create a separate DLL just for the purpose of returning the DataReader from a shared method. Since this isn’t a requirement for using the other data source types, the question that immediate comes to my mind is, “Why?”

Behind the scenes, Crystal Reports uses this DLL to determine the report’s data source structure. Crystal Reports instantiates the class in the DLL and calls the appropriate method to get an instance of the fully populated DataReader object. It analyzes this object to determine the data structure and create the list of fields that you can report from. If this weren’t a stand-alone DLL, there would be no way for Crystal Reports to determine its data structure at design time. Creating a separate library project is a necessary evil if you want to report from a DataReader object.

Let’s look at the steps for creating a report using a DataReader. Although the requirements are a bit unusual, you’ll see that they are very easy to implement and you will be able to do it quickly.

The first step is to create a new project using the Class Library template. In this example, I named my project, MyDataReader. After creating the project, I deleted the default Class1.vb file that was created automatically and added a new class called DataSource. The code is in listing 17-13.

Listing 17-13. Source code for the MyDataReader project.
[VB.NET]
Imports System.Data
Imports System.Data.OleDb
Public Class DataSource
'Note: Use your own connection string here
Private Const CONNECTION_STRING As String = _
"provider=sqloledb;Data Source=vmware;Initial Catalog=xtreme;User Id=sa;Password=pw"
Private Const CUSTOMER_TABLE As String = _
"SELECT [Customer ID], [Customer Name], [Contact Last Name], City FROM Customer"
Public Shared Function GetCustomers() As IDataReader
Dim myOleDbConnection As OleDbConnection = New OleDbConnection(CONNECTION_STRING)
myOleDbConnection.Open()
Dim myOleDbCommand As OleDbCommand
OleDbCommand = New OleDbCommand(CUSTOMER_TABLE, myOleDbConnection)
Dim myIDataReader As IDataReader = myOleDbCommand.ExecuteReader()
Return myIDataReader
End Function
End Class
[C#]
using System.Data;
using System.Data.OleDb;
public class DataSource
{
//Note: Use your own connection string here
private const string CONNECTION_STRING = "provider=sqloledb;Data Source=vmware;Initial Catalog=xtreme;User Id=sa;Password=pw";
private const string CUSTOMER_TABLE = "SELECT [Customer ID], [Customer Name], [Contact Last Name], City FROM Customer";
public static IDataReader GetCustomers()
{
OleDbConnection myOleDbConnection = new OleDbConnection(CONNECTION_STRING);
myOleDbConnection.Open();
OleDbCommand myOleDbCommand;
myOleDbCommand = new OleDbCommand(CUSTOMER_TABLE, myOleDbConnection);
IDataReader myIDataReader = myOleDbCommand.ExecuteReader();
return myIDataReader;
}
}

This class creates a shared function called GetCustomers(). It is declared using the IDataReader interface from the System.Data.OleDB library. This fulfills the first three bullet points listed above. The rest of the code is the standard code for creating the appropriate connection string and SQL query and calling the ExecuteReader() method.

After you enter this code, compile it so that it saves it as a DLL file. Now it’s ready to be used as a data source for Crystal Reports.

The second step is creating the project (or open an existing one) and add a report to it that consumes the DLL. When you create the report using the Report Wizard, on the Data tab, click the Create New Connections node and click the ADO.NET node. This opens the ADO.NET window where you can enter the connection information (refer back to Figure 17-5).

For the file path, click the ellipses button to open the Open File dialog box. Change the File Type dropdown to “.NET Data Provider DLL”. Navigate to where the DLL is located and select it. On the Class Name option, click the dropdown list and select the MyDataReader.DataSource class. Click the Finish button to save your changes and close the window.

The Data window now shows your DLL as a data source and the GetCustomers method is shown as the table name. Double-click on the GetCustomers table so that it is added to the Selected Tables list.

Click the Next button to go to the Fields tab. Here you will see that all the fields from the DataReader are listed. Behind the scenes, Crystal Reports instantiated the DLL, called the GetCustomers() method, and examined the data to determine the fields available.

At this point, creating the report follows the standard steps of using the Report Wizard to pick the fields to print and going through the remaining steps.

After you have finished creating the report, it has stored an internal reference to the DLL. Printing the report is simply a matter of getting a reference to the report and passing it to the viewer. There is no need to call the SetDataSource() method. For example, here is sample code which opens the report and previews it.

Listing 17-14. Previewing the report that uses the DataReader as a data source.
Dim myReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
myReport.Load("CrystalReport1.rpt")
CrystalReportViewer1.ReportSource = myReport

It is a good idea to test your code prior to using it as a data source for your report. This ensures that there are no bugs and that the security credentials are valid. For example, you can do something like calling the method and seeing if it returns any errors and correcting them if necessary. Here is something simple that I used to test the above code.

Dim Test As System.Data.OleDb.OleDbDataReader
Test = MyDataReader.DataSource.GetCustomers()