ADO.NET Entity Framework 学习中级篇4—存储过程(下)
在EF中,各个实体的插入、更新和删除也都通过使用存储过程来完成,以便提高点性能。这个类似于数据集。其步骤是:先定义存储过程,然后在VS的可视化设计器,设置存储过程映射即可。
下面,以为Supplier实体映射存储过程为例。
分别建立插入、更新和删除存储过程。
InsertSuppliers存储过程定义如下:
CREATE PROCEDURE [dbo].[InsertSuppliers] -- Add the parameters for the stored procedure here @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24), @HomePage ntext AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO Suppliers(CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,HomePage) VALUES(@CompanyName,@ContactName,@ContactTitle,@Address,@City,@Region,@PostalCode,@Country,@Phone,@Fax,@HomePage); SELECT SCOPE_IDENTITY() AS SupplierID; END
DeleteSuppliers存储过程定义如下:
CREATE PROCEDURE [dbo].[DeleteSuppliers] -- Add the parameters for the stored procedure here @SupplierID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE Suppliers WHERE SupplierID=@SupplierID END
UpdateSuppliers存储过程定义如下:
CREATE PROCEDURE [dbo].[UpdateSuppliers] -- Add the parameters for the stored procedure here @SupplierID int, @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24), @HomePage ntext AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE Suppliers SET CompanyName=@CompanyName,ContactName=@ContactName,ContactTitle=@ContactTitle,Address=@Address,City=@City,Region=@Region,PostalCode=@PostalCode,Country=@Country,Phone=@Phone,Fax=@Fax,HomePage=@HomePage WHERE SupplierID=@SupplierID END
然后,在VS可视化设计器中,打开实体模式edmx文件,然后在Suppliers实体上鼠标右键à“存储过程映射”,然后在在分别设置存储过程即可。如下图所示:
至此,存储过程映射完毕。我们下面来具体的使用,代码如下:
使用存储过程Insert:
[Test] public void SPInsert() { using (var db = new NorthwindEntities1()) { var supplier = new Suppliers(); supplier.CompanyName = "cnblogs"; supplier.ContactName = "xray2005"; supplier.ContactTitle = "net"; supplier.City = "成都"; supplier.Region = "四川"; supplier.Country = "中国"; supplier.PostalCode = "600041"; supplier.Phone = "028-8001"; supplier.Fax = "028-8002"; supplier.HomePage = "http://xray2005.cnblogs.com"; db.AddToSuppliers(supplier); db.SaveChanges(); } }
使用存储过程Update:
[Test] public void SPUpdate() { using (var db = new NorthwindEntities1()) { var supplier = db.Suppliers.FirstOrDefault(s => s.SupplierID == 30); Assert.IsNotNull(supplier); supplier.CompanyName = "CNBLOGS"; supplier.ContactName = "xray2005"; supplier.ContactTitle = "♂风风车.net"; supplier.City = "成都"; supplier.Region = "四川"; supplier.Country = "China"; supplier.PostalCode = "600040"; supplier.Phone = "028-1008611"; supplier.Fax = "028-10086"; supplier.HomePage = "http://www.cnblogs.com/xray2005"; db.SaveChanges(); } }
使用存储过程Delete:
[Test] public void SPDelete() { using (var db = new NorthwindEntities1()) { var supplier = db.Suppliers.FirstOrDefault(s => s.SupplierID == 31); Assert.IsNotNull(supplier); db.DeleteObject(supplier); db.SaveChanges(); var supplier1 = db.Suppliers.FirstOrDefault(s => s.SupplierID == 31); Assert.IsNull(supplier1); } }
至此,实体存储过程映射介绍完毕。本节,内容比较简单。
作者:局外人
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.