使用包含动态 SQL 的存储过程在 C# 中创建报表(报表查看器) [英] Using a stored procedure containing dynamic SQL to create a report in C# (Report viewer)

问题描述

我有一个 SQL Server 存储过程，我想用它在我的应用程序 (c#) 中创建报告.

I have a SQL Server stored procedure that I want to use to create a report in my application (c#).

因为过程中的表是动态创建的，所以我无法使用它来创建报告.

Because the table in the procedure is dynamically created I am having trouble using it to create a report.

如何使用此过程在我的应用程序中创建报告?

How can I use this procedure to create a report in my application?

我使用的是 VS2010、SQL Server 2008 R2

I am using VS2010, SQL Server 2008 R2

我在 VS 中创建了一个数据集，但不确定如何从那里使用它.报表向导无法使用我创建的数据集.

I created a dataset in VS but not sure how to use it from there. The report wizard cannot use the dataset I created.

我曾想以某种方式使用它来创建一个视图，然后从中生成一个数据集，但无法弄清楚.

I gave thought to somehow using this to create a view and then generate a dataset from that but could not figure that out.

我希望我提供了足够的信息，让别人给我一些指导.

I hope I have provided enough info for someone to give me some direction.

PROCEDURE sp_RD_Reporting_View_ExportedData_For_Period    
(   
@StartDate date,   
@EndDate date   
)  
AS  
CREATE TABLE #tmp  
(  
    StartDate DATETIME,  
    EndDate DATETIME,  
    FomatedDate VARCHAR(20)  
)  
--Calculate the date ranges   
;WITH Nbrs ( n ) AS (  
        SELECT 0 UNION ALL  
        SELECT 1+n FROM Nbrs WHERE n < 6 )  
INSERT INTO #tmp  
SELECT  
    DATEADD(DAY,-n,@StartDate),  
    DATEADD(DAY,-n,@EndDate),  
    convert(varchar, DATEADD(DAY,-n,@EndDate), 110)  
FROM  
    Nbrs  
ORDER BY  
    -n  
--The date columns for the pivot  
DECLARE @cols VARCHAR(MAX)  
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME(FomatedDate),  
                     QUOTENAME(FomatedDate))    
FROM   
    #tmp  
--Declaring some dynamic sql and executing it  
DECLARE @query NVARCHAR(4000)=  
N'SELECT  
    *  
FROM  
(  
    SELECT   ExportData.EmployeeID AS EmpID,  
            ABRAempInfo.EmpFullName AS Name,  
            ExportData.PayType AS PayType,   
            tmp.FomatedDate,   
            SUM(ExportData.Quantity) AS Hours   
    FROM ExportData   
    JOIN ABRAempInfo    
    ON ExportData.EmployeeID = ABRAempInfo.EmpID  
    JOIN #tmp AS tmp  
    ON ExportData.ChargeDate = tmp.FomatedDate  
    WHERE ChargeDate BETWEEN tmp.StartDate AND tmp.EndDate  
    GROUP BY ExportData.EmployeeID, ExportData.PayType, tmp.FomatedDate,   ABRAempInfo.EmpFullName  

) AS p  
PIVOT  
(  
    SUM(Hours)  
    FOR FomatedDate IN ('+@cols+')  
) AS pvt'  

EXECUTE(@query)  
DROP TABLE #tmp  

推荐答案

我遇到了一些我认为类似的事情 - 试图让 Linq to Sql 从使用动态 SQL 进行选择的存储过程中构建结果集结果.以下是我解决我的问题的方法……这在很大程度上是一种解决方法，但您将无法真正获得向导来处理动态结果集.另请注意，如果您的动态 sql 返回不同的列，则它不太可能起作用.

I have run into something that I think is similar - trying to get Linq to Sql to build me a result set from a stored proc that uses Dynamic SQL to select results. Here is how I solved my issue... it is very much a work around, but you won't really be able to get a wizard to work off a dynamic result set. Also note, that if your dynamic sql returns differing columns, it is not likely to work.

1. 运行你的动态语句并只输出 SQL 语句(打印语句，不要执行它)
2. 修改您的存储过程以通过您从 #1 获得的 sql 语句返回输出
3. 您现在拥有一个标准的存储过程 - 使用它来构建您的报告.
4. 在您的报告测试并正常工作后，再次修改您的存储过程并重新插入动态 sql.

这篇关于使用包含动态 SQL 的存储过程在 C# 中创建报表(报表查看器)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！