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

  

 

转载于:https://www.cnblogs.com/jiamengyang/p/10441946.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值