Click here to Skip to main content
15,354,545 members
Articles / Programming Languages / C#
Technical Blog
Posted 19 Oct 2011

Tagged as

Stats

37.3K views
2 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
19 Oct 2011CPOL
This is a common issue that development teams face when reports are being deployed to the production environment.

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:

C#
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:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
QuestionError NullReference Exception Pin
abdou_3127-Sep-20 23:53
Memberabdou_3127-Sep-20 23:53 
QuestionDoesn't work for Commands Pin
Allan Kaliel9-Jul-14 6:57
MemberAllan Kaliel9-Jul-14 6:57 
AnswerRe: Doesn't work for Commands Pin
Umair Ghaffar10-Sep-19 1:39
professionalUmair Ghaffar10-Sep-19 1:39 
Questionwhat about table changes? Pin
LAXATECH1-Oct-13 19:39
MemberLAXATECH1-Oct-13 19:39 
AnswerRe: what about table changes? Pin
Manjuke Fernando3-Oct-13 1:21
professionalManjuke Fernando3-Oct-13 1:21 
LAXATECH wrote:
What if i also want to change the name of a table in the report, or better still add a new table but with the same schema?


Actually this doesn't change or update report's connection details permanently, only does it dynamically run-time. But your requirement needs the report to be opened and do the change. For example if you are adding new tables to the report, then you have to alter the columns on the report etc.
Manjuke Fernando

QuestionError Pin
aju1844-Apr-12 18:40
Memberaju1844-Apr-12 18:40 
AnswerRe: Error Pin
Manjuke Fernando4-Apr-12 19:11
professionalManjuke Fernando4-Apr-12 19:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.