2013年3月31日 星期日

Entity Framework 與 Stored Procedure - Insert, Update, Delete

上一篇「Entity Framework 與 Stored Procedure - 基本的 Select」先以最基本的 Select 讀取資料的操作來介紹 Entity Framework 透過 Stored Procedure 取得資料，而除了讀取資料外，對於資料的操作也包含了新增、修改、刪除，而這一篇就來介紹 Entity Framework 透過新增刪除修改的 Stored procedure 來執行資料的處理。

 

操作示範執行環境：
ASP.NET MVC 4 (C#) with Entity Framework 5.0.0, MS SQL Server Express 2008 R2

因為懶得寫 Stored Procedure，所以在網路上找到了有人所提供 Northwind 的 Stored Procedure Sample，所以就直接拿來用了，網站如下：

Stored Procedures for Northwind, Pubs, AdventureWorks database, Tools4SQL.Net, Stored Procedure Generator

這邊將會使用以下三個 Stored Procedure 來練習 EF 執行 SP，分別為：PR_Customers_Insert,  PR_Customers_DeleteByPK, PR_Customers_UpdateByPK，

image

SNAGHTML3ee3f83

 

Create 新增資料

這邊我們使用「PR_Customers_Insert」來新增 Customer 資料，先看看我這邊經過整理後的 SP 內容，

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[PR_Customers_Insert]    Script Date: 03/31/2013 13:00:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[PR_Customers_Insert]
 
        @CustomerID           nchar (5),
        @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)
 
AS
 
SET NOCOUNT ON;
 
BEGIN TRY
BEGIN TRAN
 
INSERT [dbo].[Customers]
(
        [CustomerID],
        [CompanyName],
        [ContactName],
        [ContactTitle],
        [Address],
        [City],
        [Region],
        [PostalCode],
        [Country],
        [Phone],
        [Fax]
)
VALUES
(
        @CustomerID,
        @CompanyName,
        @ContactName,
        @ContactTitle,
        @Address,
        @City,
        @Region,
        @PostalCode,
        @Country,
        @Phone,
        @Fax
)
 
select @CustomerID
 
COMMIT TRAN
END TRY
 
BEGIN CATCH
ROLLBACK TRAN
 
DECLARE @ErrorNumber_INT INT;
DECLARE @ErrorSeverity_INT INT;
DECLARE @ErrorProcedure_VC VARCHAR(200);
DECLARE @ErrorLine_INT INT;
DECLARE @ErrorMessage_NVC NVARCHAR(4000);
 
SELECT
        @ErrorMessage_NVC = ERROR_MESSAGE(),
        @ErrorSeverity_INT = ERROR_SEVERITY(),
        @ErrorNumber_INT = ERROR_NUMBER(),
        @ErrorProcedure_VC = ERROR_PROCEDURE(),
        @ErrorLine_INT = ERROR_LINE()
 
RAISERROR(@ErrorMessage_NVC,@ErrorSeverity_INT,1);
 
END CATCH
 

雖然說在 Controller 的 Create() 裡 CustomerID 是由程式這邊給的，但資料庫執行 PR_Customers_Insert 新增成功後將會把 CustomerID 傳回，由此我們得知新增是否成功，失敗就拋出 Exception，而新增成功則轉移頁面到 Details 頁顯示剛才所新增的 Customer 資料，

CustomerController - Create()

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(Customer customer)
{
    if (ModelState.IsValid)
    {
        string customerID = Guid.NewGuid().ToString().Substring(0, 5);
        
        try
        {
            var result = db.PR_Customers_Insert(
                customerID,
                customer.CompanyName,
                customer.ContactName,
                customer.ContactTitle,
                customer.Address,
                customer.City,
                customer.Region,
                customer.PostalCode,
                customer.Country,
                customer.Phone,
                customer.Fax);
 
            var resultID = result.FirstOrDefault();
 
            return RedirectToAction("Details", new { id = resultID });
        }
        catch
        {
            throw;
        }
    }
    return View();
}

 

這邊另外再加上 PR_Categories_Insert 的操作練習，因為這個與 Customer 的新增是有所不同的， CategoryID 欄位的值是自動增值，而且在 PR_Categories_Insert 的 @CategoryID 為 OUTPUT，所以當 Category 新增完成後就會把該筆資料的 CategoryID 給傳回，

PR_Categories_Insert 內容：

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[PR_Categories_Insert]    Script Date: 03/31/2013 14:03:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[PR_Categories_Insert]
 
        @CategoryID           int OUTPUT,
        @CategoryName         nvarchar (15),
        @Description          ntext,
        @Picture              image
 
AS
 
SET NOCOUNT ON;
 
 
BEGIN TRY
BEGIN TRAN
 
INSERT [dbo].[Categories]
(
        [CategoryName],
        [Description],
        [Picture]
)
VALUES
(
        @CategoryName,
        @Description,
        @Picture
)
 
SET @CategoryID = @@IDENTITY
 
COMMIT TRAN
END TRY
 
BEGIN CATCH
ROLLBACK TRAN
 
DECLARE @ErrorNumber_INT INT;
DECLARE @ErrorSeverity_INT INT;
DECLARE @ErrorProcedure_VC VARCHAR(200);
DECLARE @ErrorLine_INT INT;
DECLARE @ErrorMessage_NVC NVARCHAR(4000);
 
SELECT
        @ErrorMessage_NVC = ERROR_MESSAGE(),
        @ErrorSeverity_INT = ERROR_SEVERITY(),
        @ErrorNumber_INT = ERROR_NUMBER(),
        @ErrorProcedure_VC = ERROR_PROCEDURE(),
        @ErrorLine_INT = ERROR_LINE()
 
RAISERROR(@ErrorMessage_NVC,@ErrorSeverity_INT,1);
 
END CATCH
 

Controller 的 Action 方法內容如下：

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult CategoryCreate(Category category)
{
    if (ModelState.IsValid)
    {
        try
        {
            ObjectParameter param = new ObjectParameter("categoryID", typeof(Int32));
 
            var result = db.PR_Categories_Insert(
                param,
                category.CategoryName,
                category.Description,
                null);
 
            var categoryID = param.Value;
 
            return RedirectToAction("Index");
        }
        catch
        {
            throw;
        }
    }
    return View();
}

image

在程式中會需要建立一個型別為 ObjectParameter 的物件，用來承接資料庫執行完 PR_Categories_Insert 所傳回的 CategoryID 資料， 最後可以從 ObjectParameter 的 Value 屬性拿到傳回的值。

 

Update 修改資料

以下為 [PR_Customers_UpdateByPK] 的內容：

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[PR_Customers_UpdateByPK]    Script Date: 03/31/2013 14:15:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[PR_Customers_UpdateByPK]
 
        @CustomerID           nchar (5),
        @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)
 
AS
 
SET NOCOUNT ON;
 
BEGIN TRY
BEGIN TRAN
 
UPDATE [dbo].[Customers]
SET
        [CompanyName] = @CompanyName,
        [ContactName] = @ContactName,
        [ContactTitle] = @ContactTitle,
        [Address] = @Address,
        [City] = @City,
        [Region] = @Region,
        [PostalCode] = @PostalCode,
        [Country] = @Country,
        [Phone] = @Phone,
        [Fax] = @Fax
WHERE [dbo].[Customers].[CustomerID] = @CustomerID
 
COMMIT TRAN
END TRY
 
BEGIN CATCH
ROLLBACK TRAN
 
DECLARE @ErrorNumber_INT INT;
DECLARE @ErrorSeverity_INT INT;
DECLARE @ErrorProcedure_VC VARCHAR(200);
DECLARE @ErrorLine_INT INT;
DECLARE @ErrorMessage_NVC NVARCHAR(4000);
 
SELECT
        @ErrorMessage_NVC = ERROR_MESSAGE(),
        @ErrorSeverity_INT = ERROR_SEVERITY(),
        @ErrorNumber_INT = ERROR_NUMBER(),
        @ErrorProcedure_VC = ERROR_PROCEDURE(),
        @ErrorLine_INT = ERROR_LINE()
 
RAISERROR(@ErrorMessage_NVC,@ErrorSeverity_INT,1);
 
END CATCH
 

CustomerController 的 Update() 內容：

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Update(Customer customer)
{
    if (ModelState.IsValid)
    {
        try
        {
            var result = db.PR_Customers_UpdateByPK(
                customer.CustomerID,
                customer.CompanyName,
                customer.ContactName,
                customer.ContactTitle,
                customer.Address,
                customer.City,
                customer.Region,
                customer.PostalCode,
                customer.Country,
                customer.Phone,
                customer.Fax);
 
            return RedirectToAction("Details", new { id = customer.CustomerID });
        }
        catch
        {
            throw;
        }
    }
    return View();
}

 

Delete 刪除資料

以下為 [PR_Customers_UpdateByPK] 的內容：

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[PR_Customers_DeleteByPK]    Script Date: 03/31/2013 17:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PR_Customers_DeleteByPK]
 
        @CustomerID         nchar (5)
        
AS
 
SET NOCOUNT ON;
 
BEGIN TRY
BEGIN TRAN
 
DELETE FROM [dbo].[Customers]
WHERE [dbo].[Customers].[CustomerID] = @CustomerID
 
 
COMMIT TRAN
END TRY
 
BEGIN CATCH
ROLLBACK TRAN
 
DECLARE @ErrorNumber_INT INT;
DECLARE @ErrorSeverity_INT INT;
DECLARE @ErrorProcedure_VC VARCHAR(200);
DECLARE @ErrorLine_INT INT;
DECLARE @ErrorMessage_NVC NVARCHAR(4000);
 
SELECT
        @ErrorMessage_NVC = ERROR_MESSAGE(),
        @ErrorSeverity_INT = ERROR_SEVERITY(),
        @ErrorNumber_INT = ERROR_NUMBER(),
        @ErrorProcedure_VC = ERROR_PROCEDURE(),
        @ErrorLine_INT = ERROR_LINE()
 
RAISERROR(@ErrorMessage_NVC,@ErrorSeverity_INT,1);
 
END CATCH
 

CustomerController 的 Delete() 內容：

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(string id)
{
    if (!string.IsNullOrWhiteSpace(id))
    {
        bool isExists = db.Customers.Any(x => x.CustomerID == id);
 
        if (isExists)
        {
            var result = db.PR_Customers_DeleteByPK(id);
        }
    }
    return RedirectToAction("Index");
 
}

 

以上就是在 Entity Framework 執行含有 Insert, Update, Delete Stored Procedure 的練習，都是屬於基本型的內容，包含前一篇「Entity Framework 與 Stored Procedure - 基本的 Select」，兩篇介紹了四種基本資料操作（Create, Read, Update, Delete)，專案使用 Entity Framework 為資料存取層也可以執行 Stored Procedure，而不用再另外去以一般的 ADO.NET 方式做處理。

不過像這種只有在單一資料類別做 CRUD 處理的執行，並不太需要再去透過 Stored Procedure 的處理，不過倒是有些系統處理的資料會有連動處理或是需要再另外做運算處理，許多企業的作法就勢將這些處理給放到 Stored Procedure 中，有時候是會牽涉到多個資料庫，像這一類的情況，我就不會堅持一定要把這些屬於商業邏輯處理的劉呈給挪到系統程式裡，因為搬到系統程式來做處理，反而會增加系統開發與維護的困難。

 

以上

張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

提醒

千萬不要使用 Google Talk (Hangouts) 或 Facebook 及時通訊與我聯繫、提問，因為會掉訊息甚至我是過了好幾天之後才發現到你曾經傳給我訊息過，請多多使用「詢問與建議」(在左邊，就在左邊)，另外比較深入的問題討論，或是有牽涉到你實作程式碼的內容，不適合在留言板裡留言討論，請務必使用「詢問與建議」功能（可以夾帶檔案），謝謝。