Programmatically Changing the Data Source

Some programs require changing the data source of a table while the application is running. This can include changing the server name or database name. Although this isn’t common, it does happen. An example is an application that wants to use the same report for multiple data sources. For example, a sales application could offer the user two reports: a sales report that shows current year data and a sales report that shows historical data. This historical data is archived on another server. As another example, you might use a test database for designing reports but the reports will use the production database when deployed. By changing the data source during run-time, you can design a single report that handles both options.

The data source can only be changed using the ReportDocument object. Modify the ServerName and DatabaseName properties to change the data sourceThese properties represent different values depending upon which data source you are connecting to. Table 17-1 shows what belongs in each propertyTable 17-1. The properties for each type of data source.

Table 17-1 Setting the connection proerties for different data sources.

The data source isn’t designed to have its connection type changed. For example, if the report is connected to SQL Server with an ODBC connection, you can’t modify the connection during runtime to use OLE DB or any other type of connection. This would require the report engine to change the DLL it uses to process the data source. This is set when the application is compiled and can’t be changed.

Listing 17-2 shows how to change the data source of a table as well as set the user credentials.

Listing 17-2. Changing the location of a data source.
[VB.NET]
Private Sub LogonToTables(ByVal myUserId As String, ByVal myPassword As String, _
ByVal myServerName As String, ByVal myDatabaseName As String)
Dim myReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
myReport.Load("report.rpt")
Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
'Set the database properties and security credentials
Dim myConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
myConnectionInfo.ServerName = myServerName
myConnectionInfo.DatabaseName = myDatabaseName
myConnectionInfo.UserID = myUserId
myConnectionInfo.Password = myPassword
'Apply the ConnectionInfo to the report tables
Dim myTables As CrystalDecisions.CrystalReports.Engine.Tables
myTables = myReport.Database.Tables
For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
myTableLogonInfo = myTable.LogOnInfo
myTableLogonInfo.ConnectionInfo = myConnectionInfo
myTable.ApplyLogOnInfo(myTableLogonInfo)
Next
CrystalReportViewer1.ReportSource = myReport
End Sub
[C#]
private void LogonToTables(string UserId, string Password, string ServerName, string DatabaseName, CrystalDecisions.CrystalReports.Engine.ReportDocument myReport)
{
CrystalDecisions.Shared.TableLogOnInfo myTableLogonInfo;
//Set the database properties and security credentials
CrystalDecisions.Shared.ConnectionInfo myConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo();
myConnectionInfo.ServerName = ServerName;
myConnectionInfo.DatabaseName = DatabaseName;
myConnectionInfo.UserID = UserId;
myConnectionInfo.Password = Password;
//Apply the ConnectionInfo to the report tables
CrystalDecisions.CrystalReports.Engine.Tables myTables;
myTables = myReport.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table myTable in myTables)
{
myTableLogonInfo = myTable.LogOnInfo;
myTableLogonInfo.ConnectionInfo = myConnectionInfo;
myTable.ApplyLogOnInfo(myTableLogonInfo);
}
crystalReportViewer1.ReportSource = myReport;
}

You should make sure that the ServerName and DatabaseName properties are the proper case. I’ve noticed in some projects that these are case sensitive and that Crystal Reports won’t accept them. Unfortunately, I have not been able to find any pattern to this problem. Sometimes it works; other times it doesn’t. To be on the safe side, assume that these properties are always case sensitive.

This code loops through each table in the ReportDocument.Database.Tables collection and gets a reference to the TableLogOnInfo object. It uses the ConnectionInfo class to apply the database and login information that was passed to the procedure. After setting the properties, it calls the ApplyLogOnInfo() method of the Table class. This updates the table with the new login properties. The report viewer is assigned the report document and this forces the report to open the database connections using the user credentials and display the report.