Dynamically change the connection of a Crystal Report

Question

I am using CrystalReportViewer and CrystalReportSource to load and display an .rpt file in my application.

The situation I have is this:

Say a person created a crystal report outside of my application and set its datasource to database A. I then use that .rpt file in my application but I need to bind it to a different database (identical to the original one in terms of table structure and column names but with a different connection string using a different user name and password). How do I do that in C#?

Currently I load the report using:

this.CrystalReportSource1.ReportDocument.Load(reportsSubfolder + report.ReportFileName);
//it is here that I need to change the connection data of the report.

Flyfish

Answer 1

I use a function like the following to assign the connection information at runtime.

private void SetDBLogonForReport(CrystalDecisions.Shared.ConnectionInfo connectionInfo, CrystalDecisions.CrystalReports.Engine.ReportDocument reportDocument)
{
    CrystalDecisions.CrystalReports.Engine.Tables tables = reportDocument.Database.Tables;

    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        CrystalDecisions.Shared.TableLogOnInfo tableLogonInfo = table.LogOnInfo;

        tableLogonInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogonInfo);
    }
}

You should be able to simply create a new ConnectionInfo object with the necessary info and pass it into the function along with the report document. Hope this helps.

Tinctorial

Hi Dusty ...thanks for the help. I implemented the lines of code above and I also did some research on my own. so hereis my code: private void AssignConnectionInfo(ReportDocument document, ConnectionInfo crConnection) { foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in document.Database.Tables) { – Flyfish

Here is the code - sorry hit the post too early before: private void AssignConnection(ReportDocument document,ConnectionInfo crConnection) { foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in document.Database.Tables) { CrystalDecisions.Shared.TableLogOnInfo tableLogonInfo = crTable.LogOnInfo; tableLogonInfo.ConnectionInfo = crConnection; crTable.ApplyLogOnInfo(tableLogonInfo); CrystalReportViewer1.ReportSource = document; CrystalReportViewer1.RefreshReport(); } crConnection has the correct values whenpassed. – Flyfish

The formatting is not very good by the way...is there a way to format my message ( like tags or something) – Flyfish

@Flyfish - You can edit your question with this code which will allow you to format it how you would like. It looks like the AssignConnection function is essentially the same as the one in my answer, so I'd assume that would work. Do you get an error when of any sort? – Tinctorial

Answer 2

VB Code:

    Dim report = New ReportDocument

    Try
        'open report
        report.Load(filename, OpenReportMethod.OpenReportByTempCopy)

        'do this for each distinct database connection, rather than for table
        report.SetDatabaseLogon("user", "password", "server", "database")

    Catch ex As Exception
        'preserve the stack trace information
        Throw

    End Try

Ratfink

Answer 3

You can use the following code to apply certain connection details for a report at run time.

Please use that method just after loading report rpt file, and pass required connection details to method, it will fetch report data from passed connection details.

    public static void CrystalReportLogOn(ReportDocument reportParameters,
                                          string serverName,
                                          string databaseName,
                                          string userName,
                                          string password)
    {
        TableLogOnInfo logOnInfo;
        ReportDocument subRd;
        Sections sects;
        ReportObjects ros;
        SubreportObject sro;

        if (reportParameters == null)
        {
            throw new ArgumentNullException("reportParameters");
        }

        try
        {
            foreach (CrystalDecisions.CrystalReports.Engine.Table t in reportParameters.Database.Tables)
            {
                logOnInfo = t.LogOnInfo;
                logOnInfo.ReportName = reportParameters.Name;
                logOnInfo.ConnectionInfo.ServerName = serverName;
                logOnInfo.ConnectionInfo.DatabaseName = databaseName;
                logOnInfo.ConnectionInfo.UserID = userName;
                logOnInfo.ConnectionInfo.Password = password;
                logOnInfo.TableName = t.Name;
                t.ApplyLogOnInfo(logOnInfo);
                t.Location = t.Name;
            }
        }
        catch
        {
            throw;
        }

        sects = reportParameters.ReportDefinition.Sections;
        foreach (Section sect in sects)
        {
            ros = sect.ReportObjects;
            foreach (ReportObject ro in ros)
            {
                if (ro.Kind == ReportObjectKind.SubreportObject)
                {
                    sro = (SubreportObject)ro;
                    subRd = sro.OpenSubreport(sro.SubreportName);
                    try
                    {
                        foreach (CrystalDecisions.CrystalReports.Engine.Table t in subRd.Database.Tables)
                        {
                            logOnInfo = t.LogOnInfo;
                            logOnInfo.ReportName = reportParameters.Name;
                            logOnInfo.ConnectionInfo.ServerName = serverName;
                            logOnInfo.ConnectionInfo.DatabaseName = databaseName;
                            logOnInfo.ConnectionInfo.UserID = userName;
                            logOnInfo.ConnectionInfo.Password = password;
                            logOnInfo.TableName = t.Name;
                            t.ApplyLogOnInfo(logOnInfo);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
    }

Opulence