使用实体框架从存储过程获取数据

I am trying to get the content a table with a dynamic SQL stored procedure called from the database context object (using Entity Framework 6.1.1), in order to populate a GridView control. I fail to retrieve the data.

我正在尝试从数据库上下文对象(使用Entity Framework 6.1.1)中调用动态SQL存储过程来获取内容，以便填充GridView控件。我找不到数据。

Here's the stored procedure. It is for a student demonstration about SQL injection in stored procedures, so I KNOW this is injectable and it's fine.

这是存储过程。这是一个关于存储过程中的SQL注入的学生演示，所以我知道这是可注入的，这很好。

ALTER PROCEDURE dbo.SearchProducts
  @SearchTerm VARCHAR(max)
AS
BEGIN
  DECLARE @query VARCHAR(max)
  SET @query = 'SELECT * FROM dbo.Products WHERE Name LIKE ''%' + @SearchTerm + '%'''
  EXEC(@query)
END

The C# code behind I then use to execute the stored procedure is :

我后面用来执行存储过程的c#代码是:

var db = new MyEntities();
var TEST_SEARCH_TERM = "product";
var result = db.SearchProducts(TEST_SEARCH_TERM);

MyGridView.DataSource = result;
MyGridView.DataBind();

When executed, in the Database Explorer in Visual Studio, the stored procedure works fine. But when executed in the running ASP.NET app, I get an exception in the DataBind() method because result returns -1 instead of an IEnumerable DataSet containing the objects resulting from the stored procedure's SELECT.

在Visual Studio的Database Explorer中执行时，存储过程可以正常工作。但是当在运行的ASP中执行时。我在DataBind()方法中得到一个异常，因为结果返回-1，而不是包含存储过程的SELECT结果的IEnumerable的数据集。

How can I retrieve the data and populate my GridView?

如何检索数据并填充GridView?

4 个解决方案

#1

Use the following steps to solve this issue:

使用以下步骤来解决这个问题:

You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.
您需要将存储过程作为函数导入。右键单击实体模型的工作区区域，选择Add ->函数导入。
In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for example Search_Products, choose your procedure from the drop down list, and choose the return value of the procedure to be Entities and choose Products from the drop down list.
在添加函数导入对话框中,输入您想要您的存储过程的名称例如Search_Products在您的模型,从下拉列表中选择你的过程,选择实体和程序的返回值从下拉列表中选择产品。

Then in the code behind:

然后在后面的代码中:

var db = new MyEntities();
var TEST_SEARCH_TERM = "product";
var result = db.Search_Products(TEST_SEARCH_TERM);//Search_Products is the name that you specified in Function Import dialog

MyGridView.DataSource = result;
MyGridView.DataBind();

The reason that you get -1 for result is that Entity Framework cannot support Stored Procedure Return values out of the box. I think support of stored procedure return values depends on version of Entity framework. Also Entity Framework doesn't have rich stored procedure support because its an ORM, not a SQL replacement.

结果得到-1的原因是实体框架不支持存储过程返回值。我认为支持存储过程返回值取决于实体框架的版本。同样，实体框架也没有丰富的存储过程支持，因为它是ORM，而不是SQL替换。

#2

I have come across this before with stored procedures using dynamic SQL. I have had success using complex types if I add the line 'SET FMTONLY OFF;' (see https://msdn.microsoft.com/en-us/library/ms173839.aspx) to the top of my stored procedure before it is added to the EF model. Once you have your model setup with your complex type, be sure to remove this line.

我以前遇到过使用动态SQL的存储过程。如果我将“SET FMTONLY OFF;”(请参阅https://msdn.microsoft.com/en-us/library/ms173839.aspx)这一行添加到存储过程的顶部，然后将其添加到EF模型中，那么我已经成功地使用了复杂类型。在使用复杂类型建立模型之后，请确保删除这一行。

Example:

例子:

ALTER PROCEDURE dbo.SearchProducts
  @SearchTerm VARCHAR(max)
AS
BEGIN
  SET FMTONLY OFF;
  DECLARE @query VARCHAR(max)
  SET @query = 'SELECT * FROM dbo.Products WHERE Name LIKE ''%' + @SearchTerm + '%'''
  EXEC(@query)
END

#3

Verify that your EDMX has a return type: Go to Function Imports --> SearchProducts, and double click it.

验证您的EDMX有一个返回类型:转到函数导入——>搜索产品，双击它。

In order to utilize a Complex return type, Entity Framework will require that you explicitly define column names in your stored procedure instead of using *.

为了使用复杂的返回类型，实体框架将要求您在存储过程中显式地定义列名，而不是使用*。

Once your stored procedure is modified to define the column names, you can update your model in the project. (Note, performing a complete drop of the SP, and then adding it back to your edmx may be the best route.)

在修改存储过程以定义列名之后，可以在项目中更新模型。(注意，执行SP的完整删除，然后将其添加回您的edmx可能是最好的方法。)

EDIT

编辑

Maybe you can modify your SP like the following:

也许你可以修改你的SP如下:

ALTER PROCEDURE dbo.SearchProducts
  @SearchTerm VARCHAR(max)
AS
BEGIN
  SELECT * FROM dbo.Products WHERE Name LIKE '%' + @SearchTerm + '%'
END

#4

You seem to have sorted your problem out, there is official documentation from Microsoft available at the links below:

您似乎已经解决了您的问题，以下链接有来自微软的官方文档:

How to import a stored procedure into your entity data model: https://msdn.microsoft.com/en-us/library/vstudio/bb896231(v=vs.100).aspx

如何将存储过程导入实体数据模型:https://msdn.microsoft.com/en-us/library/vstudio/bb896231(v=vs.100).aspx

Complex types in the EF designer: https://msdn.microsoft.com/en-gb/data/jj680147.aspx

EF设计器中的复杂类型:https://msdn.microsoft.com/en-gb/data/jj680147.aspx

Make sure you are working with the latest version of .net and you keep your model up to date when you make changes to your database.

确保您使用的是最新版本的。net，当您对数据库进行更改时，您可以使您的模型保持最新状态。

#1