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);
}
}
至此，实体存储过程映射介绍完毕。本节，内容比较简单。
