首先编辑好XML格式,格式为<Root><Row 字段名="值" /><Row 字段名="值" />.....<Root/>,这里我做了两次操作,需求需要。完全一次就可以执行全部。
比如: (因为我的是读取不确定行的Excel数据所以 循环条件了肯定为真的 1==1)
strXML.Append("<Root>");
int index = 0;
{
if (index >= 3)
{
break;
}
else
{
if (clsEx.GetValue(2 + i, 1) == "")
{
index++;
}
else
{
index = 0;
strXML.Append("<Row FInComeAccount=\"" + clsEx.GetValue(2 + i, 1) + "\"");
strXML.Append(" FClientName=\"" + clsEx.GetValue(2 + i, 2) + "\"");
strXML.Append(" FInComeDate=\"" + clsEx.GetDateTimeValue(2 + i, 3) + "\"");
strXML.Append(" FInComeAmount=\"" + clsEx.GetValue(2 + i, 4) + "\"");
strXML.Append(" FDuring=\"" + clsEx.GetDateTimeValue(2 + i, 5) + "\"");
strXML.Append(" FNumber=\"" + clsEx.GetValue(2 + i, 6) + "\"");
strXML.Append(" FSJCode=\"" + clsEx.GetValue(2 + i, 7) + "\"");
strXML.Append(" FCreateDate=\"" + DateTime.Now + "\"");
strXML.Append(" RowIndex=\"" + (2 + i) + "\" />");
}
}
}
strXML.Append("</Root>");
然后执行一次存储过程,值就是strXML 编辑好的XML格式
sp_Import_PrepareClientAccount ‘“+strXML.ToString()+”’
sql 存储过程:(验证数据合法性)
/****** Object: StoredProcedure [dbo].[sp_Import_PrepareClientAccount] Script Date: 09/26/2013 10:47:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: hefeng
-- Create date: 2013-9-25
-- Description: 批量添加经销商账目
-- =============================================
ALTER PROCEDURE [dbo].[sp_Import_PrepareClientAccount]
@XmlStr NVARCHAR(max)
AS
BEGIN
--DECLARE @XmlStr NVARCHAR(max)
--SET @XmlStr='<Root>'
--+'<Row FInComeAccount="HSBC SH" FClientName="宜兴市宜通汽车销售服务有限公司" FInComeDate="7/31/2013 8:00:00 AM" FInComeAmount="32500" FDuring="7/31/2013 8:00:00 AM" FNumber="130731BS40096555" FCreateDate="9/24/2013 5:32:32 PM" RowIndex="16" /> '
--+'</Root>'
DECLARE @docHandle int;
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlStr;
SELECT * INTO #temp
FROM OPENXML(@docHandle, N'Root/Row')
WITH (FInComeAccount varchar(50),FClientName varchar(200),FInComeDate datetime,FInComeAmount decimal,FDuring varchar(50),FNumber varchar(50),FSJCode varchar(50), FCreateDate datetime,RowIndex int);
EXEC sp_xml_removedocument @docHandle;
--select * from #temp
CREATE TABLE #ErrorList
(
StrMessage varchar(200)
)
---------查询,如果经销商名称及编号存在,则合法,否则添加到错误表
DECLARE @row varchar(5000);
select @row=ISNULL(@row+',','')+convert(varchar(50), #temp.RowIndex) from t_Dic_Customlist_RelationClient rc
inner join t_Dic_Customlist_RelationClientDetail rcd on rc.ID = rcd.FID
inner join #temp on rc.名称 = #temp.FClientName and rcd.FSJCode = #temp.FSJCode
---------查询编号是否存在,存在则添加错误表信息
IF EXISTS(select * from t_Dic_ClientAccount ca inner join #temp on ca.FNumber=#temp.FNumber)
BEGIN
insert into #ErrorList select '第'+CONVERT(varchar(50),#temp.RowIndex)+'条记录参考号已存在,请更改' from t_Dic_ClientAccount ca inner join #temp on ca.FNumber=#temp.FNumber
END
DECLARE @sql varchar(500);
SET @sql = 'insert into #ErrorList select ''第''+CONVERT(VARCHAR(50),RowIndex)+''条记录经销商不存在'' from #temp where RowIndex not in('+@row+')'
EXEC(@sql)
---返回错误信息表(如果错误信息表为空,则数据合法)
SELECT * FROM #ErrorList
DROP TABLE #temp
DROP TABLE #ErrorList
END
sql 执行批量插入(验证合法后,执行存储过程,同样传XML)
/****** Object: StoredProcedure [dbo].[sp_Import_InsertClientAccount] Script Date: 09/26/2013 10:50:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: hefeng
-- Create date: 2013-9-25
-- Description: 批量添加经销商账目并更改金额
-- =============================================
ALTER PROCEDURE [dbo].[sp_Import_InsertClientAccount]
@XmlStr NVARCHAR(max)
AS
BEGIN
--DECLARE @XmlStr NVARCHAR(max)
--SET @XmlStr='<Root>'
--+'<Row FInComeAccount="HSBC SH" FClientName="宜兴市宜通汽车销售服务有限公司" FInComeDate="7/31/2013 8:00:00 AM" FInComeAmount="32500" FDuring="7/31/2013 8:00:00 AM" FNumber="130731BS40096555" FCreateDate="9/24/2013 5:32:32 PM" RowIndex="16" /> '
--+'</Root>'
DECLARE @docHandle int;
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlStr;
SELECT * INTO #temp
FROM OPENXML(@docHandle, N'Root/Row')
WITH (FInComeAccount varchar(50),FClientName varchar(200),FInComeDate datetime,FInComeAmount decimal,FDuring varchar(50),FNumber varchar(50),FSJCode varchar(50), FCreateDate datetime,RowIndex int);
EXEC sp_xml_removedocument @docHandle;
------添加表,关联添加,因为是多个表之间主键关联
insert into t_Dic_ClientAccount select rc.ID,#temp.FInComeAccount,#temp.FInComeDate,#temp.FInComeAmount,#temp.FDuring,#temp.FNumber,#temp.FCreateDate from t_Dic_Customlist_RelationClient rc
inner join t_Dic_Customlist_RelationClientDetail rcd on rc.ID = rcd.FID
inner join #temp on rc.名称 = #temp.FClientName and rcd.FSJCode = #temp.FSJCode
------更新金额 ,关联更新
update rcd set rcd.FBalance = rcd.FBalance+#temp.FInComeAmount from t_Dic_Customlist_RelationClientDetail rcd
inner join t_Dic_Customlist_RelationClient rc on rc.ID = rcd.FID
inner join #temp on rc.名称 = #temp.FClientName and rcd.FSJCode = #temp.FSJCode
inner join t_Dic_ClientAccount ca on ca.FNumber =#temp.FNumber
DROP TABLE #temp
END
这只是一个思路,简单的代码应用。可以借鉴就借鉴。。。相对于批量操作数据,还是比较简单的。