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

问题描述

我有3张桌子我在ADO.NET实体框架中编写一个存储过程。

  ALTER PROCEDURE [dbo]。[sp_GetDepartmanData] 
（@departman nvarchar（50））
 BEGIN 
 SELECT 
 d.ID，d.Name作为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 
  

---

我需要一个我在下面写的存储过程函数：

  var staffPersonel = staffContext.GetPersonelInformationWithDepartmanID（Yazılım ）; 
 
 gvPersonel.DataSource = staffPersonel; 
 gvPersonel.DataBind（）; 
  

GetPersonelInformationWithDepartmanID函数我从SQL（ADO.NET Entity Framework中的用户定义函数）写入有3个选项（这是愚蠢的!!!）但我有3个联合表!!!

解决方案

好的，你需要几步：

• 将您的存储过程 sp_GetDepartmanData 添加到您的实体框架模型
  （除此之外 - 强烈推荐不来调用您的存储过程 sp_（something） - 使用由于您的存储过程返回一组数据，因此您将需要创建一个概念实体，以便为Microsoft系统存储过程保留。

• 首先，在你可以使用你的存储过程之前;在实体设计器中，创建一个新实体，并将其称为一些有用的名称，如 DepartmentDataEntityType 或某事;将所有从存储过程返回的字段添加到该实体类型


• ，您可以在实体数据模型中创建函数导入 - 转到模型浏览器，在模型中。存储部分转到您的存储过程，然后右键单击创建函数导入


• 现在，您可以在对象上下文中给出您的函数名称并定义其返回的内容 - 在此你可以选择你新创建的实体类型（例如 DepartmentDataEntityType ）


• 你完成了！

您现在应该有一个函数导入，如：

  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）; 
} 
  

现在可以通过该对象上下文来调用该函数来检索数据存储过程从您的数据库。

Marc

编辑：

如果您收到映射错误（错误3027：没有为以下EntitySet / AssociationSet指定映射），那是因为您创建的实体未映射到任何内容，仅当功能导入填充这些实体的集合时才使用。您需要将该实体映射到数据存储，或者您需要将其更改为复杂类型。

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

这就是你所要做的：）

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 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:

• 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.

Marc

Edit:

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.

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.

That's all you have to do :)

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