In ADO.NET Entity Framework, how to utilize a stored procedure

.net c# entity-framework linq

Question

I create a stored method in ADO.NET Entity Framework for my three tables.

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 the following stored procedure function:

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

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

Function GetPersonalInformationWithDepartmanID There are three options when writing SQL (user defined function in ADO.NET Entity Framework), which is ridiculous. nonetheless, I have 3 joining tables. How do I utilize a join of three tables?

1
6
6/28/2009 6:09:50 PM

Accepted Answer

Okay, here are the actions you must take:

  • include your stored proceduresp_GetDepartmanData to your Entity Framework model (aside: we advocate calling your stored procedures using strongly rather than NOT).sp_(something) Utilizing thesp_ prefix is exclusively used for system stored procedures that are exclusive to Microsoft.)
  • Before using your stored procedure, you must first establish a conceptual entity for it since it returns a collection of data; in the Entity Designer, build a new entity and give it a meaningful name, such asDepartmentDataEntityType or anything; provide that entity type access to all the attributes the stored method returns
  • In the entity data model, you can now build your function import. Go to the model browser, choose your stored procedure in the "model.store" section, and then select "create function import" from the context menu.
  • You may now specify the function's return value and give it a name in the object context. In this example, choose the newly generated entity type (e.g.DepartmentDataEntityType from the top)
  • You've finished!

Now, your function import should look something like this:

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

You may now use this function on your object context to obtain the data from your database using the stored procedure.

Marc

Edit:

The entity you generated is not mapped to anything and is only ever used when the function import populates a collection of these entities, which is why you can receive a mapping error ("Error 3027: No mapping given for the following EntitySet/AssociationSet") after doing this. Either you need to convert this thing to a complicated type or find a way to transfer it to a data storage.

making a complicated type Just launch the EF designer and do a right-click on an empty space. Select Complex Type under Add. A new complex type ought to show up in the model browser. Similar to how you added attributes to your entity, right-click it and choose "Add Scalar Properties." Then, after deleting your entity, rename your complex type to reflect the name of the entity.

All you need to do is that.

6
4/21/2011 4:31:56 PM

Popular Answer

How is this "conceptual entity" made? The following error message appears if I create an entity that is not mapped to the database: "Entity type 'foobar' is not mapped to the database.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow