Crystal report and problem with connection

Question

This question to people who have experience with CR.

I have problem with set connection to Crystal report. I have received report from my customer.The database's struct is the same on his and on my server. But the name of server and db is different. He is using "Command" for creating report (Add command in Database fields->Database Expert...). This command has some function for returning data. I try to run this report on my computer and I have a problem when I try to execute TestConnectivity().This method return false. I try to debug and I found that after applying ApplyLogOnInfo() internal object RasTable has old ConnectionInfo.

I'm using the next code for set connection:

            private void ApplyConnection(ReportDocument report, ConnectionInfo connectionInfo)
    {
        ApplyLogOnInfo(report, connectionInfo);
        ApplyLogOnInfoForSubreports(report, connectionInfo);
    }

    private void ApplyLogOnInfo(ReportDocument reportDocument, ConnectionInfo connectionInfo)
    {
        foreach (Table table in reportDocument.Database.Tables)
        {
            table.LogOnInfo.ConnectionInfo.AllowCustomConnection = true;
            TableLogOnInfo tableLogonInfo = table.LogOnInfo;
            tableLogonInfo.ConnectionInfo = connectionInfo;
            table.ApplyLogOnInfo(tableLogonInfo);

            _log.InfoFormat("Table connection state: TableName = {0}, IsConnect = {1}", table.Name, table.TestConnectivity());
        }
    }

    private void ApplyLogOnInfoForSubreports(ReportDocument reportDocument, ConnectionInfo connectionInfo)
    {
        Sections sections = reportDocument.ReportDefinition.Sections;
        foreach (Section section in sections)
        {
            ReportObjects reportObjects = section.ReportObjects;
            foreach (ReportObject reportObject in reportObjects)
            {
                _log.InfoFormat("Type = {0}, Name = {1}",reportObject.Name, reportObject.Kind);
                if (reportObject.Kind == ReportObjectKind.SubreportObject)
                {
                    var subreportObject = (SubreportObject)reportObject;
                    ReportDocument subReportDocument = subreportObject.OpenSubreport(subreportObject.SubreportName);
                    ApplyLogOnInfo(subReportDocument, connectionInfo);
                }
            }
        }
    }

So my questions are:

• How can I set up right connection to Command?
• Why cann't I change connection? (if report was created on other server).

Answer 1

I used to face the same problem a few years back and then wrote a class. Here is the class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace ReportExportDemo 
{ 
    class Reports 
{ 
    static TableLogOnInfo crTableLogonInfo; 
    static ConnectionInfo crConnectionInfo; 
    static Tables crTables; 
    static Database crDatabase; 

    public static void ReportLogin(ReportDocument crDoc, string Server, string Database, string UserID, string Password) 
    { 
        crConnectionInfo = new ConnectionInfo();
        crConnectionInfo.ServerName = Server;
        crConnectionInfo.DatabaseName = Database;
        crConnectionInfo.UserID = UserID;
        crConnectionInfo.Password = Password;
        crDatabase = crDoc.Database; crTables = crDatabase.Tables;

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

    public static void ReportLogin(ReportDocument crDoc, string Server, string Database)
    {
        crConnectionInfo = new ConnectionInfo();
        crConnectionInfo.ServerName = Server;
        crConnectionInfo.DatabaseName = Database;
        crConnectionInfo.IntegratedSecurity = true;
        crDatabase = crDoc.Database; crTables = crDatabase.Tables;

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

Just in case if you want to know how to use read more here: http://midnightprogrammer.net/post/Passing-Parameters-and-Loading-Crystal-Report-Programmatically.aspx

Answer 2

You just need to add ref word in the method definitions

as below

    private void ApplyConnection(ref ReportDocument report, ConnectionInfo connectionInfo)
{
    ApplyLogOnInfo(report, connectionInfo);
    ApplyLogOnInfoForSubreports(report, connectionInfo);
}

private void ApplyLogOnInfo(ref ReportDocument reportDocument, ConnectionInfo connectionInfo)
{
    foreach (Table table in reportDocument.Database.Tables)
    {
        table.LogOnInfo.ConnectionInfo.AllowCustomConnection = true;
        TableLogOnInfo tableLogonInfo = table.LogOnInfo;
        tableLogonInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogonInfo);

        _log.InfoFormat("Table connection state: TableName = {0}, IsConnect = {1}", table.Name, table.TestConnectivity());
    }
}

private void ApplyLogOnInfoForSubreports(ref ReportDocument reportDocument, ConnectionInfo connectionInfo)
{
    Sections sections = reportDocument.ReportDefinition.Sections;
    foreach (Section section in sections)
    {
        ReportObjects reportObjects = section.ReportObjects;
        foreach (ReportObject reportObject in reportObjects)
        {
            _log.InfoFormat("Type = {0}, Name = {1}",reportObject.Name, reportObject.Kind);
            if (reportObject.Kind == ReportObjectKind.SubreportObject)
            {
                var subreportObject = (SubreportObject)reportObject;
                ReportDocument subReportDocument = subreportObject.OpenSubreport(subreportObject.SubreportName);
                ApplyLogOnInfo(subReportDocument, connectionInfo);
            }
        }
    }
}