2013年3月30日 星期六

Entity Framework 與 Stored Procedure - 基本的 Select

在企業裡,使用 Stored Procedure (以下簡稱 SP) 相當尋常,常常會看到有為數不少的 SP 在資料庫裡頭,而這些 SP 裡面的 T-SQL 內容是各式各樣,相信有許多朋友見過的 SP 一定是比我還要多,早期有些企業因為要避免過多的 SQL Statement 在程式中出現,所以會將程式中所需要執行操作的 SQL Statement 給移到資料庫裡然後建立 SP,然後再讓程式透過 ADO.NET 或是其他方式來執行,還有一種就是某些資料的存取有一定的規則,而這些為了要統一管理這些規則而避免各個系統有自己一套的處理方式,所以會把資料存取的方式給統一建立 SP,然後各系統在去透過這些 SP 來存取資料,另外還有的就是有些資料的存取是必須要在資料庫裡頭來做,所以建立 SP 來做處理,當然還有很多其他的原因。

現在很多企業很少有專責的 DBA 來管理資料庫(其實在我的工作經歷中,也只有一家公司有這樣的編制也真的有這麼一位 DBA),大多是程式設計師也必須要兼任 DBA 的角色,也因為如此,很多個開發人員也都可以使用到公司裡的資料庫們,這樣也導致一個 DB 中的 SP 會有很多開發人員去使用與維護,經常發生的就是原本運作好好的 SP 會突然無法正常使用,這樣的狀況很多都是 SP 被別人做了修改,而另一種情況就是 DB 裡有多個 SP 的內容都是做一樣的事情,但卻會有很多分身,然後名稱都不同但有很類似,甚至於有些 SP 的內容看起來已經是沒有在使用了,但又不感冒然移除,深怕移除之後讓某個古董級系統產生異常而無法運作。

以上這些都是目前很多企業在使用 SP 的現狀,SP 的內容也是程式,但偏偏很少有去做管理或是做版本控制,導致 DB 內的 SP 隨著時間的推移而越來越多,等到哪一天資料庫要更新版本或是出了狀況時,這些 SP 就讓人相當頭大。

一開始就說了一堆看似與主題無關的內容,但也說明了過多以及過度使用 SP 對於系統開發的影響,我個人是不反對使用 SP,因為有些資料的處理並不適合放在程式中,但我反對過多的商業邏輯放在 SP 裡面去處理,每個系統的商業邏輯不盡相同,所以就必須將商業邏輯給放在個系統的程式裡做處理,在需求變動的情況下,也只要去修改系統的程式即可。

而已經使用 Entity Framework 開發的系統通常很少會再去跟資料庫裡的 SP 打交道,但是有時候是無法避免要跟 SP 做接觸,此時就必須要學習怎麼透過 Entity Framework 去跟這些 SP 溝通。

 


操作示範執行環境:

ASP.NET MVC 4 (C#) with Entity Framework 5.0.0, MS SQL Server Express 2008 R2

 

前面東啦西扯地講了一堆,就直接看 EF 裡面怎麼使用 SP,在 Northwind 範例資料庫裡有以下這些的 SP,

image

接著我們以「usp_GetAllCustomer」「usp_GetCustomerByID」來做練習。

我們建立一個 ASP.NET MVC 專案,然後加入 ADO.NET 實體資料模型,除了加入 Table 外,我們也加入這次要用來練習的兩個 SP ,

SNAGHTML13e4e73

建立 ADO.NET 實體資料模型後,並不會在 Model Diagram 裡看到加入的 SP,而是需要在模型瀏覽器(Model Browser)裡的 Function Imports 才看得到,

image

Model Browser 裡可以看到要使用這些 SP 時於傳入所需要使用到的 Parameter,而要看這些 SP 所回傳來的資料對應內容則是要使用 「Function Import Mapping」,

image

以下是「usp_GetAllCustomer」的 Mapping Details,

image

另外透過 SP 存取的資料型別並不是 Entity Type 而是 Complex Type,預設的型別名稱會是以 SP 名稱再加上「_Result」的後綴詞,

image

 

usp_GetAllCustomer

這個 SP 的內容很簡單,也就是取回全部的 Customer 資料,

ALTER PROCEDURE [dbo].[usp_GetAllCustomer]
AS
BEGIN
 
    SELECT 
        [CustomerID]
        ,[CompanyName]
        ,[ContactName]
        ,[ContactTitle]
        ,[Address]
        ,[City]
        ,[Region]
        ,[PostalCode]
        ,[Country]
        ,[Phone]
        ,[Fax]
    FROM [Northwind].[dbo].[Customers]
    order by [CustomerID] ASC
 
END

然後我們在 ASP.NET MVC Controller 程式裡用 EF 的 SP Function 來取得全部的 Customer 資料,

image

這邊要在提醒一次,傳回來的資料欄位雖然與 Customer 大致一樣,但型別並不是 Entity Type「Customer」,接回來的資料型別以及在 View 所使用 Model 型別為「usp_GetAllCustomer_Result」,

image

image

執行結果

image

 

usp_GetCustomerByID

取得全部資料後,接著我們再來練習怎麼取得單筆資料,以下是 usp_GetCustomerByID 的內容,

ALTER PROCEDURE [dbo].[usp_GetCustomerByID]
(
    @CustomerID NCHAR(5)
)
AS
BEGIN
 
    SELECT 
        [CustomerID]
        ,[CompanyName]
        ,[ContactName]
        ,[ContactTitle]
        ,[Address]
        ,[City]
        ,[Region]
        ,[PostalCode]
        ,[Country]
        ,[Phone]
        ,[Fax]
    FROM [Northwind].[dbo].[Customers]
    where CustomerID=@CustomerID
 
END

這邊我們需要傳給 usp_GetCustomerByID 一個 @CustomerID 的參數,程式的內容如下,

image

一定有人會質疑接回的資料只有一筆而已,為什麼最後還需要再加上 FirstOrDefault() 方法來取出那一筆資料呢?

因為 NorthwindEntities.usp_GetCustomerID() 這個方法所傳回的資料型別為「ObjectResult<T>」,

image

型別 ObjectResult<T> 繼承了 ObjectResult, IEnumerable<T>, IEnumerable,所以既使我們知道 usp_GetCustomerByID 回傳只有一筆資料,因為回傳的是 ObjectResult<T> 型別,所以要用 FirstOrDefault() 來取得那一筆資料出來,

View

image

執行結果

image

 


以上就是用兩個簡單範例來練習如何在 EF 去取得 Stored Procedure 的查詢結果,當然實際專案上的 SP 內容與回傳結果不是這麼地單純,所以在幾個基本操作介紹完之後再來探討較進階的處理。

 

以上

2 則留言:

  1. 請問,若是 前人留下的 SP 因為某些因素不能改的前提下,該 SP 的 select 語法中又取出兩個相同名稱的資料欄位,其中第二個重複的欄位並未使用別名,譬如 select AA, BB, CC, AA, DD from TT 出現兩次 AA,這種情況下所產生的程式碼會自動將第二個重複的 AA 命名為 AA1,執行時會有錯誤訊息:

    "在相同名稱的資料讀取器中沒有對應的資料行"

    請問,我們是否可以加 "不用讀取 的 排除欄位"?
    或,
    於 SP_XXX_Result 的 model 中使用某種 metadata 來定義 AA1 對應到 SP 中的 AA?


    回覆刪除
    回覆
    1. 其實真有遇到這樣的狀況時,就不要強求一定要在 EF 去使用 Stored Procedure,
      可以直接使用 ADO.NET 的方式來讀取 SP,
      又或者是可以改用 EF Code First 的方式來操作。

      刪除

提醒

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