• Crystal Report and .Net – Changing a Datasource at Runtime

    Posted Jul 30th, 2009 By in Miscellaneous With | No Comments

    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.

    • delicious
    • digg
    • reddit

    Paul
    My name is Paul Patterson and I am a software developer who has a keen interest in technology, including; open source, .Net, and anything Interweb. When not crafting some code, I can be found learning something new about photography. As well, I occasionally escape to the "music room" with my guitars to practice a few scales and then jam with my favourite FM radio stations.

Leave a Reply


No comments yet. Be the first!

© Copyright Paul S Patterson - Please, no touchie. :)