I couldn’t figure out why a Crystal Report I developed was displaying information from my development system while viewing the report in a test system. Seems like the report was also caching the data or something.

What I had to do was somehow ensure that, each time a user loaded a Crystal report in the application’s report viewer, the report would get the data from the database used by the system. To do this, I needed to dynamically change the data source of the report at runtime.

Here is some code that I hope you may find useful…

First make sure to bring in the necessary namespaces.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

Here’s the complete code I used. The magic occurs when a user clicks a button to open a Crystal Report file.

private void btnOpenReport_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Crystal Report Documents (*.rpt)|*.rpt";
            dlg.Title = "Please select a Crystal Report to run...";
            dlg.InitialDirectory = "C:\\";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                ReportDocument rpt = new ReportDocument();
                rpt.FileName = dlg.FileName;

                // make sure the connection to the right database.
                TableLogOnInfos crTableLogonInfos = new TableLogOnInfos();
                TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
                ConnectionInfo crConnectionInfo = new ConnectionInfo();
                Tables crTables;

                DbConnection conn = new DbConnection(ConfigHelper.ConnString);

                string dbServer = conn.ServerName;
                string dbName = conn.DatabaseName;
                string dbUserName = conn.UserName;
                string dbPassword = conn.Password;

                crConnectionInfo.ServerName = dbServer;
                crConnectionInfo.DatabaseName = dbName;
                crConnectionInfo.UserID = dbUserName;
                crConnectionInfo.Password = dbPassword;

                crTables = rpt.Database.Tables;

                foreach (Table crTable in crTables)
                {
                    crTableLogonInfo = crTable.LogOnInfo;
                    crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                    crTable.ApplyLogOnInfo(crTableLogonInfo);
                }

                rpt.Refresh();
                vwMain.ReportSource = rpt;

            }
        }

The key part is the loop through the tables already in the report, resetting each of their connection information.

Worked like a charm.