C# – How to retrieve the SQL SELECT statement used in an Crystal Report

ccrystal-reportscrystal-reports-2008net

I am currently working on a program in C# that allows our users to run, view and export a batch of Crystal Reports. The reports were made using the Crystal Reports 2008 GUI. One of the main reasons for doing this is to allow us to preserve hyperlinks when the Crystal Report is exported to PDF. My program does this by exporting to rtf, then converting the rtf to a pdf. If anyone knows of a less convoluted method of preserving hyperlinks when converting to PDf I'd love to hear it, but that's not my current question.

I have done a lot of tests on how to optimize my program to make the export take as little time as possible. From what I've seen, having the application query for the data, then binding the resultset to the Crystal Report is by far the fastest method. My problem is that I can't hard-code the queries into the program, they need to be retreived from the Crystal Report itself.

In Crystal Reports 2008, there is an option called "Show SQL Query" under the Database menu. This brings up a window with the SQL query used for the given report. This is exactly what I need to get my hands on from within my application. I've loaded a crystal report and, while debugging, traversed the ReportDocument object trying to find the query, but no luck.

So, my question is; is there any method available that would allow me to pull out the query used by a given Crystal Report?

Best Answer

I realize this is a very old question, but thought I would offer an alternative for those who stumble across this but need a framework target of 3.5 (dynamic is not available in 3.5).

You will need the following references for this solution to work.

using CrystalDecisions.ReportAppServer.DataDefModel;
using CrystalDecisions.CrystalReports.Engine;

Then just access the ClientDoc interface with the following and return a list of Command Text strings.

    private static List<string> GetCommandText(CrystalDecisions.CrystalReports.Engine.ReportDocument report)
    {
        var rptClientDoc = report.ReportClientDocument;
        return rptClientDoc.DatabaseController.Database.Tables.OfType<CommandTable>()
              .Select(cmdTbl => cmdTbl.CommandText).ToList();
    }
Related Topic