Choose the right data access strategy when using Crystal Reports with .NET

If you've decided to use Crystal Reports as your data source in .NET, it's important to plan your data access strategy carefully. Your choice of data access strategy will directly affect your ability to update the reports and manage their distribution.

Push or pull?
You basically have two choices when acquiring data:

• Pull it from a data source.
• Push a dataset to the report.

With the pull method, the Crystal Reports engine pulls the requested data from a connected data source. Crystal Reports can use industry standard OLE DB or ODBC drivers to connect to a database, or it can use specialised drivers (included with the package) that query an Excel worksheet or an Access database. The developer starts one of the built-in wizards, selects data sources from which to pull data, and then selects the tables and fields to use in the report. Since this activity is completed at design time, the user doesn't need to do anything at run time for the reports to access the necessary data.

The push method uses a different procedure to give the reporting engine access to the data. In this mode, the developer sets up the tables and fields that the report uses in the report designer as normal. But instead of hard-coding the data sources, the developer builds a dataset at run time and passes it to the report. If the .NET project uses typed datasets, the developer can use those as the model to embed table definitions into the report. At run time, the developer simply populates a dataset and passes it to the report engine using the SetDataSource method.

There are two objects that expose the SetDataSource method. The ReportDocument class is the encapsulation of the report itself; it exposes SetDataSource as a method. You should use SetDataSource from the report object when your dataset contains all the tables and they match with the definitions inside the report. The report object also exposes a database property that has a tables collection. The Table class also has a SetDataSource method. You can fill the report's tables directly by retrieving the appropriate table, calling SetDataSource, and passing the dataset with the appropriate records directly to it.

The choice of pull or push data access is not mutually exclusive. The developer can mix these methods in a single report, allowing the Crystal Reports engine to pull data from different data sources at run time. But you must make your decision to use either or both methods while considering the context of each method's limitations.

Making a push or pull decision
When deciding which data access method to use, consider the trade-offs. One of the most important considerations in adding reporting capability to any project is how the decision will affect maintainability. Using the pull access method requires the developer to put information about the data source into a report directly. If the details need to be changed, the reports affected will also have to be changed and potentially redeployed. Using datasets with the push model requires that only the report know the schema for the data. It's up to the developer to gather the necessary data and make sure it's accessible in the data access layer at run time.

On the other hand, the push model requires more work to set up. The developer has to set up database connections, run the necessary queries, and pass the data to the report using the SetDataSource method. Push model reports are also more time consuming for developers to create because they have to define the schema with little help from the underlying data source. Using typed datasets makes the process somewhat simpler since the report experts can extract the information from the schema file. Otherwise, the developer has to create the schema using a field or data definition file and then load it into the report at design time. The push model also requires more interaction between the view and the report.

The final consideration for using these models involves the choice of viewer. Since a report created with the pull model has all data source definitions embedded, it can be used with the Windows or Web report viewer. A report created using the push model, however, can be viewed only by using a program designed to collect and expose the data for it.

Consider such trade-offs when selecting a data acquisition model; doing so can help you make more intelligent decisions about how to architect a more effective reporting mechanism using Crystal Reports.