通过Visual Studio C#在Crystal Report中编写自定义查询 [英] Write a custom query in Crystal Report by visual studio c#

问题描述

我想创建一个从3个MySQL表中获取值的Crystal报表，但是我想编写我的自定义查询，因为当我在Visual Studio中选择表时，它显示的查询不符合我的期望.我在本地主机phpMyAdmin上测试了查询，并且该查询正常工作.

I want to create a Crystal report that takes values from 3 MySQL tables but I want to write my custom query because when I select tables in visual studio it shows a query that doesn't meet what I expected. I tested query on localhost phpMyAdmin and it worked.

注释1:我正在使用Visual Studio 2019并安装了Crystal Report SP26注意2:当Visual Studio写查询时，它在查询栏中添加"1".到表名像boq_table一样设为boq_table1.

note 1: I am using visual studio 2019 and installed Crystal report SP26 note 2: when visual studio wrote the query it added "1" to the table name like boq_table make it boq_table1.

下面的代码表示我要执行的查询:

code below represents what query I want to execute:

SELECT
    ubc.boq_table.itemNum,
    ubc.boq_table.descriptionOfWork,
    ubc.boq_table.unit,
    ubc.boq_table.contractualQuantity,
    ubc.boq_table.priceNum,
    ubc.summary.executedQuantLastSummary,
    ubc.summary.priceLastWorks,
    ubc.summary.executedQuantBetw2Sum,
    ubc.submittal.priceCurrentWorks
FROM
    ubc.boq_table
LEFT OUTER JOIN ubc.summary ON
    ubc.boq_table.itemNum = ubc.summary.itemNum
LEFT OUTER JOIN ubc.submittal ON
    ubc.boq_table.itemNum = ubc.submittal.itemNum
WHERE
    ubc.boq_table.projectName ='proj'
UNION
SELECT
    ubc.boq_table.itemNum,
    ubc.boq_table.descriptionOfWork, 
    ubc.boq_table.unit, 
    ubc.boq_table.contractualQuantity, 
    ubc.boq_table.priceNum, 
    ubc.summary.executedQuantLastSummary, 
    ubc.summary.priceLastWorks, 
    ubc.summary.executedQuantBetw2Sum, 
    ubc.submittal.priceCurrentWorks 
FROM
    ubc.summary
LEFT OUTER JOIN ubc.boq_table ON 
    ubc.summary.itemNum = ubc.boq_table.itemNum 
LEFT OUTER JOIN ubc.submittal ON 
    ubc.summary.itemNum = ubc.submittal.itemNum 
WHERE 
    ubc.summary.projectName = 'proj'  
UNION
SELECT
    ubc.boq_table.itemNum,
    ubc.boq_table.descriptionOfWork,
    ubc.boq_table.unit,
    ubc.boq_table.contractualQuantity,
    ubc.boq_table.priceNum,
    ubc.summary.executedQuantLastSummary, 
    ubc.summary.priceLastWorks, 
    ubc.summary.executedQuantBetw2Sum, 
    ubc.submittal.priceCurrentWorks 
FROM
    ubc.submittal 
LEFT OUTER JOIN ubc.boq_table ON 
    ubc.submittal.itemNum = ubc.boq_table.itemNum 
LEFT OUTER JOIN ubc.summary ON 
    ubc.submittal.itemNum = ubc.summary.itemNum 
WHERE 
    ubc.submittal.projectName = 'proj' 
ORDER BY 
    itemNum;

这是我选择表格后由Visual Studio编写的代码

and this code was written by the visual studio after I selected tables

 SELECT boq_table1.itemNum, boq_table1.descriptionOfWork, boq_table1.unit, boq_table1.contractualQuantity, boq_table1.priceNum, submittal1.priceCurrentWorks, summary1.executedQuantLastSummary, summary1.executedQuantBetw2Sum, summary1.priceLastWorks
 FROM   (ubc.boq_table boq_table1 INNER JOIN ubc.submittal submittal1 ON boq_table1.ID=submittal1.ID) INNER JOIN ubc.summary summary1 ON boq_table1.ID=summary1.ID
 ORDER BY boq_table1.itemNum

我无法编辑的最后一个只读代码

the last code read-only I can't edit

推荐答案

如果要获取数据到晶体副本，您可以做两件事，再次在数据库选择器中查找，可以选择添加命令或使用连接器和数据读取器以构建可以使用的数据集.

You can do 2 things if you want to get data to crystal reprots, you look again in the database selector, there is an option for adding a command or use the connector and a datareader to build a dataset that you can use.

如果您不需要大量计算数据，那么首先可以.

The first is ok, if you don't need to compute the data heavily.

这篇关于通过Visual Studio C#在Crystal Report中编写自定义查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！