通过

ADO.NET Entity Framework : Working with Stored Procedure

  • 项目

Here we go, I generally get questions from developers that if they will have to scrub the existing powerful stored procedure they have written by investing time and money. The answer to that is “NO”. You must enjoy using it as it has its own power.

So it is very simple in ADO.NET Entity Framework. Little bit of background before we start,

We need a table

CREATE TABLE [dbo].[Emp](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

There will be three stored procedures, insert/update/delete

Insert

++++

ALTER PROCEDURE [dbo].[InsertEmp]

(

@Name VARCHAR(50)

)

AS

BEGIN

INSERT INTO Emp(Name) VALUES(@Name)

END

Update

+++++

ALTER PROCEDURE [dbo].[UpdateEmp]

(

@Id int,

@Name VARCHAR(50)

)

AS

UPDATE Emp SET [Name] = @Name WHERE ID = @Id

RETURN

Delete

++++

ALTER PROCEDURE [dbo].[DeleteEmp]

(

@Id int

)

AS

DELETE Emp WHERE ID = @Id

Now the story begins, you create your apps in Visual Studio 2008 (any kind). And add edmx to it. While creating the model select the stored procedures and table you will be working with.

So your final edmx would look like,

 

image

EDMX

image

Model Browser
After that you right click on the edm designer and choose “Mapping Details”. In that you choose “Map Entity to Functions” then put the required stored procedures. You are done.

 

image

Stored Procedure mapping

Now if you are observing the SQL Profiler you would be able to see that the stored procedures are getting executed instead of your T-SQL.

I am planning to create a screen capture, when ready I will add that to this blog.

Namoskar!!!

Comments

  • Anonymous
    February 20, 2009
    PingBack from http://www.anith.com/?p=12181

  • Anonymous
    March 04, 2009
    I've been messing with the Entity Framework a little lately for a project.  Is it possible to write the sproc in visual studio and then have it push to the database?

  • Anonymous
    May 07, 2009
    In my previous text based blog I had promised to share the recording. Here it is and I am using Silverlight

  • Anonymous
    January 08, 2011
    The comment has been removed