运行存储过程并获得动态结果 [英] Run Stored Proc and get dynamic results

问题描述

场景是我希望能够在数据库中运行数据报告 - 这些报告采用存储过程（SP）的形式。我希望能够在用户需要时创建新的SP，而无需重建/发布项目。 日期（报告）
返回只需要为用户输出.csv。

The scenario is that I want to be able to run reports on data in a database - these are in the form of Stored Procedures (SP). I want to be able to create new SP when the user requires without having to rebuild/publish the project.  The date (report) return simply needs to output to .csv for the user.

到目前为止，我已经创建了一个包含ID，报告名称，报告描述和名称的表存储过程。 这将允许用户选择他们希望运行的报告，并通过向表格添加详细信息来添加新报告。

So far I have created a table containing ID, Report Name, Report Description and name of Stored Proc.  This will allow the users to select the report they wish to run and me to add new reports by simply adding details to the table.

所以，我有3个主要问题：

So, I have 3 main question:

1）运行所需SP的最佳/最简单方法是什么。 我正在使用Entity Framework，所以我在项目中有数据库，但显然不是SP - 我考虑过只有一个SP，
可以包含在运行相关SP的dbml中：

1) What's the best/easiest way to run the required SP.  I am using Entity Framework, so I have the DB in the project but obviously not the SP - I have considered having a single SP that could be included in the dbml that runs the relevant SP:

IF @ReportID = 1
	BEGIN
		EXEC pReport1
	END	
ELSE IF @ReportID = 2

etc........

2）从SP返回的数据包含未知数量的列。 数据都可以视为字符串。 如何最好地处理退货？

2) The data returned from the SP contain unknown number of columns.  The data can all be treated as strings.  How best do I handle the return?

3）如何最好地将csv结果输出给用户（此步骤可能与步骤2合并？）。

3) How best do I output the csv results to the user (this step may well merge with step 2?).

提前感谢任何指针或建议：）

Thanks in advance for any pointers or advice :)

推荐答案

只需使用ADO.NET运行存储过程，并获得DataReader。 您可以检查DataReader的列以创建CSV。 参见eg：  https://stackoverflow.com/questions/29561340/datareader-to -csv-with-column-names

这篇关于运行存储过程并获得动态结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！