SqlServer存储过程传入Table参数
今天是周日，刚好有空闲时间整理一下这些天工作业务中遇到的问题。
有时候我们有这样一个需求，就是在后台中传过来一个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 [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] NULL 10 ) 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.PayrollCycle 10 ( [Year] , 11 [Month] , 12 CutoffDate , 13 PayrollDate , 14 EnterUser , 15 EnterDate , 16 LastUpdatedUser , 17 LastUpdatedDate 18 ) 19 SELECT [Year] , 20 [Month] , 21 CutoffDate , 22 PayrollDate , 23 EnterUser , 24 EnterDate , 25 LastUpdatedUser , 26 LastUpdatedDate 27 FROM @PayrollCycles 28 COMMIT TRANSACTION 29 END 30 31 GO
更新操作：
1 CREATE PROCEDURE [MCU].[USP_UpdatePayrollCycle] 2 ( 3 @PayrollCycles MCU.PayrollCycleType READONLY, 4 @TypeOfDate NVARCHAR(7) 5 ) 6 AS 7 BEGIN 8 SET NOCOUNT ON 9 --declare an table 10 DECLARE @temp AS MCU.PayrollCycleType 11 --insert into @temp from @PayrollCycles 12 INSERT INTO @temp 13 ( [YEAR] , 14 [Month] , 15 CutoffDate , 16 PayrollDate 17 ) 18 SELECT 19 [YEAR] , 20 [Month] , 21 CutoffDate , 22 PayrollDate 23 FROM @PayrollCycles 24 25 --Update the PayrollCycle 26 IF(@TypeOfDate='Payroll') 27 BEGIN 28 UPDATE MCU.PayrollCycle 29 SET 30 PayrollDate=t.PayrollDate 31 FROM 32 @temp t 33 WHERE MCU.PayrollCycle.[Year]=t.[Year] AND MCU.PayrollCycle.[Month]=t.[Month] 34 END 35 36 IF(@TypeOfDate='Cut-off') 37 BEGIN 38 UPDATE MCU.PayrollCycle 39 SET 40 CutoffDate=t.CutOffDate 41 FROM @temp t 42 WHERE MCU.PayrollCycle.[Year]=t.[Year] AND MCU.PayrollCycle.[Month]=t.[Month] 43 END 44 END 45 46 GO
就这样大功告成，用起来相当方便，就不用通过拼接字符串进行数据的插入，更新操作了。
