1、新建自定义表结构
注意字段的顺序要一致 类型要一样 不然会出错
CREATE TYPE [dbo].[HBForHBGHDR] AS TABLE(
[序号] [int] NULL,
[客户编号] [varchar](15) NULL
)
GO
2、建立存储过程
alter PROCEDURE UP_DRGHHB
@HBList HBForHBGHDR readonly,
@returnCode varchar(10) output,
@returnMsg varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @rowcount int
set @returnCode=1
select @rowcount=count(1) from @HBList
--set @returnMsg=(select top 1 [客户编号] from @HBList)
update jc_hb set yl5=1 where khbh in(select [客户编号] from @HBList)
set @returnMsg='总共提交条数:'+convert(varchar(10),@rowcount)+',修改成功:'+ convert(varchar(10),@@rowcount)+'条'
END
GO
3、调用存储过程
string returnCode = string.Empty;
string returnMsg = string.Empty;
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@returnCode",returnCode),
new SqlParameter("@returnMsg",returnMsg),
new SqlParameter("@HBList",dt)
};
paras[0].Direction = ParameterDirection.Output;
paras[1].Direction = ParameterDirection.Output;
paras[1].Size = 50;
paras[2].TypeName = "HBForHBGHDR";
DataSet ds = DbHelperSQL.RunProcedure("[UP_DRGHHB]", paras, "tb1");
returnMsg = paras[1].Value.ToString();
returnCode = paras[0].Value.ToString();
if (returnCode != "1")
{
this.lblError.Text = "批量插入数据失败:" + returnMsg;
}
else
this.lblError.Text = "批量插入数据成功:" + returnMsg;