Setting Crystal Report's Database Info at Run-Time

In previous posts I have discussed issues regarding Crystal Reports programming in ASP.NET applications. My most recent problem was, "How can I change a report's data source at run-time?" At design-time, you can use the CR designer's "Set Location" option, but this is impractical because:

• Modifying even just a few reports with this mechanism is time consuming
• Changing a report's data source, although infrequent, is not a one-time event

On my current project, I need to change the data source on reports for these reasons:

• Each developer has different test databases to run the reports against
• Moving the ASP.NET app through Development, Testing, and Production phases requires using different data stores for the reports

These are pretty standard circumstances for any dev team. Fortunately, it was very easy to leverage my EnterpriseDataStore class and set the appropriate information on the report.

SqlConnection conn = EnterpriseDataStore.GetConnection(<DataStore Name>);
CrystalDecisions.Shared.ConnectionInfo connInfo = new ConnectionInfo();
connInfo.DatabaseName = conn.Database;
connInfo.ServerName = conn.DataSource;
// Leave UserID & Password alone if report was designed for Trusted Security (NT Integrated)
connInfo.UserID = <user name>;
connInfo.Password = <user password>;
foreach(CrystalDecisions.CrystalReports.Engine.Table tbl in rpt.Database.Tables) {
    CrystalDecisions.Shared.TableLogOnInfo logOnInfo = tbl.LogOnInfo;
    logOnInfo.ConnectionInfo = connInfo;
    tbl.ApplyLogOnInfo(logOnInfo);
}

The combination is powerful. Now I can change the connection string info as part of the app config and have the reports point to the right data store dynamically. Each developer and app environment (Test, Production) has the config setting in machine.config or web.config, so the code can move seamlessly among all environments.