ADO.NET Entity Framework 透過有output值的Stored Procedure存取資料庫資料

ADO.NET EntityFramework 透過有output值的Stored Procedure存取資料庫資料,就如一般微軟文件所提到,EntityFramework要與Stored Procedure配合可透過VS開發環境中的Model Browse內設定一個Function Import,給與這個Function Import一個名稱,並指向我們的stored procedure,然後在程式中存取該Function Import來完成資料存取.

但是若是我們寫好的Stored Procedure有output值,我們在EntityFramework的程式中,
應該如何取回這個output值呢?以下我們就製作一個簡單的例子來說明這個需求.(本文關鍵設定在5-3小點,若不想看完整範例可直接跳到5-3小點查閱)

1.我們使用Northwind資料庫製作一個下拉選擇控制項與一個基本的Form,可讓user選擇一個Friend的資料後,編輯這位Friend的每個欄位,然後按下儲存按鍵後回存到資料庫中.

2.本次我們會用到的Friend每個欄位型態大小如下:

3.本次我們會用到的Stored Procedure名稱為sp_friend_update,script如下:

-- =============================================
-- Author: horace
-- Create date: 20100511
-- Description: update a friend
-- =============================================
ALTER PROCEDURE [dbo]. [sp_friend_update]
@Seq int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Age int,
@Birthday datetime,
@Salary numeric(10,2),
@Email nvarchar(100),
@PhotoPath nvarchar(50),
@returncode int output
AS
BEGIN
  SET @returncode=1
  IF (SELECT COUNT(*) FROM Friends WHERE Seq = @Seq) > 0
    BEGIN
      UPDATE Friends SET FirstName = @FirstName,
          LastName = @LastName,
          Age = @Age,
          Birthday = @Birthday,
          Salary = @Salary,
          Email = @Email,
          PhotoPath = @PhotoPath
        WHERE Seq = @Seq
      SET @returncode=0--success
    END
  ELSE
    BEGIN
      SET @returncode=2--No data exist
    END
END

4.我們要開始在VS.NET 2010 Express開發環境中設定Function Import:

4-1假設我們已經建好了一個ADO.NET Entity Data Model名稱為NorthWind.edmx

4-2在開發環境中打開NorthWind.edmx,並點選右側的Model Browser視窗->點選NorthwndModel->點選EntityContainer:NorthwindEntities->點選Function Import->Add Function Import

4-3在Add Function Import對話方塊中,分別填入與選擇下列三項資訊:

Function Import Name:spEditFriend
Stored Procedure Name:sp_friend_update(下拉選擇我們要的stored procedure)
Returns a Collection Of:None

因為我們的stored procedure並不是回應出一群資料,而是一個整數型態的數值,故我們選擇None.設定好Function Import後,我們就可以在程式中使用剛剛打入的spEditFriend這個名稱來更新資料.

5.ASP.NET程式中:

5-1:可透過下列程式,抓取下拉選擇控制項中的資料,讓user可下拉選擇要更新資料的Friend:

using (NorthwindEntities NWContext = new NorthwindEntities())
{
    var friends = from s in NWContext.Friends
          selec new { Seq = s.Seq, FirstName = s.FirstName };
    if (friends.Any())
    {
        ddlSeq.DataSource = friends;
        ddlSeq.DataTextField = "FirstName";
        ddlSeq.DataValueField = "Seq";
        ddlSeq.DataBind();
        ListItem list = new ListItem("==choice==", "");
        ddlSeq.Items.Add(list);
        ddlSeq.SelectedValue = "";
    }
}

5-2:我們選擇一個Friend後,可透過下列程式,抓取這個Friend的全部資料,並顯示在網頁Form上,已供user修改

Friend friend;
int selSeq = Convert.ToInt32(ddlSeq.SelectedValue);
using (NorthwindEntities NWContext = new NorthwindEntities())
{
    friend = (from f in NWContext.Friends
            where f.Seq == selSeq
            select f).First();
}

5-3:修改好後,按下確認按鍵,我們執行回存到資料庫的程式碼如下,這裡我們分別處理stored pocedure output的三種值,並將這三種狀況其中之一顯示在網頁的label控制項上面,請注意spEditFriend是上面我們建立的Function Import的名稱,而其後的叁數分別是要傳送到stored precedure內的,最後一個就是承接output出來的叁數,我們拿這個叁數的值來進行儲存後的各種狀況判斷判斷:

using (NorthwindEntities NWContext = new NorthwindEntities())
{
    try
    {
        //Function Import的return type要設為None
        DateTime birthday = (TxBirth.Text == "") ? DateTime.Now : Convert.ToDateTime(TxBirth.Text);
        ObjectParameter returnCode = new ObjectParameter("returncode", typeof( Int32));
        NWContext.spEditFriend(Convert.ToInt32(TxSeq.Text),
            TxFN.Text,
            TxLN.Text,
            Convert.ToInt32(TxAge.Text),
            birthday,
            Convert.ToDecimal(TxSalary.Text),
            TxMail.Text,
            TxPath.Text,returnCode);
        switch (returnCode.Value.ToString())
        {
            case "0": Lb2.Text = "Success"; break;
            case "1": Lb2.Text = "Edit record failure or invalid format"; break;
            case "2": Lb2.Text = "No data exist"; break;
        }
    }
    catch (Exception ex2)
    {
        Lb2.Text = ex2.Message;
    }
}

6.執行程式結果如下:

當user修改完畢後,回存到資料庫後,會顯示success的字眼,這是由stored procedure的output值回傳到程式中判斷而來的.