c#表類型參數批量導入與批量更新

c#

if (classify.ListL.Count > 0)
                                {
                                    classify.ListL.ForEach(w =>
                                    {
                                        w.ClassifyCode = classify.ClassifyCode;
                                        w.UseCode = (dt1.Rows[0]["UseCode"].ToString());

                                    });

                                    using (var conn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings["DbHelperConnectionString"].Trim()))
                                    {
                                        conn.Open();
                                        //// Invokes the stored procedure.
                                        using (cmd = new SqlCommand("MaterialManage_BatchUseSaveSub", conn))
                                        {
                                            cmd.CommandType = CommandType.StoredProcedure;
                                            cmd.Parameters.Add(new SqlParameter("@ItemTable", SqlDbType.Structured) { Value = Tool.ListToDataTable(classify.ListL) });
                                            cmd.ExecuteNonQuery();
                                        }
                                    }
                                }
      

　　sqlprocedure insert

USE [Preschool_ABC]
GO
/****** Object:  StoredProcedure [dbo].[MaterialManage_BatchUseSaveSub]    Script Date: 2019/2/27 9:09:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MaterialManage_BatchUseSaveSub]
(

		 
         @ItemTable MaterialManage_BatchUse_Item_BatchInsert READONLY
	

)
AS
	SET XACT_ABORT ON
	SET NOCOUNT ON 	
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	
	DECLARE @intTranCount INT 
	SET @intTranCount=@@TranCount
	IF @intTranCount<> 0  SAVE TRANSACTION Savepoint ELSE BEGIN TRANSACTION 

	DECLARE @TemplateCode UNIQUEIDENTIFIER

	BEGIN
		
		INSERT INTO dbo.MaterialManage_BatchUse_Item
		        (  
		          UseCode ,
		          ClassifyCode ,
		          ListCode ,
		          ReserveNum ,
		          ApplyNum
		        )
		SELECT UseCode = CAST(UseCode AS UNIQUEIDENTIFIER) , ClassifyCode = CAST(ClassifyCode AS UNIQUEIDENTIFIER) , CAST(ListCode AS UNIQUEIDENTIFIER), ReserveNum, ApplyNum FROM @ItemTable
		
		
	END

--完成--
	IF @intTranCount<>@@TranCount COMMIT TRANSACTION		 
	RETURN
	
ErrHandle:
	 IF @intTranCount=@@TranCount 
	BEGIN
		ROLLBACK TRANSACTION Savepoint 
		RETURN
	END
	ELSE
	BEGIN
		ROLLBACK TRANSACTION 
		RETURN
	END
      

　　

update

CREATE PROCEDURE [MCU].[USP_UpdatePayrollCycle]
(
    @PayrollCycles MCU.PayrollCycleType READONLY,
    @TypeOfDate NVARCHAR(7)
)
AS
BEGIN
    SET NOCOUNT ON
    --declare an table
    DECLARE @temp AS MCU.PayrollCycleType
   
    --Update the PayrollCycle
    IF(@TypeOfDate='Payroll')
         BEGIN 
            UPDATE MCU.PayrollCycle 
            SET 
                PayrollDate=t.PayrollDate
            FROM
                @temp t
                WHERE  MCU.PayrollCycle.[Year]=t.[Year] AND MCU.PayrollCycle.[Month]=t.[Month]
         END
             
    IF(@TypeOfDate='Cut-off')
        BEGIN
            UPDATE MCU.PayrollCycle
            SET
                CutoffDate=t.CutOffDate
            FROM @temp t
                   WHERE  MCU.PayrollCycle.[Year]=t.[Year] AND MCU.PayrollCycle.[Month]=t.[Month]
        END                   
END

GO
      

　　表值類型

USE [Preschool_ABC]
GO

/****** Object:  UserDefinedTableType [dbo].[MaterialManage_BatchUse_Item_BatchInsert]    Script Date: 2019/2/27 9:21:11 ******/
CREATE TYPE [dbo].[MaterialManage_BatchUse_Item_BatchInsert] AS TABLE(
	[UseCode] [VARCHAR](50) NOT NULL,
	[ClassifyCode] [VARCHAR](50) NOT NULL,
	[ListCode] [VARCHAR](50) NOT NULL,
	[ReserveNum] [INT] NOT NULL,
	[ApplyNum] [INT] NOT NULL
)
GO