[ADO.NET] 利用 SqlDataAdapter 產生預存程序

閱讀 極意之道次世代 .NET Framework 3.5資料庫開發聖典 發現，原來 SqlDataAdapter Wizard 可以根據 SelectCommand 產生對應的 INSERT、UPDATE 和 DELETE 預存程序，雖然說 Wizard 產生的 TSQL 語法慘不忍賭，但總比重頭開始，一個字一個字打來的好

SqlDataAdapter 選擇命令類型，選擇 [建立新的預存程序]，之前都直接手打預存程序，然後選擇 [使用現有的預存程序]，完全忽視第二個選項，Orz

輸入 SelectCommand，拿 AdventureWorks2014 Employee 來當成目標

命名 Select、Insert、Update 和 Delete 預存程序的名稱

預覽 SQL 指令碼，就可以看見要在 SQL Server 內建立的預存程序

產生預存程序

在 SQL Server 內可以看見預存程序已經被建立啦

以 Update 語法來看，Wizard 產生的 TSQL 真的是很慘，尤其是那串 WHERE 條件

USE [AdventureWorks2014]
GO

/****** Object:  StoredProcedure [dbo].[NewUpdateCommand]    Script Date: 2016/3/24 下午 01:32:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NewUpdateCommand]
(
    @BusinessEntityID int,
    @NationalIDNumber nvarchar(15),
    @LoginID nvarchar(256),
    @OrganizationNode hierarchyid,
    @JobTitle nvarchar(50),
    @BirthDate date,
    @MaritalStatus nchar(1),
    @Gender nchar(1),
    @HireDate date,
    @SalariedFlag Flag,
    @VacationHours smallint,
    @SickLeaveHours smallint,
    @CurrentFlag Flag,
    @rowguid uniqueidentifier,
    @ModifiedDate datetime,
    @Original_BusinessEntityID int,
    @Original_NationalIDNumber nvarchar(15),
    @Original_LoginID nvarchar(256),
    @IsNull_OrganizationNode Int,
    @Original_OrganizationNode hierarchyid,
    @IsNull_OrganizationLevel Int,
    @Original_OrganizationLevel smallint,
    @Original_JobTitle nvarchar(50),
    @Original_BirthDate date,
    @Original_MaritalStatus nchar(1),
    @Original_Gender nchar(1),
    @Original_HireDate date,
    @Original_SalariedFlag Flag,
    @Original_VacationHours smallint,
    @Original_SickLeaveHours smallint,
    @Original_CurrentFlag Flag,
    @Original_rowguid uniqueidentifier,
    @Original_ModifiedDate datetime
)
AS

SET NOCOUNT OFF;

UPDATE [HumanResources].[Employee]
SET [BusinessEntityID] = @BusinessEntityID
    ,[NationalIDNumber] = @NationalIDNumber
    ,[LoginID] = @LoginID
    ,[OrganizationNode] = @OrganizationNode
    ,[JobTitle] = @JobTitle
    ,[BirthDate] = @BirthDate
    ,[MaritalStatus] = @MaritalStatus
    ,[Gender] = @Gender
    ,[HireDate] = @HireDate
    ,[SalariedFlag] = @SalariedFlag
    ,[VacationHours] = @VacationHours
    ,[SickLeaveHours] = @SickLeaveHours
    ,[CurrentFlag] = @CurrentFlag
    ,[rowguid] = @rowguid
    ,[ModifiedDate] = @ModifiedDate
WHERE (([BusinessEntityID] = @Original_BusinessEntityID)
    AND ([NationalIDNumber] = @Original_NationalIDNumber)
    AND ([LoginID] = @Original_LoginID)
    AND ((@IsNull_OrganizationNode = 1
    AND [OrganizationNode] IS NULL)
    OR ([OrganizationNode] = @Original_OrganizationNode))
    AND ((@IsNull_OrganizationLevel = 1
    AND [OrganizationLevel] IS NULL)
    OR ([OrganizationLevel] = @Original_OrganizationLevel))
    AND ([JobTitle] = @Original_JobTitle)
    AND ([BirthDate] = @Original_BirthDate)
    AND ([MaritalStatus] = @Original_MaritalStatus)
    AND ([Gender] = @Original_Gender)
    AND ([HireDate] = @Original_HireDate)
    AND ([SalariedFlag] = @Original_SalariedFlag)
    AND ([VacationHours] = @Original_VacationHours)
    AND ([SickLeaveHours] = @Original_SickLeaveHours)
    AND ([CurrentFlag] = @Original_CurrentFlag)
    AND ([rowguid] = @Original_rowguid)
    AND ([ModifiedDate] = @Original_ModifiedDate));

SELECT
    BusinessEntityID
    ,NationalIDNumber
    ,LoginID
    ,OrganizationNode
    ,OrganizationLevel
    ,JobTitle
    ,BirthDate
    ,MaritalStatus
    ,Gender
    ,HireDate
    ,SalariedFlag
    ,VacationHours
    ,SickLeaveHours
    ,CurrentFlag
    ,rowguid
    ,ModifiedDate
FROM HumanResources.Employee
WHERE (BusinessEntityID = @BusinessEntityID)
GO

產生預存程序後，要調整 TSQL 語法，千萬不要直接拿來用，另外在 使用預存程序修改資料 有特別提到

如果您要使用 SQL Server 預存程序搭配 SqlDataAdapter 來編輯或刪除資料，請務必不要在預存程序定義中使用 SET NOCOUNT ON。因為這樣會讓傳回的受影響資料列計數成為零，而 DataAdapter 會將它解譯為並行衝突。在此事件中，系統會擲回 DBConcurrencyException。

要特別注意喔