如何在MVC中获取存储过程调用以返回数据？ [英] How do I get my stored procedure call in MVC to return data?
问题描述
我在Windows 10上的Visual Studio社区2017中运行SQL Server Express 2016，SSMS 2017和MVC6。
我无法让ReportViewer工作经过两周的尝试，我创建了一个存储过程，成功地填充了一个临时表，就像它应该在报告中显示一样。
作为测试，我然后创建了一个MVC索引视图，可以将临时表完美地显示为报表。但当然，这些数据是静态的。
我真正需要的是从存储过程末尾的临时表中的SELECT语句驱动索引视图。我在NeededDocsReportController索引ActionResult过程中有以下代码。它显示列标题很好，但没有数据。
在存储过程调用上面标出的行是显示17行数据的测试行临时表。即使我从该表中删除记录，存储过程也会成功重新填充临时表。不同之处在于，在MVC控制器中的下面的存储过程调用中，它不返回任何记录，而SSMS 2017中SQL查询窗口中的相同存储过程正确返回17条记录。
有没有明显的我做错了，或者我应该做什么，以获得MVC中出现的记录？谢谢。
我的尝试：
命名空间MVCwithDIYReport。控制器
{
公共类NeededDocsReportController：控制器
{
私有ReportDBContext db = new ReportDBContext（）; < br $>
// GET：NeededDocsReport
公共ActionResult索引（）
{
//返回View（db.NeededDocsReports.ToList（））;
返回视图（db.sp_docsstatus（true）.OfType< neededdocsreport>（）。ToList（））;
}
我会运行SQL事件探查器，并在调用db.sp_docsstatus（true）时检查正在触发的查询/调用。 OfType< neededdocsreport>（）。ToList（）
我会手动在DB上运行相同的查询并看到我仍然得到预期数据/行为。
我得到了存储过程调用wor通过使用ExecuteSqlCommand，如下面的控制器代码。我运行存储过程来检索，排序和加载数据到临时表中，组列只显示一次组（因为这是一个报告）。然后我以正常方式使用列表类型视图显示临时表。这对于我们凡人来说都是一个很好的解决方法，对于他们来说，ReportViewer上的培训视频和演练都不起作用。
// GET：NeededDocsReports
public ActionResult Index（）
{
//刷新报告数据
db.Database.ExecuteSqlCommand（ dbo.sp_docsstatus，false）;
//显示报告
返回视图（db.NeededDocsReport.ToList（））;
}
I'm running SQL Server Express 2016, SSMS 2017, and MVC6 in Visual Studio Community 2017 on Windows 10.
I was unable to get ReportViewer to work after two weeks of trying, so I created a stored procedure that successfully populates a temporary table exactly as it should be displayed in a report.
As a test, I then created an MVC Index view that displays temporary table perfectly as a report. But of course, that data is static.
What I really need is to drive the Index view from the SELECT statement FROM the temporary table at the end of the stored procedure. I have the following code in my NeededDocsReportController Index ActionResult procedure. It displays the column headers just fine, but no data.
The line that is remarked out above the stored procedure call is the test line that displays 17 rows of data from the temp table. Even if I delete records from that table, the stored procedure successfully re-populates the temp table. The difference is that in the stored procedure call below in the MVC controller, it returns no records, while the same stored procedure in an SQL Query window in SSMS 2017 correctly returns the 17 records.
Is there anything obvious that I am doing wrong, or that I should do, to get the records the come out in MVC? Thank you.
What I have tried:
namespace MVCwithDIYReport.Controllers
{
public class NeededDocsReportController : Controller
{
private ReportDBContext db = new ReportDBContext();
// GET: NeededDocsReport
public ActionResult Index()
{
//return View(db.NeededDocsReports.ToList());
return View(db.sp_docsstatus(true).OfType<neededdocsreport>().ToList());
}
I would run SQL profiler and check what query/call is being fired when you calldb.sp_docsstatus(true).OfType<neededdocsreport>().ToList()
The I would run the same query on DB manually and see still I am getting expected data/behavior.
I got the stored procedure call working by using ExecuteSqlCommand as in the controller code below. I ran the stored procedure to retrieve, sort, and load the data into a temporary table, with group columns showing groups only once (because this is a report). Then I display the temporary table in the normal way with a list-type view. It's a great workaround for us mere mortals for whom training videos and walkthroughs on ReportViewer don't work.
// GET: NeededDocsReports
public ActionResult Index()
{
//Refresh the report data
db.Database.ExecuteSqlCommand("dbo.sp_docsstatus", false);
//Display the report
return View(db.NeededDocsReport.ToList());
}
这篇关于如何在MVC中获取存储过程调用以返回数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
