C# 如何通过拼接XML调用存储过程来优化系统性能

2022-10-06 14:38:37

平常新增多条记录，需要多次访问数据库，这样会影响性能；如果把新增的数据拼接成XML形式，作为参数传给存储过程来处理，这只访问数据库一次，执行速度会快很多。

1.C#代码如下：XML拼接的字段不能出现&等其他执行存储过程不支持的符号，需要转义，C#里面的方法是 1.Server.HtmlEncode(参数值);2. ReplaceXmlStr(参数值);

/// <summary>

/// XML特殊字符替换

/// </summary>

/// <param name="str"></param>

public static string ReplaceXmlStr(string str)

{

    if (!string.IsNullOrEmpty(str))

    {

  str = str.Replace("'", " "); //20140420 add

  str = str.Replace("<", "&lt;");

  str = str.Replace(">", "&gt;");

  str = str.Replace("&", "&amp;");

  str = str.Replace("'", "&apos;");

  str = str.Replace("\"", "&quot;");

    }

  return str;

}

另外注意：

foreach循环不要这样写：

MovementItemWmsInfoToXM = 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)

strBuilder.AppendLine(MovementItemWmsInfoToXM);

应该这样写：

strBuilder.AppendFormat(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);

或strBuilder.Append(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));

//拼接XML

private 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 = ;

    try

    {

        if (list != null && list.Count > )

        {

            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") : ""

                        ,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 XML

AS

BEGIN

  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;

  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

     );

  --解析移库单MovementItemWmsInfo的XML信息

  IF @MovementItemWmsInfoTOXML IS NOT NULL

  BEGIN

     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

码农公寓

相关文章