/// <summary>        /// 查询订单信息        /// </summary>        /// <param name="WhereXML">查询条件XML格式</param>        /// <param name="OrderBy">排序字段</param>        /// <param name="PageIndex">当前页数</param>        /// <param name="PageSize">每页条数</param>        /// <param name="RecordCount">总记录数</param>        /// <returns></returns>        public List<OrderTO> SearchOrder(string WhereXML, string OrderBy, int PageIndex, int PageSize, out int RecordCount)        {            List<OrderTO> list = new List<OrderTO>();            try            {                Query qu = new Query();                qu.Procedure = "[dbo].[mp_Order_List]";                Query.ParameterItem item = new Query.ParameterItem();                item.Type = SqlDbType.Xml;                item.Value = WhereXML;                qu.Parameters.Add("Where", item);                //                item = new Query.ParameterItem();                item.Type = SqlDbType.NVarChar;                item.Value = OrderBy;                qu.Parameters.Add("OrderBy", item);                //                item = new Query.ParameterItem();                item.Type = SqlDbType.Int;                item.Value = PageIndex.ToString();                qu.Parameters.Add("PageIndex", item);                //                item = new Query.ParameterItem();                item.Type = SqlDbType.Int;                item.Value = PageSize.ToString();                qu.Parameters.Add("PageSize", item);                //                item = new Query.ParameterItem();                item.Direction = ParameterDirection.Output;                qu.Parameters.Add("RecordCount", item);                list = ExecuteProcAndGetOutput(qu, out RecordCount);                return list;            }            catch (Exception ex)            {                RecordCount = 0;                ErrorLog.Write("SearchOrder【订单跟踪查询】", ex);                return null;            }        }

create procedure [dbo].[mp_Order_List_Test](   @Where xml    --条件   ,@OrderBy varchar(100) --排序字段   ,@PageIndex int  --页码   ,@PageSize int   --每页显示行数   ,@RecordCount int output --记录数)as /************************************************************ Purpose : 订单跟踪查询** ** Date        Author      Version        Revision** -----      --------    ----------      ---** 2014/7/29   Ham Tan      1.0**********************************************************/begin  declare @sql nvarchar(max)  ,@OrderByStr nvarchar(1024) = ‘‘  ,@whereStr nvarchar(1024) = ‘‘  ,@params nvarchar(1024) = ‘‘      declare @Code nvarchar(50)  ,@SourceCode nvarchar(50)  ,@SourcePlate varchar(10)  ,@PayTimeForm varchar(10)  ,@PayTimeTo varchar(10)  ,@OrderNum varchar(10)  ,@OrderNumOpType varchar(20)    /*    <Query>    <Code>SO140820-1</Code>    <SourceCode>SO140820-1</SourceCode>    <SourcePlate>1</SourcePlate>    <OrderNum OpType="&gt;">1</OrderNum>    <PayTime From="2014-08-20" To="2014-08-20"></PayTime>  </Query>  */    --准备查询条件     select       @Code = R.c.value(‘(Code)[1]‘,‘nvarchar(50)‘) --编号      ,@SourceCode = R.c.value(‘(SourceCode)[1]‘,‘nvarchar(50)‘) --来源单号      ,@SourcePlate = R.c.value(‘(SourcePlate)[1]‘,‘varchar(10)‘) --来源平台      ,@PayTimeForm = R.c.value(‘(./PayTime/@From)[1]‘,‘varchar(10)‘) --付款开始时间      ,@PayTimeTo = R.c.value(‘(./PayTime/@To)[1]‘,‘varchar(10)‘) --付款结束时间      ,@OrderNum = R.c.value(‘(OrderNum)[1]‘,‘varchar(10)‘) --数量      ,@OrderNumOpType = R.c.value(‘(./OrderNum/@OpType)[1]‘,‘varchar(20)‘) --数量查询符   from @Where.nodes(‘/Query‘) R(c)           print @Code;          --@Code   if(@Code <> ‘‘)   begin     set @Code = @Code +‘%‘     set @whereStr = @whereStr + ‘ and A.code like @code ‘   end       if(@SourceCode <> ‘‘)   begin      set @SourceCode = @SourceCode +‘%‘      set @whereStr = @whereStr + ‘ and A.SourceCode Like @SourceCode ‘   end       if(@SourcePlate <> ‘‘)    begin      set @whereStr = @whereStr + ‘ and A.SourcePlate = @SourcePlate ‘   end      if(@OrderNum <> ‘‘)   begin     set @whereStr = @whereStr + ‘ And A.OrderNum ‘ + @OrderNumOpType+ ‘ @OrderNum ‘    end      if(@PayTimeForm <> ‘‘)   begin     set @whereStr = @whereStr + ‘ And A.PayTime >= @PayTimeForm ‘   end      print @PayTimeTo   if(@PayTimeTo <> ‘‘)   begin     set @PayTimeTo = CONVERT(varchar(10),CONVERT(datetime,@PayTimeTo) + 1,120)     set @whereStr = @whereStr + ‘ And A.Paytime < @PayTimeTo ‘   end      if CHARINDEX(‘Code‘,@OrderBy) = 1   begin      set @OrderByStr = ‘A.‘ + @OrderBy   end   else if CHARINDEX(‘Paytime‘,@OrderBy) = 1   begin      set @OrderByStr = REPLACE(@OrderBy,‘PayTime‘,‘A.PayTime‘)   end   else    begin     set @OrderByStr = @OrderBy   end      create table #torderList(        RowId int primary key,        Id int not null,        Code nvarchar(50) null,        SourceCode nvarchar(50) null,        SourcePlate int null,        OrderNum int null,        PayTime datetime null   )    --@sql需要和#torderList一一对应  set @sql = ‘    select ROW_NUMBER() over(order by ‘+ @OrderByStr +‘)  ,A.Id  ,A.Code  ,A.SourceCode  ,A.SourcePlate  ,A.OrderNum  ,A.PayTime  from [order] A With(nolock)  where 1 = 1‘  + @whereStr    print @sql;    set @params =‘@Code nvarchar(50),@SourceCode nvarchar(50),@SourcePlate int,@PayTimeForm datetime,@PayTimeTo datetime,@OrderNum int‘    insert into #torderList(      [RowId]      ,Id      ,Code      ,SourceCode      ,SourcePlate      ,OrderNum      ,PayTime       )   exec sp_executesql @sql,   @params,@Code,@SourceCode,@SourcePlate,@PayTimeForm,@PayTimeTo,@OrderNum      set @RecordCount = @@ROWCOUNT;      select * from    #torderList   where [RowId] > (@PageIndex - 1)  * @PageSize       and [RowId] <= @PageIndex * @PageSize   order by [RowId];      Drop Table #torderList;   end