SqlServer存储过程传入Table参数

浏览数：22 / 时间：2015年06月12日

今天是周日，刚好有空闲时间整理一下这些天工作业务中遇到的问题。

有时候我们有这样一个需求，就是在后台中传过来一个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] 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             ( 
11               [Year] ,
12               [Month] ,
13               CutoffDate ,
14               PayrollDate ,
15               EnterUser ,
16               EnterDate ,
17               LastUpdatedUser ,
18               LastUpdatedDate
19             )
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 END
32 
33 GO