ADO.NET Entity Framework 中使用存储过程

本文主要介绍在ADO.NET Entity Framework中使用存储过程来进行查询、插入、更新、删除操作。

使用一下SQL建立实例数据库，下面选择了Northwind库来执行

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Group](
    [GroupID] [int] IDENTITY(1,1) NOT NULL,
    [GroupName] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
    [GroupID] 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


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](20) NOT NULL,
    [UserGroupID] [int] NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserID] 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

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Group] FOREIGN KEY([UserGroupID])
REFERENCES [dbo].[Group] ([GroupID])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Group]
GO

create procedure CreateUser
@username nvarchar(20),
@groupid int
as
begin
insert into [User] ([UserName] ,[UserGroupID] ) values(@username,@groupid)
end
go

create procedure DeleteUser
@userid int,
@groupid int
as
begin
delete from [User] where UserID=@userid
end
go

create procedure UpdateUser
@userid int,
@username nvarchar(20),
@groupid int
as
begin
update [User] set [UserName] =@username ,[UserGroupID] =@groupid where [UserID] =@userid 
end
go
create procedure GetUserInfo
@userid int
as
begin
select [UserID],[UserName],[UserGroupID] from [User] where [UserID] =@userid 
end
go

insert into [Group] ([GroupName] ) values('管理员')
insert into [Group] ([GroupName] ) values('注册用户')
insert into [Group] ([GroupName] ) values('游客')

 

执行成功建立了User和Group两个表以及4个存储过程，CRUD User表的4个存储过程

 

由于之前我已经建立了EDMX模型，现在我要更新一下，右键模型板

右键 功能很强大，其中有打开Model Browser 窗口，从数据库更新模型，和从模型生成数据库（反映射），选择Update Model from Database 项目选择添加选项卡，选中全部复选框，像生成时候一样即可

 

 

查询存储过程的使用

点选下图中的红框处，右键点击函数导入，选择创建函数导入，输入方法名和返回类型。

添加函数后就可以通过方法来调用存储过程了。

NorthwindEntities ne = new NorthwindEntities();
            var user = ne.GetUserInfo(1);
            foreach (User u in user)
            {
                string userName = u.UserName;
            }

 

对应的存储过程是

ALTER procedure [dbo].[GetUserInfo]
@userid int
as
begin
select [UserID],[UserName],[UserGroupID] from [User] where [UserID] =@userid 
end

 

 

添加、修改、删除存储过程的ADOEF 使用

 

添加、修改、删除的方法也可以直接绑定到实体对象上，通过模型浏览器。

要特别注意，还要添加对应的属性

插入/更新/删除操作只需要像平时一样使用AddToUser/更新实体类并SaveChanges/DeleteObject这些方法就可以了。