首页 > 代码库 > SqlServer存储过程传入Table参数
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 [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
就这样大功告成,用起来相当方便,就不用通过拼接字符串进行数据的插入,更新操作了。
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。