如何使用LINQ To Entity執行Stored Procedure並取得Output參數

如何使用LINQ To Entity執行Stored Procedure並取得Output參數

假設於Northwind資料庫中建立一個名為usp_GetRegionDesc的Stored Procedure,該Stored Procedure接受兩個參數,分別為RegionID和RegionDesc用來輸入RegionID透過查詢ResionDesccription,並以Outpu參數ResionDesc來回傳結果,TSQL敘述如下:

 

   1:  use Northwind
   2:  go
   3:  
   4:  if exists (select * from sys.objects
   5:             where type = 'P'
   6:               and name = 'usp_GetRegionDesc')
   7:      drop procedure usp_GetRegionDesc
   8:  go
   9:  
  10:  create procedure usp_GetRegionDesc
  11:  (
  12:      @RegionID        int
  13:      ,@RegionDesc    nvarchar(50)        output
  14:  )
  15:  as
  16:      set @RegionDesc = (select RegionDescription
  17:              from Region
  18:              where RegionID = @RegionID)
  19:  go
  20:  

 

接著建立一ADO.NET實體資料模型(.edmx)(下圖左),並選擇由資料庫產生模型(下圖中),在選擇您的資料庫物件視窗中勾選usp_GetRegionDesc(下圖右)。

imageimageimage

 

於Entity Designer空白處按滑鼠右鍵選擇【加入\函式匯入】(下圖左),於加入函式匯入視窗中輸入函式名稱為【GetRegionDesc】,選擇預存程序名稱為【usp_GetRegionDesc】,傳回下列項目的集合使用預設值-無,因本文使用的Stored Procedure沒有回傳值,RegionDescription欄位是使用RegionDesc參數回傳(下圖右),輸入完畢後按確定即可於模型瀏覽器看到GetRegionDesc函式(下圖右)。

imageimageimage

 

最後建立一測試網頁名為SPOutputParam.aspx,並輸入下列程式碼:

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Linq;
   4:  using System.Web;
   5:  using System.Web.UI;
   6:  using System.Web.UI.WebControls;
   7:  using System.Data.Objects;
   8:  
   9:  namespace NW
  10:  {
  11:      public partial class SPOutputParam : System.Web.UI.Page
  12:      {
  13:          protected void Page_Load(object sender, EventArgs e)
  14:          {
  15:              using (NorthwindEntities context = new NorthwindEntities())
  16:              {
  17:                  ObjectParameter opRegionDesc = new ObjectParameter("RegionDesc", typeof(string));
  18:                  for (int i = 1; i < 5; i++)
  19:                  {
  20:                      context.GetRegionDesc(i, opRegionDesc);
  21:                      Response.Write(string.Format("RegionID={0},RegionDescription={1}<br/>", i.ToString(), opRegionDesc.Value));
  22:                  }
  23:              }
  24:          }
  25:      }
  26:  }

上述程式碼的重點在於第17列的bjectParamter的實體化參數,其中第一個參數為Stored Procedure中Output參數名稱相同,但可不區分大小寫,第二個參數為Output參數的資料型態,上面使用的是Entity Model中函式的型別。

image

 

另外一種寫法是用Stored Procedure中Output參數的型別,本文中Output參數的型別為【nchar(50)】,如下列程式碼第17列。

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Linq;
   4:  using System.Web;
   5:  using System.Web.UI;
   6:  using System.Web.UI.WebControls;
   7:  using System.Data.Objects;
   8:  
   9:  namespace NW
  10:  {
  11:      public partial class SPOutputParam : System.Web.UI.Page
  12:      {
  13:          protected void Page_Load(object sender, EventArgs e)
  14:          {
  15:              using (NorthwindEntities context = new NorthwindEntities())
  16:              {
  17:                  ObjectParameter opRegionDesc = new ObjectParameter("RegionDesc", "nchar(50)");
  18:                  for (int i = 1; i < 5; i++)
  19:                  {
  20:                      context.GetRegionDesc(i, opRegionDesc);
  21:                      Response.Write(string.Format("RegionID={0},RegionDescription={1}<br/>", i.ToString(), opRegionDesc.Value));
  22:                  }
  23:              }
  24:          }
  25:      }
  26:  }

 

【執行結果】

image