We couldn’t sign you in
Select the account you want to use.

關於作者:

本文由微軟最有價值專 MVP TerryChuang 提供。微軟十分感謝 MVP 主動地將他們的經驗與上百萬名其他技術使用者交流。MVP TerryChuang 同時也將此篇文章放在他的部落格內,歡迎您按 此處 瀏覽 MVP TerryChuang 更多的技術文章、經驗分享與觀點。

問題的來龍去脈

實務上我們常將許多資料庫端的商業邏輯利用 T-SQL 語法撰寫預存程序(Stored Procedure)或使用者自訂函式,其中預存程序具有提高執行效能、增加商業邏輯的維護性、更易於除錯與方便權限控管等多項優點。甚至有 SQL Server 專家建議,每一個基礎資料表至少應該對應四組預存程序,分別對基礎資料表進行新增、刪除、修改及查詢等動作,使用者只能透過預存程序存取基礎資料表。

然而並非所有程式開發人員都熟悉資料庫及 T-SQL 語法,而且面對不同的資料來源(ADO.NET DataSet、資料庫、XML等)必須以不同資料的方式來存取,對於開發團隊成員素質不一的情況而言,將對開發效率有所阻礙。因此 Microsoft 發展出 Language-Integrated Query (以下簡稱 LINQ ),希望藉由一致性的資料存取介面來彌平物件與資料之間的間隙,讓不熟悉 T-SQL 語法的開發人員也可以輕易存取資料庫。

一個較為嚴謹的應用程式架構,前端應用程式與資料存取層應該也通常是彼此獨立。以 Microsoft 的解決方案為例,前端應用程式只需透過 .Net 程式來處理應用程式的流程控制與使用者介面,所有與後端資料庫存取相關則透過 T-SQL 來進行,在許多開發團隊的系統分析師或資料庫管理師也常用 T-SQL 來撰寫預存程序供前端應用程式開發人員來使用。本文將介紹以 LINQ to Entity 如何執行預存程序並接收回傳值。

問題的發生原因

要模擬出本文的測試環境,首先必須至 Microsoft Download Center 下載 Northwind 和 Pubs 範例資料庫安裝程式,依照預設值安裝完畢後將於【C:\SQL Server 2000 Sample Databases】路徑下,NorthWind 及 Pubs 資料庫的 sql(.sql)指令碼檔及資料庫檔案(.mdf、.ldf),您可以選擇於 SQL Server Management Studio(以下簡稱 SSMS)執行【instnwnd.sql】或是將【NORTHWND.MDF、NORTHWND.LDF】附加至您的 SQL Server 執行個體,以完成範例資料庫的安裝。接著依照下列步驟建立測試環境:

1.開啟 SSMS 輸入下列程式碼後按 F5 以建立 Stored Procedure。

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE type = 'P'
and name = 'usp_GetRegionDesc'
)
DROP PROC usp_GetRegionDesc
GO

CREATE PROCEDURE usp_GetRegionDesc
(
@RegionID int
,@RegionDesc nvarchar(50) output
)
AS
SET NOCOUNT ON
SET @RegionDesc = (SELECT RegionDescription
FROM Region
WHERE RegionID = @RegionID)

2.於【新增專案】對話方塊中選擇【主控台應用程式】並輸入名稱為【UseLinqGetSPOutput】。





3. 於方案總管中點選【UseLinqGetSPOutput】專案後按右鍵選擇【新增項目】。





4.於【加入新項目】對話視窗左方【已安裝的範本】中選擇【資料】,接著選擇【ADO.NET資料實體模型】後輸入名稱為【NWModel】。





5.於【實體資料模型精靈】中選擇【從資料庫產生】後按【下一步】。





6.於【選擇您的資料連接】對話視窗中點選【新增連接】。





7.假設 SQL Server 位於本機,則於【連接屬性】對話視窗輸入伺服器名稱為【.】或【(localhost)】,接著【選取或輸入資料庫名稱】為【Northwind】。





8.連線建立成功後,將會出現如下圖的畫面,預設【將實體連接設定另存在App.Config中】選項為勾選的狀態,請務必保持預設值。
另外 ADO.NET 資料實體模型會建立實體至 .edmx 檔,以本文的範例實體名稱即是 NorthwindEntities。





9.選擇模型中要包含的資料庫物件。在此我們選擇步驟1所建立的【usp_GetRegionDesc】即可。另外,也可以在這個畫面中設定模型的命名空間。








10.按下完成後應看到如下圖的畫面。





問題的解決方法

要在 ADO.NET 實體資料模型中執行 Stored Procedure,請依照下列步驟進行:

1. 【Ctrl+1】開啟實體資料模型瀏覽器,於函式匯入項目中按右鍵選擇【加入函式匯入】。





2.於【加入函式匯入】對話視窗中輸入下列資訊:

  • 函式匯入名稱 :GetRegionDesc。

  • 預存程序名稱:usp_GetRegionDesc(由於在問題發生的原因步驟 9,筆者只選擇 usp_GetRegionDesc,因此在預存程序名稱下拉式選單中只有一個 usp_GetRegionDesc 選項)。

  • 傳回下列項目的集合:由於 usp_GetRegionDesc 的無任何結果集(Result Set)回傳,因此使用預設值【無】。





3. 按下確定後即可看到如下圖的【GetRegionDesc】函式及其參數。





4.在 Program.cs 輸入下列程式碼:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace UseLinqGetSPOutput
{
class Program
{
static void Main(string[] args)
{
using (NorthwindEntities context = new NorthwindEntities())
{
#region 宣告ObjectParameter來接收Stored Procedure的Output參數
//多載一
System.Data.Objects.ObjectParameter opRegionDesc =
new System.Data.Objects.ObjectParameter("RegionDesc", typeof(string));
//多載二
//System.Data.Objects.ObjectParameter opRegionDesc =
// new System.Data.Objects.ObjectParameter("RegionDesc", "nvarchar(50)");
#endregion
for (int i = 1; i < 5; i++)
{
//呼叫Strored Procedure
context.GetRegionDesc(i, opRegionDesc);
Console.WriteLine(string.Format("RegionId={0}, RegionDesciption={1}"
,i.ToString(),opRegionDesc.Value));
}
}
Console.Read();
}
}
}


5.上述程式碼用來接收回傳值得 ObjectParameter 物件接收兩個參數,第一個是 Output 參數的名稱(不含@),第二個是 Output 參數的型態,其中 Output 參數的型態可以函式匯入的型別(本例為 String)或 T-SQL 的型別(本例為 nvarchar(50))。

6.執行結果如下:



 

其他相關資訊

CREATE PROCEDURE (Transact-SQL)
LINQ 簡介
LINQ to Entities


請讓我們知道

還滿意我們寫的內容嗎?無論您是遇到了以下任何一種情況:
「太好了!問題解決了。」
「問題沒有解決,我有更好的建議。」
都歡迎您利用本篇文章底部的意見調查表,寫下您寶貴的意見。也期待您的鼓勵,讓我們為您創造更多實用的技術文章。

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×