Change the Data Source of a Crystal Reports Report at Run-time using C#

It’s a known fact that in Crystal Reports, if you try to display details, using data in a SQL Server database other than the one that you’ve used to design the report, either you have to set the database location or refresh the report. This is a common issue that development teams face when reports are being deployed to the production environment.

But this can be prevented using the following method.

The following namespaces are required:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

And we have to assign the required SQL Server information to each table/view of the report, sub reports, and the report viewer.

Use the following code:

SQLReport report = new SQLReport();
 
//Get SQL Server Details
string zServer = @"SERVER_NAME";
string zDatabase = @"DATABASE";
string zUsername = @"USER";
string zPassword = @"PASSWORD";

ConnectionInfo ciReportConnection = new ConnectionInfo();

ciReportConnection.ServerName = zServer;
ciReportConnection.DatabaseName = zDatabase;
ciReportConnection.UserID = zUsername;
ciReportConnection.Password = zPassword;

//Assign data source details to tables

foreach (Table table in report.Database.Tables) {
     table.LogOnInfo.ConnectionInfo = ciReportConnection;
     table.ApplyLogOnInfo(table.LogOnInfo);
}

foreach (ReportDocument subrep in report.Subreports) {
     foreach (Table table in subrep.Database.Tables) {
         table.LogOnInfo.ConnectionInfo = ciReportConnection;
         table.ApplyLogOnInfo(table.LogOnInfo);
     }
}

//Assign data source details to the report viewer
if (this.crystalReportViewer1.LogOnInfo != null) {
     TableLogOnInfos tlInfo = this.crystalReportViewer1.LogOnInfo;
     foreach (TableLogOnInfo tbloginfo in tlInfo) {
         tbloginfo.ConnectionInfo = ciReportConnection;
     }
}
 
crystalReportViewer1.ReportSource = report;
crystalReportViewer1.Refresh();