[转载]SqlServer存储过程传入Table参数 – 世民后人 – 博客园.
今天是周日,刚好有空闲时间整理一下这些天工作业务中遇到的问题。
有时候我们有这样一个需求,就是在后台中传过来一个IList<类>的泛型集合数据,该集合是某个类的实例集合体,然后将该集合中的实 例的数据一个个地插入到数据库或者更新到数据库中去。一开始我想到的方法是拼接字符串,然后通过存储过程对接收到的字符串进行截取,再一个个地插入或者更 新到数据库中去,这是最原始的方法,不过过程会比较复杂,想到这就头疼。后来查找发现说SQLServer2008中为存储过程添加了一个新特性,可以传 递表类型的参数,既然可以传递表类型参数,那问题就变得简单啦。以下是开发中写的code.
1.ASP.NET后台:
/// <summary> /// Add the PayrollCycle /// </summary> /// <param name="payrollCycle">payrollCycle</param> /// <returns>bool</returns> public bool AddPayrollCycle(IList<PayrollCycle> payrollCycles) { DataTable dataTable=new DataTable(); dataTable.Columns.Add("Year",typeof(int)); dataTable.Columns.Add("Month",typeof(int)); dataTable.Columns.Add("CutoffDate",typeof(int)); dataTable.Columns.Add("PayrollDate",typeof(int)); dataTable.Columns.Add("EnterUser",typeof(string)); dataTable.Columns.Add("EnterDate",typeof(DateTime)); dataTable.Columns.Add("LastUpdatedUser",typeof(string)); dataTable.Columns.Add("LastUpdatedDate",typeof(DateTime)); foreach (PayrollCycle p in payrollCycles) { DataRow dataRow = dataTable.NewRow(); dataRow["Year"] = p.Year; dataRow["Month"] = p.Month; dataRow["CutoffDate"] = p.CutoffDate; dataRow["PayrollDate"] = p.PayrollDate; dataRow["EnterUser"] = UserSession.LogOnUserAccount; dataRow["EnterDate"] = DateTime.Now; dataRow["LastUpdatedUser"] = UserSession.LogOnUserAccount; dataRow["LastUpdatedDate"] = DateTime.Now; dataTable.Rows.Add(dataRow); } SqlParameter[]paras=new SqlParameter[] { new SqlParameter("@PayrollCycles",dataTable) }; return SqlHelper.ExecuteNonQuery("MCU.USP_AddPayrollCycles", paras) > 0; }
为dataTable添加column的时候,必须明确该列的typeof,否则在存储过程当中会把传入的该列当成varchar类型看待,导致某些类型转换失败
2.在SQLServer中先定义一个Table类型的Type:
CREATE TYPE [MCU].[PayrollCycleType] AS TABLE( [YEAR] [int] NOT NULL, [Month] [int] NOT NULL, [CutoffDate] [int] NOT NULL, [PayrollDate] [int] NOT NULL, [EnterUser] [varchar](20) NULL, [EnterDate] [datetime] NULL, [LastUpdatedUser] [varchar](20) NULL, [LastUpdatedDate] [datetime] NULL ) GO
接着编写一个传入上步骤中定义的表类型的参数的存储过程,该参数为Readonly(作为表类型参数必须为可读),代码如下:
CREATE PROCEDURE [MCU].[USP_AddPayrollCycles] ( @PayrollCycles MCU.PayrollCycleType Readonly ) AS BEGIN SET NOCOUNT ON BEGIN TRANSACTION INSERT INTO MCU.PayrollCycle ( [Year] , [Month] , CutoffDate , PayrollDate , EnterUser , EnterDate , LastUpdatedUser , LastUpdatedDate ) SELECT [Year] , [Month] , CutoffDate , PayrollDate , EnterUser , EnterDate , LastUpdatedUser , LastUpdatedDate FROM @PayrollCycles COMMIT TRANSACTION END GO
就这样大功告成,用起来相当方便,就不用通过拼接字符串进行数据的插入,更新操作了。
绿色通道: 好文要顶 关注我 收藏该文与我联系