首页 > 代码库 > C# 如何通过拼接XML调用存储过程来优化系统性能
C# 如何通过拼接XML调用存储过程来优化系统性能
平常新增多条记录，需要多次访问数据库，这样会影响性能；如果把新增的数据拼接成XML形式，作为参数传给存储过程来处理，这只访问数据库一次，执行速度会快很多。
1.C#代码如下：
//拼接XMLprivate static string MovementItemWmsInfoToXML = "<MovementItemWmsInfoTO SkuCode=\"{0}\" MovenmentCode=\"{1}\" Effect=\"{2}\" Skunum=\"{3}\" PropertityStock=\"{4}\" " + "PromotionCode=\"{5}\" StockoutCount=\"{6}\" StockSerialNumber=\"{7}\" UnitPrice=\"{8}\" ShipperId=\"{9}\"/>";/// <summary>/// 调用存储过程插入MovementItemWmsInfo信息/// </summary>/// <param name="list"></param>/// <returns></returns>public int InsertMovementItemWmsInfoByListstring(List<MovementItemWmsInfoTO> list){ int result = 0; try { if (list != null && list.Count > 0) { StringBuilder strBuilder = new StringBuilder(); foreach (MovementItemWmsInfoTO item in list) { strBuilder.AppendLine(string.Format(MovementItemWmsInfoToXML, item.SkuCode, item.MovenmentCode, item.Effect, item.Skunum, item.PropertityStock, item.PromotionCode.Replace("&", "%"), item.StockoutCount, item.StockSerialNumber, !string.IsNullOrEmpty(item.UnitPrice) ? double.Parse(item.UnitPrice).ToString("0.00") : "0" ,item.ShipperId)); } List<SqlParameter> param = new List<SqlParameter>(); param.Add(new SqlParameter("@MovementItemWmsInfoToXML", strBuilder.ToString())); result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connectionString, CommandType.StoredProcedure, "Proc_InsertMovementItemWmsInfo", param.ToArray())); LogInfo.GetErrorInfoByExcetion("[移库单拆分]添加总数：“" + list.Count + "”,执行成功数“" + result + "”。\r\n SQL语句：" + strBuilder.ToString()); } return result; } catch (Exception ee) { LogInfo.GetErrorInfoByExcetion("异常信息:"+ee.Message); return result; }}
2.SQL脚本：
--====================================================-- Author:Hamilton Tan-- Create Date:2014-07-22-- Description:插入MovementItemWmsInfo信息--====================================================CREATE PROCEDURE [dbo].[Proc_InsertMovementItemWmsInfo]@MovementItemWmsInfoToXML XMLASBEGIN DECLARE @ERROR INT SET @ERROR = 0; DECLARE @rows INT; DECLARE @index INT SET @index=1; DECLARE @StockSerialNumber INT; DECLARE @SkuCode NVARCHAR(200); DECLARE @Effect VARCHAR(500); DECLARE @PropertityStock INT; DECLARE @UnitPrice numeric(18,2); DECLARE @shipperid INT --解析移库单MovementItemWmsInfo的XML信息 IF @MovementItemWmsInfoTOXML IS NOT NULL BEGIN DECLARE @Tb_MovementItem TABLE ( [ID] int IDENTITY(1,1), [SkuCode] [nvarchar](200) NULL, [MovenmentCode] [nvarchar](200) NULL, [Effect] [varchar](500) NULL, [Skunum] [int] NULL, [PropertityStock] [int] NULL, [PromotionCode] [nvarchar](200) NULL, [StockoutCount] [int] NULL, [StockSerialNumber] [int] NULL, [UnitPrice] [numeric](18, 3) NULL, [ShipperId] [int] NULL ); BEGIN TRY INSERT @Tb_MovementItem SELECT R.c.value(‘@SkuCode‘,‘nvarchar(200)‘), R.c.value(‘@MovenmentCode‘,‘nvarchar(200)‘), R.c.value(‘@Effect‘,‘varchar(500)‘), R.c.value(‘@Skunum‘,‘int‘), R.c.value(‘@PropertityStock‘,‘int‘), R.c.value(‘@PromotionCode‘,‘nvarchar(200)‘), R.c.value(‘@StockoutCount‘,‘int‘), R.c.value(‘@StockSerialNumber‘,‘int‘), R.c.value(‘@UnitPrice‘,‘numeric(18,2)‘), R.c.value(‘@ShipperId‘,‘int‘) FROM @MovementItemWmsInfoTOXML.nodes(‘MovementItemWmsInfoTO‘) R(c) --R为行(Row),c为列(Column), /* 也可以这样写 SELECT Y.x.value(‘@SkuCode‘,‘nvarchar(200)‘), Y.x.value(‘@MovenmentCode‘,‘nvarchar(200)‘), Y.x.value(‘@Effect‘,‘varchar(500)‘), Y.x.value(‘@Skunum‘,‘int‘), Y.x.value(‘@PropertityStock‘,‘int‘), Y.x.value(‘@PromotionCode‘,‘nvarchar(200)‘), Y.x.value(‘@StockoutCount‘,‘int‘), Y.x.value(‘@StockSerialNumber‘,‘int‘), Y.x.value(‘@UnitPrice‘,‘numeric(18,2)‘), Y.x.value(‘@ShipperId‘,‘int‘) FROM @MovementItemWmsInfoTOXML.nodes(‘MovementItemWmsInfoTO‘) Y(x) --R为行(Row),c为列(Column), */ /* <Rows> <Row Name="1" Value="">abcde</Row> <Row Name="2" Value="">abcde</Row> </Rows> SELECT X.y.value(‘@Name‘,‘nvarchar(200)‘), 为1 X.y.value(‘Row[1]‘,‘nvarchar(200)‘)为abcde FROM @MovementItemWmsInfoTOXML.nodes(‘MovementItemWmsInfoTO‘) X(y) */ END TRY BEGIN CATCH SET @ERROR =-1; END CATCH --获取总记录数 SELECT @rows = COUNT(1) FROM @Tb_MovementItem --循环处理 while(@rows>0) begin select @StockSerialNumber=StockSerialNumber,@SkuCode=SkuCode,@Effect=Effect ,@PropertityStock=PropertityStock,@UnitPrice= UnitPrice,@shipperid= ShipperId from @Tb_MovementItem where ID=@index -- 不能出现StockSerialNumber,SkuCode ,Effect,PropertityStock,UnitPrice,ShipperId相同的记录 IF NOT EXISTS (select 1 from MovementItemWmsInfo where StockSerialNumber=@StockSerialNumber and SkuCode = @SkuCode and Effect = @Effect and PropertityStock = @PropertityStock and UnitPrice = @UnitPrice and ShipperId = @shipperid) BEGIN --插入数据 INSERT INTO [MovementItemWmsInfo]([SkuCode],[MovenmentCode],[Effect],[Skunum],[PropertityStock],[PromotionCode],[StockoutCount],[StockSerialNumber],[UnitPrice],[ShipperId]) SELECT [SkuCode],[MovenmentCode],[Effect],[Skunum],[PropertityStock],Replace([PromotionCode],‘%‘,‘&‘),[StockoutCount],[StockSerialNumber],[UnitPrice],[ShipperId] FROM @Tb_MovementItem END set @rows=@rows-1; set @index=@index+1; end END select @Error; END
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。