SqlServer預存程序傳入Table參數

標籤：style   blog   color   資料   io   for   問題   cti   

今天是周日，剛好有空閑時間整理一下這些天工作業務中遇到的問題。

有時候我們有這樣一個需求，就是在後台中傳過來一個IList<類>的泛型集合資料,該集合是某個類的執行個體集合體，然後將該集合中的執行個體的資料一個個地插入到資料庫或者更新到資料庫中去。一開始我想到的方法是拼接字串，然後通過預存程序對接收到的字串進行截取，再一個個地插入或者更新到資料庫中去，這是最原始的方法，不過過程會比較複雜，想到這就頭疼。後來尋找發現說SqlServer2008中為預存程序添加了一個新特性，可以傳遞表類型的參數，既然可以傳遞表型別參數，那問題就變得簡單啦。以下是開發中寫的code.

1.asp.net後台：

 1         /// <summary> 2         /// Add the PayrollCycle 3         /// </summary> 4         /// <param name="payrollCycle">payrollCycle</param> 5         /// <returns>bool</returns> 6         public bool AddPayrollCycle(IList<PayrollCycle> payrollCycles) 7         { 8             DataTable dataTable=new DataTable(); 9             dataTable.Columns.Add("Year",typeof(int));10             dataTable.Columns.Add("Month",typeof(int));11             dataTable.Columns.Add("CutoffDate",typeof(int));12             dataTable.Columns.Add("PayrollDate",typeof(int));13             dataTable.Columns.Add("EnterUser",typeof(string));14             dataTable.Columns.Add("EnterDate",typeof(DateTime));15             dataTable.Columns.Add("LastUpdatedUser",typeof(string));16             dataTable.Columns.Add("LastUpdatedDate",typeof(DateTime));17             foreach (PayrollCycle p in payrollCycles)18             {19                 DataRow dataRow = dataTable.NewRow();20                 dataRow["Year"] = p.Year;21                 dataRow["Month"] = p.Month;22                 dataRow["CutoffDate"] = p.CutoffDate;23                 dataRow["PayrollDate"] = p.PayrollDate;24                 dataRow["EnterUser"] = UserSession.LogOnUserAccount;25                 dataRow["EnterDate"] = DateTime.Now;26                 dataRow["LastUpdatedUser"] = UserSession.LogOnUserAccount;27                 dataRow["LastUpdatedDate"] = DateTime.Now;28                 dataTable.Rows.Add(dataRow);29             }30             31             SqlParameter[]paras=new SqlParameter[]32                                     {33                                         new SqlParameter("@PayrollCycles",dataTable)34                                     };35             return SqlHelper.ExecuteNonQuery("MCU.USP_AddPayrollCycles", paras) > 0;36         }

為dataTable添加column的時候，必須明確該列的typeof，否則在預存程序當中會把傳入的該列當成varchar類型看待，導致某些類型轉換失敗

2.在SqlServer中先定義一個Table類型的Type：

 1 CREATE TYPE [MCU].[PayrollCycleType] AS TABLE( 2     [YEAR] [int] NOT NULL, 3     [Month] [int] NOT NULL, 4     [CutoffDate] [int] NOT NULL, 5     [PayrollDate] [int] NOT NULL, 6     [EnterUser] [varchar](20) NULL, 7     [EnterDate] [datetime] NULL, 8     [LastUpdatedUser] [varchar](20) NULL, 9     [LastUpdatedDate] [datetime] NULL10 )11 GO

接著編寫一個傳入上步驟中定義的表類型的參數的預存程序，該參數為Readonly(作為表型別參數必須為可讀)，代碼如下：

 1 CREATE PROCEDURE [MCU].[USP_AddPayrollCycles] 2 ( 3     @PayrollCycles MCU.PayrollCycleType Readonly  4 ) 5 AS 6 BEGIN 7     SET NOCOUNT ON 8     BEGIN TRANSACTION 9     INSERT INTO MCU.PayrollCycle10             ( 11               [Year] ,12               [Month] ,13               CutoffDate ,14               PayrollDate ,15               EnterUser ,16               EnterDate ,17               LastUpdatedUser ,18               LastUpdatedDate19             )20     SELECT    21               [Year] ,22               [Month] ,23               CutoffDate ,24               PayrollDate ,25               EnterUser ,26               EnterDate ,27               LastUpdatedUser ,28               LastUpdatedDate 29               FROM @PayrollCycles 30     COMMIT TRANSACTION           31 END32 33 GO

就這樣大功告成，用起來相當方便，就不用通過拼接字串進行資料的插入，更新操作了。