Crystal Reports for Visual Studio: 2 useful C# functions

When I first started working with Crystal Reports, what I found troubling was that when you connect to a database and select a table to use in your report (or for that matter any stored procedure), you provide Crystal Reports with the connection information which is then stored in some hidden place within the RPT files. The reason that this was troubling to me was that there did not seem to be any way to point to have different databases for development and production.

Sure, I tried the "SetDatabaseLogon" that is available on ReportDocument within the CrystalReportSource object - but that never seemed to work. After a lot of frustration, what I ended up doing was creating data-sets and then creating reports using the data-sets. Then during run-time, I had a method that would return the data-set from the appropriate database (dev or production), which was then set into the report using "SetDataSource" that is also available on on the ReportDocument object in the CrystalReportSource object.

By setting the DataSource for the CrystalReport, I was able to get a solution where depending on what mode I was running the application - I was able to get the data from the development, test or production databases.

This solution worked fine and dandy for a while, until I came across a complex report that had a sub-report embedded inside it. The sub-report had to be run once for every record in the main report. This is something that cannot be done using the DataSource method, because, even though I could give CR the main report's data-source, I would not be able to provide a data-source for each sub-report that needed to be run.

This time while looking for a solution, I found out 2 things:

1. The reason SetDatabaseLogon does not work is that it only allows you to change the user-id and password. The four parameter version will not change the database and server names: It will change the user-id and password for all those objects that share the database and server name passed in as part of the arguments. (This information is unfortunately buried in the remarks part of the documentation).
2. To change the database that is being used one needs to use the "ApplyLogOnInfo" on each table (CrystalDecisions.CrystalReports.Engine.Table) that is in the CrystalReport object.

So here is my first useful C# function for working with CrystalReports. It is used to change the database used by CrystalReports to get its data. (use it when you need to use separate development, test and production servers as your data-source for CrystalReports)

public static void SetCRLogOnInfo(ReportDocument mainInRD, string dataSource, string userId, string pwd)
    {
        //do the main reports database
        TableLogOnInfo logonInfo = null;
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in mainInRD.Database.Tables)
        {
            logonInfo = table.LogOnInfo;
            logonInfo.ConnectionInfo.ServerName = dataSource;
            logonInfo.ConnectionInfo.DatabaseName = "";
            logonInfo.ConnectionInfo.UserID = userId;
            logonInfo.ConnectionInfo.Password = pwd;
            table.ApplyLogOnInfo(logonInfo);
        }
        try
        {
            //now update logon info for all sub-reports
            if (!mainInRD.IsSubreport && mainInRD.Subreports != null && mainInRD.Subreports.Count > 0)
            {
                foreach (ReportDocument rd in mainInRD.Subreports)
                {
                    SetCRLogOnInfo(rd, dataSource, userId, pwd);
                }
            }
        }
        catch
        {
        }
    }

The second method is used to export the CrystalReport as a PDF file. The reason it takes a HTTPResponse, is that this snippet is used in a ASP.Net application and it outputs the PDF to the browser.

public static void ExportToPDF(CrystalReportSource crs, HttpResponse response)
{
    try
    {
        response.Buffer = false;
        response.ClearContent();
        response.ClearHeaders();
        response.ContentType = "application/pdf";
        
        Stream pdfStreamFromCR = crs.ReportDocument.ExportToStream(ExportFormatType.PortableDocFormat);
        byte[] outputBuffer = new byte[(int)pdfStreamFromCR.Length];
        pdfStreamFromCR.Read(outputBuffer, 0, (int)pdfStreamFromCR.Length);
        response.BinaryWrite(outputBuffer);
        response.Flush();
        response.End();
    }
    catch(ThreadAbortException)
    {
        //expected do nothing
    }
    catch (Exception ex)
    {
        //Log4Net
    }
}

(updated on 1/12/2008)