استفاده از stored procedure در entity framework در این مقاله تصمیم داریم نحوه استفاده از روال های ذخیره شده stored procedure در entity framework را بررسی کنیم

سلام دوستان

همان گونه که می دانید، رویه های ذخیره شده (Stored Procedures) دستورهای SQL هستند که بصورت کمپایل شده روی پایگاه داده ذخیره شده و آماده اجرا می باشند. از آنجا که این آبجکت ها نیاز به بررسی گرامر و کمپایل ندارند، به مراتب سریع تر از دستورات SQL است که در C# (اعم از LINQ و غیر آن) می نویسیم، عمل می کنند.

ابتدا جدولی با نام Person میسازیم

اسکریپت ساخت جدول و بانک

USE [master]
GO
/****** Object:  Database [PersonDB]    Script Date: 08/31/2014 15:56:20 ******/
CREATE DATABASE [PersonDB] ON  PRIMARY 
( NAME = N'PersonDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PersonDB.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'PersonDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PersonDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [PersonDB] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [PersonDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [PersonDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PersonDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PersonDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PersonDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PersonDB] SET ARITHABORT OFF
GO
ALTER DATABASE [PersonDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PersonDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [PersonDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PersonDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PersonDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PersonDB] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [PersonDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PersonDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PersonDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PersonDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PersonDB] SET  DISABLE_BROKER
GO
ALTER DATABASE [PersonDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PersonDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PersonDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [PersonDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PersonDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PersonDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [PersonDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [PersonDB] SET  READ_WRITE
GO
ALTER DATABASE [PersonDB] SET RECOVERY FULL
GO
ALTER DATABASE [PersonDB] SET  MULTI_USER
GO
ALTER DATABASE [PersonDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [PersonDB] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'PersonDB', N'ON'
GO
USE [PersonDB]
GO
/****** Object:  Table [dbo].[Persons]    Script Date: 08/31/2014 15:56:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Persons](
	[PersonID] [int] IDENTITY(1,1) NOT NULL,
	[PersonName] [nvarchar](150) NOT NULL,
	[PersonFamily] [nvarchar](150) NOT NULL,
	[PersonAge] [int] NOT NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

سپس stored procedure های 5 عمل اصلی

--
-- Dropping stored procedure Persons_SelectAll : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Persons_SelectAll]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[Persons_SelectAll]
  
GO

-- ==========================================================================================
-- Entity Name:	Persons_SelectAll
-- Author:	Mehdi Keramati
-- Create date:	8/31/2014 3:57:15 PM
-- Description:	This stored procedure is intended for selecting all rows from Persons table
-- ==========================================================================================
Create Procedure Persons_SelectAll
As
Begin
	Select 
		[PersonID],
		[PersonName],
		[PersonFamily],
		[PersonAge]
	From Persons
End

GO


--
-- Dropping stored procedure Persons_SelectRow : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Persons_SelectRow]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[Persons_SelectRow]
  
GO

-- ==========================================================================================
-- Entity Name:	Persons_SelectRow
-- Author:	Mehdi Keramati
-- Create date:	8/31/2014 3:57:15 PM
-- Description:	This stored procedure is intended for selecting a specific row from Persons table
-- ==========================================================================================
Create Procedure Persons_SelectRow
	@PersonID int
As
Begin
	Select 
		[PersonID],
		[PersonName],
		[PersonFamily],
		[PersonAge]
	From Persons
	Where
		[PersonID] = @PersonID
End

GO


--
-- Dropping stored procedure Persons_Insert : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Persons_Insert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[Persons_Insert]
  
GO

-- ==========================================================================================
-- Entity Name:	Persons_Insert
-- Author:	Mehdi Keramati
-- Create date:	8/31/2014 3:57:15 PM
-- Description:	This stored procedure is intended for inserting values to Persons table
-- ==========================================================================================
Create Procedure Persons_Insert
	@PersonName nvarchar(150),
	@PersonFamily nvarchar(150),
	@PersonAge int
As
Begin
	Insert Into Persons
		([PersonName],[PersonFamily],[PersonAge])
	Values
		(@PersonName,@PersonFamily,@PersonAge)

	Declare @ReferenceID int
	Select @ReferenceID = @@IDENTITY

	Return @ReferenceID

End

GO


--
-- Dropping stored procedure Persons_Update : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Persons_Update]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[Persons_Update]
  
GO

-- ==========================================================================================
-- Entity Name:	Persons_Update
-- Author:	Mehdi Keramati
-- Create date:	8/31/2014 3:57:15 PM
-- Description:	This stored procedure is intended for updating Persons table
-- ==========================================================================================
Create Procedure Persons_Update
	@PersonID int,
	@PersonName nvarchar(150),
	@PersonFamily nvarchar(150),
	@PersonAge int
As
Begin
	Update Persons
	Set
		[PersonName] = @PersonName,
		[PersonFamily] = @PersonFamily,
		[PersonAge] = @PersonAge
	Where		
		[PersonID] = @PersonID

End

GO


--
-- Dropping stored procedure Persons_DeleteRow : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Persons_DeleteRow]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[Persons_DeleteRow]
  
GO

-- ==========================================================================================
-- Entity Name:	Persons_DeleteRow
-- Author:	Mehdi Keramati
-- Create date:	8/31/2014 3:57:15 PM
-- Description:	This stored procedure is intended for deleting a specific row from Persons table
-- ==========================================================================================
Create Procedure Persons_DeleteRow
	@PersonID int
As
Begin
	Delete Persons
	Where
		[PersonID] = @PersonID

End

GO

سپس یک پروژه از نوع Console Application ایجاد میکنیم

در مرحله بعد مدل Entity را اضافه میکنیم در قسمت انتخاب جداول ار قسمت Store Procedures روال های خود را نیز انتخاب میکنید

و حالا وقت استفاده است

دستور آنتخاب همه

 //انتخاب همه 
            var All = db.Persons_SelectAll();

 همانطور که در تصویر میبینید نام روال ها نیز نمایش داده میشود

دستورات به شرح ذیل است

            PersonDBEntities db=new PersonDBEntities();

            //انتخاب همه 
            var All = db.Persons_SelectAll();

            //ثبت جدید
            db.Persons_Insert("ایمان", "مدائنی", 29);


                //ویرایش
            db.Persons_Update(1, "ایمان", "مدائنی", 30);


            //حذف
            db.Persons_DeleteRow(1);

            //انتخاب یک شخص
           var person= db.Persons_SelectRow(1);

همانطور که میبینید نیازی به فراخوانی SaveChanges نیست

نمونه هم ضمیمه کردم

موفق و پیروز باشید