SQL Server stored procedure passed in table parameter

Today is Sunday, just have free time to sort out these days of work in the business of the problems encountered.

Sometimes we have a need to pass in the background a ilist< class > generic collection data, which is the instance collection of a class, and then inserts the data from the instance of the collection into the database or into the database. At first I thought of the method is to splice strings, and then through the stored procedure docking received string interception, and then insert or update to the database, this is the most primitive method, but the process will be more complex, think of this headache. Later found that SqlServer2008 added a new feature to the stored procedure, you can pass parameters of the table type, since the table type parameters can be passed, the problem becomes simple. The following code is written in the development.

1.asp.net Backstage:

1         /// <summary>2         ///ADD the Payrollcycle3         /// </summary>4         /// <param name= "Payrollcycle" >payrollcycle</param>5         /// <returns>BOOL</returns>6          Public BOOLAddpayrollcycle (ilist<payrollcycle>payrollcycles)7         {8DataTable datatable=NewDataTable ();9DATATABLE.COLUMNS.ADD (" Year",typeof(int));TenDATATABLE.COLUMNS.ADD ("Month",typeof(int)); OneDATATABLE.COLUMNS.ADD ("cutoffdate",typeof(int)); ADATATABLE.COLUMNS.ADD ("payrolldate",typeof(int)); -DATATABLE.COLUMNS.ADD ("Enteruser",typeof(string)); -DATATABLE.COLUMNS.ADD ("enterdate",typeof(DateTime)); theDATATABLE.COLUMNS.ADD ("Lastupdateduser",typeof(string)); -DATATABLE.COLUMNS.ADD ("lastupdateddate",typeof(DateTime)); -             foreach(Payrollcycle Pinchpayrollcycles) -             { +DataRow datarow =Datatable.newrow (); -datarow[" Year"] =p.year; +datarow["Month"] =P.month; Adatarow["cutoffdate"] =p.cutoffdate; atdatarow["payrolldate"] =p.payrolldate; -datarow["Enteruser"] =Usersession.logonuseraccount; -datarow["enterdate"] =DateTime.Now; -datarow["Lastupdateduser"] =Usersession.logonuseraccount; -datarow["lastupdateddate"] =DateTime.Now; - DataTable.Rows.Add (dataRow); in             } -              tosqlparameter[]paras=Newsqlparameter[] +                                     { -                                         NewSqlParameter ("@PayrollCycles", dataTable) the                                     }; *             returnSqlhelper.executenonquery ("MCU. Usp_addpayrollcycles", paras) >0; $}

When you add column to a DataTable, you must explicitly typeof the column, otherwise the incoming column will be treated as a varchar type in the stored procedure, causing some type conversions to fail

2. Define the type of table type in SQL Server first:

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] ( -) NULL,7 [Enterdate] [datetime] NULL,8[Lastupdateduser] [varchar] ( -) NULL,9 [Lastupdateddate] [datetime] NULLTen ) OneGO

Then write a stored procedure that passes the parameters of the table type defined in the previous step, which is readonly (which must be readable as a table-type parameter), with the following code:

1 CREATE PROCEDURE [MCU]. [Usp_addpayrollcycles]2 (3 @PayrollCycles MCU. Payrollcycletype Readonly4 )5  as6 BEGIN7 SET NOCOUNT on8 BEGIN TRANSACTION9 INSERT into MCU. PayrollcycleTen             (  One [year], A [Month], - Cutoffdate, - Payrolldate, the Enteruser, - Enterdate, - Lastupdateduser, - lastupdateddate +             ) - SELECT + [year], A [Month], at Cutoffdate, - Payrolldate, - Enteruser, - Enterdate, - Lastupdateduser, - lastupdateddate in From @PayrollCycles - COMMIT TRANSACTION to END +  -GO

In this way, it is quite convenient to use it, it is not necessary to insert the data by stitching the string, update the operation.