Tuesday, 9 August 2011

Change the data source of a Crystal 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 team face when the reports are being deployed to the production environment.

But this can be prevented using the following method.

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();

4 comments:

  1. thank you very much, you helped me a lot

    ReplyDelete
  2. thank u very much, you r simply clever, i have just changed data source of the report not the report viewer
    thank u a lot

    ReplyDelete