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

问题描述：

我试图让内容从数据库上下文对象称为动态SQL存储过程的表，以填充 GridView控件（使用实体框架6.1.1）控制。我无法检索数据。

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 injectable 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＃code后面我再用来执行存储过程是：

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（）方法的例外，因为结果收益 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:

您需要导入存储过程作为一个功能。在你的实体模型工作区，点击右键并选择新增 - ＆GT;功能导入。

在添加功能导入对话框中，输入你希望你的存储过程来在你的模型，例如 Search_Products 被提到的名字，从下拉列表中选择你的程序，并选择过程的返回值为实体，然后选择产品从下拉列表中。

然后在后面的code：

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.

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

相关推荐