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

问题描述：

我正在尝试使用从数据库上下文对象(使用实体框架 6.1.1)调用的动态 SQL 存储过程获取表的内容，以便填充 GridView 控件.我无法检索数据.

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.

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

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

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

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

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

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

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

在 Visual Studio 的数据库资源管理器中执行时，存储过程工作正常.但是当在正在运行的 ASP.NET 应用程序中执行时，我在 DataBind() 方法中得到一个异常，因为 result 返回 -1 而不是一个IEnumerable DataSet 包含由存储过程的 SELECT 产生的对象.

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.

如何检索数据并填充我的 GridView?

How can I retrieve the data and populate my GridView?

答

使用以下步骤解决此问题:

Use the following steps to solve this issue:

您需要将存储过程作为函数导入.右键单击实体模型的工作区，然后选择 Add ->函数导入.
在添加函数导入"对话框中，输入您希望在模型中引用您的存储过程的名称，例如 Search_Products，从下拉列表中选择您的过程，然后选择返回值将过程设置为 Entities，然后从下拉列表中选择 Products.
然后在后面的代码中:

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.
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.
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();

您获得 -1 结果的原因是实体框架无法支持开箱即用的存储过程返回值.我认为对存储过程返回值的支持取决于实体框架的版本.此外，实体框架没有丰富的存储过程支持，因为它是一个 ORM，而不是 SQL 替代品.

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.

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

相关推荐