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 INTOSuppliers(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 SETCompanyName=@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);

            }

        }