如何检索 Crystal Report 中使用的 SQL SELECT 语句? [英] How can I retrieve the SQL SELECT statement used in an Crystal Report?

问题描述

我目前正在使用 C# 编写一个程序，该程序允许我们的用户运行、查看和导出一批 Crystal Reports.这些报告是使用 Crystal Reports 2008 GUI 制作的.这样做的主要原因之一是允许我们在将 Crystal Report 导出为 PDF 时保留超链接.我的程序通过导出到 rtf，然后将 rtf 转换为 pdf 来做到这一点.如果有人知道在转换为 PDF 时保留超链接的不那么复杂的方法，我很想听听，但这不是我目前的问题.

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.

我已经做了很多关于如何优化我的程序以使导出时间尽可能短的测试.从我所见，让应用程序查询数据，然后将结果集绑定到 Crystal Report 是迄今为止最快的方法.我的问题是我无法将查询硬编码到程序中，它们需要从 Crystal Report 本身中检索.

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.

在 Crystal Reports 2008 中，数据库"菜单下有一个名为显示 SQL 查询"的选项.这将打开一个窗口，其中包含用于给定报告的 SQL 查询.这正是我需要从我的应用程序中获得的.我已经加载了一个水晶报表，并且在调试时遍历了 ReportDocument 对象试图找到查询，但没有运气.

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.

所以，我的问题是；是否有任何可用的方法可以让我提取给定 Crystal Report 使用的查询?

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

推荐答案

好的，所以 dotjoe 给了我解决这个问题所需的所有提示.以下代码可用于从水晶报表中提取命令文本.

Alright, so dotjoe gave me all of the hints that I needed to work this out. The following code can be used to pull the command text from a crystal report.

public string getCommandText(ReportDocument rd)
{
    if (!rd.IsLoaded)
        throw new ArgumentException("Please ensure that the reportDocument has been loaded before being passed to getCommandText");
    PropertyInfo pi = rd.Database.Tables.GetType().GetProperty("RasTables", BindingFlags.NonPublic | BindingFlags.Instance);
    return ((dynamic)pi.GetValue(rd.Database.Tables, pi.GetIndexParameters()))[0].CommandText;
}

它看起来有点乱，但是当你开始涉水它时它会有点意思.基本上，它使用反射来获取 CommandText 属性的值，并加入一些动态以通过 ReportDocument 中的动态属性.

It looks a bit messy, but it makes some sort of sense when you start wading through it. Basically, it uses reflection to get the value of the CommandText property, with a little dynamics thrown in to get past the dynamic properties in the ReportDocument.

这是为我提取命令文本，但我没有时间对代码进行任何测试.我敢肯定，一旦我有时间处理这个问题，我就会做一些调整.我不知道不使用SQL 命令"的报告会发生什么.进一步测试后，我会发表评论.

This is pulling the command text for me, but I have had no time to do any tests on the code. I'm sure I'll be making some tweaks once I've had the time to work with this. I have no clue what happens with reports that do not use "SQL Commands". I'll post a comment once I've tested this further.

• 斯科特

附:这要求您引用标准反射/动态库，以及以下 Crystal Report 库:

P.S. This requires that you reference the standard reflection/dynamic libraries, as well as the following Crystal Report libraries:

crystaldecisions.reportappserver.datadefmodel

crystaldecisions.reportappserver.datadefmodel

crystaldecisions.crystalreports.engine

crystaldecisions.crystalreports.engine

crystaldecisions.shared

crystaldecisions.shared

这篇关于如何检索 Crystal Report 中使用的 SQL SELECT 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！