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这些方法就可以了。

未完成！