SQL Server 主子關係而不是表中的儲存過程
去年在大學裡，我發現了一些關於儲存過程非常有趣的東西。它主要是關於減少事務管理、錯誤處理和安全性的程式碼重複。但是我從那以後就對其進行了研究，但在任何地方都找不到。也許我不知道它叫什麼，所以我會解釋一下。
假設我有一個簡單的表（忘記它的關係，只是一個表）。我至少可以對它執行 5 種可能的操作，即 CRUD，其中 R 讀取一行的詳細資訊，或者讀取給定條件的行列表。同樣，讓我們不要對複雜的儲存過程進行太多詳細介紹，讓我們假設，對於這個範例，我們只需要執行以下 5 個操作：
- 創建（簡單插入）
- 讀取（用於讀取單行）
- 讀取多個（讀取行列表，簡單
select * from sometable where some condition，沒什麼複雜的），- 更新（簡單更新）
- 刪除（同樣，簡單刪除）。
出於本範例的目的，一個具有 2 或 3 列的簡單表，以及您可以想出的最簡單的過程來執行這些操作。
問題：
我的講師在與我們談論交易時建議我們採取這些簡單的程序，並像往常一樣將它們分開。但隨後要編寫一個執行其中每一個的主程序。這意味著我們有一個主程序，我們向其中傳遞一個字元讓我們說“C”用於創建等等，以及一系列決定執行哪個作業的條件。當然，我們應該將主程序所需的詳細資訊傳遞給子程序，這意味著我們需要從使用者那裡獲取它們作為參數。這個主程序已經開始聽起來很複雜。
他的推理是，在主程序中進行事務處理、驗證、錯誤處理和安全處理。然後使用所需的參數呼叫該主過程，它會執行所有檢查，並將參數傳遞給子過程。下面是一個例子。
簡單的表，不用太擔心關係、鍵、索引、約束、觸發器等。
對於以下兩段程式碼，唯一真正相關的部分是 try…catch 之間的部分。剩下的就是master的樣板。
下面是創建過程的程式碼：
ALTER proc [Actions].[AreaCreate] --External Variables - Input @AreaName varchar(50), @AreaAvailablity bit, @Description varchar(max), --External Variables - Output @NoOfRecords int output as --Internal Variables declare @ErrorMessage varchar(max) Begin try insert [Tennis3rdYrMVC].[dbo].Areas ([AreaName], [AreaAvailablity], [Description]) values (@AreaName, @AreaAvailablity, @Description) --Show # of records affected so you can detect nulls or empty lists --and handle them as you choose in the code set @NoOfRecords = @@ROWCOUNT End try Begin Catch set @ErrorMessage = ERROR_MESSAGE() raiserror(@ErrorMessage,16,1) return ERROR_NUMBER() End Catch --All Ok return 0下面是刪除過程的另一段程式碼
ALTER proc [Actions].[AreaDelete] --External Variables - Input @Id int, --External Variables - Output @NoOfRecords int output as --Internal Variables declare @ErrorMessage varchar(max) begin try delete from [Tennis3rdYrMVC].[dbo].Areas where Id = @Id --Show # of records affected so you can detect nulls or empty lists --and handle them as you choose in the code set @NoOfRecords=@@ROWCOUNT end try begin catch set @ErrorMessage = ERROR_MESSAGE() raiserror(@ErrorMessage,16,1) return ERROR_NUMBER() end catch --All Ok return 0最後，推薦的，複雜的master proc。
ALTER proc [Admin].[AreaMaster] --External Variables - Input @JobType int, -- 1=Create, 2=Read 1, 3=Read Many, 4=Update, 5=Delete --Master sprock uses null defaults because not every job requires every field to be present. --i.e. We dont need to know the area name of an area we want to delete. @Id int = null, @AreaName varchar(50) = null, @AreaAvailablity bit = null, @Description varchar(max) = null, --External Variables - Output @NoOfRecords int = null output --Used to count the number of records affected where needed. as BEGIN --Internal Variables declare @ErrorMessage varchar(max) declare @return_value int -- SET NOCOUNT ON added to reduce network traffic and speed things up a little. SET NOCOUNT ON; /* --VALIDATION --Logic for ensuring all required values are entered should be done in processing below in --the master sprock, NOT in the children (i.e. why check for valid id in 5 sprocks when you --can do it here in one). We will do all the processing needed to ensure valid and required values are entered where needed. --SECURITY This is also where we put the security code required to stop SQL Injection and other attacks, NOT in the child sprocks. The child sprocks would not be allowed to execute by pages directly. */ --Once all validation is done, call relevant child sprocks --Call AreaCreate Sprock if(@JobType='1') begin exec @return_value = [Actions].[AreaCreate] @AreaName = @AreaName, @AreaAvailablity = @AreaAvailablity, @Description = @Description, @NoOfRecords = @NoOfRecords OUTPUT --select @return_value 'Return Value' if @return_value<>0 begin raiserror('Error: Problem creating area.',16,0) --rollback transaction return 99 end end --Call AreaShowDetail Sprock if(@JobType='2') begin exec @return_value = [Actions].[AreaShowDetail] @Id = @Id, @NoOfRecords = @NoOfRecords output ----Testing --select 'Return Value' = @return_value if @return_value<>0 begin raiserror('Error: Problem reading area details.',16,0) --rollback transaction return 99 end end --Call AreaShowList Sprock if(@JobType='3') begin exec @return_value = [Actions].[AreasShowList] @NoOfRecords = @NoOfRecords output ----Testing --select 'Return Value' = @return_value if @return_value<>0 begin raiserror('Error: Problem reading areas list.',16,0) --rollback transaction return 99 end end --Call AreaUpdate Sprock if(@JobType='4') begin EXEC @return_value = [Actions].[AreaUpdate] @Id = @Id, @AreaName = @AreaName, @AreaAvailablity = @AreaAvailablity, @Description = @Description, @NoOfRecords = @NoOfRecords OUTPUT --select 'Return Value' = @return_value if @return_value<>0 begin raiserror('Error: Problem updating area.',16,0) --rollback transaction return 99 end end --Call AreaDelete Sprock if(@JobType='5') begin exec @return_value = [Actions].[AreaDelete] @Id = @Id, @NoOfRecords = @NoOfRecords output --select 'Return Value' = @return_value if @return_value<>0 begin raiserror('Error: Problem deleting area(s).',16,0) --rollback transaction return 99 end end --All Ok return 0 END這是複雜的還是什麼？現在想像另一個層次，如果你有幾個表要操作，將決定呼叫哪個子主控，而這個主控需要一個更好的名字，每個表的每個欄位都必須有外部參數。但是，驗證、安全和事務程式碼將被提升到該級別。
更重要的是，這是專業人士的做法。最後，我將 MVC 與實體框架一起使用。假設我有一個這樣的數據庫（我有），並且只允許通過這個主記憶體儲過程進行訪問（它是）。如何從 EF 和 MVC 中呼叫這樣的儲存過程。更好的是，我將如何一起繞過 EF 並以我的視圖可以理解的方式將數據輸入我的控制器。我知道如何使用 ASP.Net 程式碼來做到這一點。但不是在 MVC 中。
有什麼建議麼。請隨時告訴我，這完全是瘋狂的，因為它看起來像地獄般的大量工作（如果你有 50 個表（我有）怎麼辦。想像一下，跨 50 個表的 5 個簡單操作 = 250 個子儲存過程 + 50 Sub-Sub-Masters, + 5 Sub-Masters + 1 Master。我的工作被剪掉了。有沒有工具可以做到這一點，或者至少為我生成一個模板？
總而言之，我的問題是：
- 這是過於復雜還是這是專業的方法？
- 用於描述這種主/子程序關係的術語是什麼。
- 有沒有可以自動化這個過程的工具。
- 有沒有工具可以幫助我生成某種樣板模板。
- 如何將此過程與 EF 和 MVC 一起使用
- 我怎樣才能完全繞過 EF 並找到一種直接呼叫該過程的方法。
- 有沒有類似類的東西，甚至 T-SQL 中的方法可以像標準的程式碼一樣構造它。這意味著每個子過程都是一個方法，每個表都有其屬性，建構子將是對象的初始（或更改）表示。
- 如果 7 確實存在，這將是更標準的，我描述的方式（即）拆分所有內容，或者 7 中的想法。
關於這一點 4 我說的是一些簡單的東西，比如已經在 SQL Server 中的那個，你可以在其中右鍵點擊一個過程並選擇執行，它會生成一些簡單的程式碼來測試你的過程，或者就像一個用於製作函式的程式碼。
感謝您閱讀本文以及您可以提供的任何幫助。
從理論上講，您的講師提出了一個很好的概念。將某些類型視為頂級域模型的想法很有意義。了解如何拯救其直系子女的對像也非常有用。
除了理論，我不同意實現，主要是因為它（不必要的）冗長和不靈活。
儲存過程
儲存過程很棒，但是使用 ORM（如 EF）或任何自動化框架的好處之一是您不必一遍又一遍地編寫樣板程式碼來做同樣的事情。多年來，我已經大大減少了我的應用程序中僅 CRUD 過程的數量，而是更喜歡根據需要自動生成語句（同樣，這是一個好的 ORM 的功能）。
閱讀
大多數應用程序需要以不同的方式查看相同的數據，並且擁有無限數量的選項來查詢該數據通常是有利的。也許你想要整張唱片；也許你想要一個欄位。也許您想要一級對象層次結構，或者您希望將對象層次結構扁平化為視圖模型。
多讀
當我編寫儲存過程時，我傾向於將精力放在高度優化的儲存過程上，以有效地瀏覽/搜尋數據。
在大多數業務應用程序中，read-many 最終成為數據的摘要視圖。作為搜尋結果返回 1000 條窄/扁平記錄是微不足道的；但是檢索 1000 個複雜對象的完整層次結構效率極低，通常不需要。
公平地說，您的主 proc 可以返回適當的摘要視圖，但我指的是我之前的觀點，即能夠以多種方式查看數據。聲明單一的多讀行為是不必要的限制。
刪除
刪除有時是儲存過程的理想選擇。將 100 條記錄載入到業務層只是為了獲取它們的 ID 並一一發出 DELETE 是沒有意義的。
事務
在我看來，最靈活的事務支持通常來自業務層。請參閱我對“應在 .NET 或 SQL Server 中處理事務？”的回答：https ://stackoverflow.com/a/12470061/453277
頂級模型
“Person”是頂級模型的良好候選者，而“Address”可能是該模型的子模型。
一個主程序促進所有訪問的想法是完全不切實際的。儘管您可能只向使用者顯示人員管理螢幕，但在後台您可能希望訪問地址對象而不需要或不知道與它相關的人員。
一個簡單的範例是帶有地址列表的個人資料頁面；您點擊“編輯”按鈕以啟動模式視窗以編輯地址的詳細資訊。你真的想要通過人員管理邏輯來獲取地址數據嗎？您可能會執行與父母相關的安全檢查；您可能會與父母建立新的關係；你可能什麼都不做。靈活性和可重用性是這裡的關鍵。
此外，當您決定在 Person->Address 關係之外添加 Company->Address 關係時，您不希望重新創建任何此邏輯。
親子關係
兩個相關對象之間並不總是存在可定義的父/子關係。我最喜歡的例子是 Person 和 Document。
- 一個人可能有一個他們創作的文件列表。
- 一個文件可能有一個作者列表。
哪個是家長？
對數據庫來說，它幾乎不重要。某處可能有一個
PersonDocument包含兩個整數列的表；哪個是父母並不重要。同樣，在業務層中，靈活性是關鍵。您應該能夠以“孩子”的形式訪問Document人員列表，並以“孩子”Person的形式訪問文件列表。我喜歡將所有關係視為潛在的雙向關係。將它們視為雙向的從來沒有什麼壞處，但強制父/子層次結構可能會受到限制。
安全
SQL 注入
我在您的主程序的評論中註意到了這一點：
這也是我們放置阻止 SQL 注入和其他攻擊所需的安全程式碼的地方，而不是在子鏈中。不允許子鏈輪直接由頁面執行。
對儲存過程的呼叫應始終正確參數化，這提供了所需的防止注入的保護。這裡提到的 SQL 注入要麼無關緊要，要麼令人擔憂，因為它表明呼叫沒有得到適當的準備。
Securables
對 SQL Server 中的對象級安全性的討論遠遠超出了本文的範圍，但我會提到，您可以在不使用主過程的情況下實現極其精細的安全性。
建議
- 如果您使用的是 ORM（如 EF），請讓它完成工作並為您編寫此管道程式碼。不要試圖將不同的範式強加給它。如果您必須使用講師的方法（例如對於作業），從等式中刪除 EF 可能更容易。
- 靈活性是關鍵（我已經第三次說了）。無論您開發了多麼出色的範例，您都需要在某些時候擴展它或偏離它。能夠做到這一點至關重要。
- 即使兩種類型看似無關，您也應該能夠在相同的事務和邏輯上下文中操作它們。
- 正如@Habibillah 指出的那樣，不要忘記測試/可測試性。
- 當您需要傳遞項目時，不要重新發明輪子（即使用完成工作的現有工具）。然而，這是一個很好的問題，創建自己的對象持久性/檢索方法是一個很好的學術練習。