如何使用ADO.NET实体框架的存储过程 [英] How to use a stored procedure in ADO.NET Entity Framework

问题描述

我有3个表;我写的ADO.NET实体框架的存储过程。

I have 3 tables; I write a stored procedure in ADO.NET Entity Framework.

ALTER PROCEDURE [dbo].[sp_GetDepartmanData]
(@departman nvarchar(50))
BEGIN
  SELECT  
    d.ID, d.Name as DepartmanName,  
    sb.Salary, sb.email,
    sp.Name, sp.SurName, sp.Phone, sp.Married, sp.Address
  FROM         
    Departman d 
  INNER JOIN StaffsBusiness sb ON d.ID = sb.StaffsPersonelDepartmanID
  INNER JOIN StaffsPersonel sp ON sb.StaffsPersonelID = sp.ID 
  WHERE
    d.Name = @departman
END

---

---

我需要一个存储过程，函数我写如下：

I need a stored procedure function I write below:

var staffPersonel = staffContext.GetPersonelInformationWithDepartmanID("Yazılım");

gvPersonel.DataSource = staffPersonel;
gvPersonel.DataBind();

GetPersonelInformationWithDepartmanID功能，从SQL（在ADO.NET实体框架用户自定义函数）写有3个选择（这是愚蠢的！），但我有3个joininig表！。我该如何使用，如果我加入3表之前？

GetPersonelInformationWithDepartmanID function I write from SQL (user defined function in ADO.NET Entity Framework) there are 3 alternative (it is silly!!!) but i have 3 joininig table!!!. How can i use if i join 3 table before?

推荐答案

好了，你需要几个步骤，在这里：

Okay, you need a few steps here:

• 添加存储过程 sp_GetDepartmanData 您的实体框架模型 （顺便说一句 - 这是强烈推荐不会即可调用存储过程以sp_（东西） - 使用 SP _ preFIX保留给微软只有系统存储过程）
• 因为你的存储过程返回一组数据，您需要先创建一个概念实体为它之前，你可以使用你的存储过程;在实体设计中，创建一个新的实体，并调用它像 DepartmentDataEntityType 什么的一些有用的名称;所有字段添加正从存储过程返回到实体类型
• 现在，你可以创建实体数据模型的导入功能 - 到模型浏览器中，model.store部分中去你的存储过程，并在创建函数导入
• 您现在可以给对象上下文中的函数的名称，定义什么返回 - 在这种情况下，选择你的新创建的实体类型（例如： DepartmentDataEntityType 从上面）
• 您就大功告成了！

• add your stored procedure sp_GetDepartmanData to your Entity Framework model (as an aside - it's is strongly recommend NOT to call your stored procedures sp_(something) - use of the sp_ prefix is reserved for Microsoft-only system stored procedures)
• since your stored procedure is returning a set of data, you will need to create a conceptual entity for it first, before you can use your stored proc; in the Entity Designer, create a new entity and call it some useful name like DepartmentDataEntityType or something; add all the fields being returned from the stored procedure to that entity type
• now, you can create your function import in the entity data model - go to the model browser, in the "model.store" section go to your stored procedure, and right-click on "create function import"
• you can now give your function in the object context a name and define what it returns - in this case, pick your newly created entity type (e.g. DepartmentDataEntityType from above)
• you're done!

您现在应该有一个函数导入是这样的：

You should now have a function import something like:

public global::System.Data.Objects.ObjectResult<DepartmentDataEntityType> GetPersonelInformationWithDepartmanID(global::System.String departmentName)
{
    global::System.Data.Objects.ObjectParameter departmentNameParameter;

    departmentNameParameter = new global::System.Data.Objects.ObjectParameter("departmentNameParameter", departmentName);

    return base.ExecuteFunction<DepartmentDataEntityType>("sp_GetDepartmanData", departmentNameParameter);
}

你的对象范围内。此功能现在可以调用从数据库中检索通过存储过程中的数据。

This function on your object context can now be call to retrieve the data via the stored procedure from your database.

马克·

编辑：

如果你得到一个映射错误（错误3027：无以下的EntitySet / AssociationSet指定映射），这样做了以后，那是因为你创建的实体未映射到任何东西，只使用过的功能导入时，填充这些实体的集合。你要么需要映射这个实体的数据存储在某种程度上，或者您需要将其更改为一个复杂类型。

If you are getting a mapping error ("Error 3027: No mapping specified for the following EntitySet/AssociationSet") after doing this, it's because the entity you created is not mapped to anything and is only ever used when the function import populates a collection of these entities. You either need to map this entity to a data store somehow or you need to change it to a complex type.

要创建一个复杂类型只需打开了EF设计师，上的空白区域单击鼠标右键。去加入>复杂类型。您应该看到在模型浏览器一个新的复杂类型出现。右键单击它并添加类似于你如何添加属性的实体标量的属性。然后删除您的实体和重命名你的复杂类型相同的实体。

To create a complex type simply open up the EF designer and right-click on an empty area. Go to Add > Complex Type. You should see a new complex type appear in the model browser. Right click it and add scalar properties similar to how you added properties to your entity. Then delete your entity and rename your complex type the same as the entity.

这就是你需要做的：）

这篇关于如何使用ADO.NET实体框架的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！