• sqlserver 存储过程学习笔记(二) 在项目中的应用<多表分页>


    (1)存储过程建立

    USE [NewPlat] GO

    /****** Object:  StoredProcedure [dbo].[usp_PagingLarge]    Script Date: 07/11/2013 08:27:44 ******/ SET ANSI_NULLS ON GO

    SET QUOTED_IDENTIFIER ON GO

    Create PROCEDURE [dbo].[usp_PagingLarge] 

    @TableNames VARCHAR(200),     --表名,可以是多个表,但不能用别名

    @PrimaryKey VARCHAR(100),     --主键,可以为空,但@Order为空时该值不能为空 

    @Fields     VARCHAR(4000),         --要取出的字段,可以是多个表的字段,可以为空,为空表示select * 

    @PageSize INT,             --每页记录数 

    @CurrentPage INT,         --当前页,0表示第1页 

    @Filter VARCHAR(4000) = '',     --条件,可以为空,不用填 where 

    @Group VARCHAR(200) = '',     --分组依据,可以为空,不用填 group by 

    @Order VARCHAR(200) = '',    --排序,可以为空,为空默认按主键升序排列,不用填 order by 

    @RecordCount int OUTPUT             --总记录数,自己增加(总记录数)

     AS 

    BEGIN      

    DECLARE @SortColumn VARCHAR(200)    

    DECLARE @Operator CHAR(2)      

    DECLARE @SortTable VARCHAR(200)      

    DECLARE @SortName VARCHAR(200)      

    IF @Fields = ''          

    SET @Fields = '*'      

    IF @Filter = ''          

    SET @Filter = 'Where 1=1'      

    ELSE          

    SET @Filter = 'Where ' +   @Filter      

    IF @Group <>''          

    SET @Group = 'GROUP BY ' + @Group        

    IF @Order <> ''      

    BEGIN          

    DECLARE @pos1 INT, @pos2 INT          

    SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')          

    IF CHARINDEX(' DESC', @Order) > 0       

    IF CHARINDEX(' ASC', @Order) > 0              

    BEGIN                  

    IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)                      

    SET @Operator = '<='                  

    ELSE                      

    SET @Operator = '>='              

    END             

      ELSE                 

      SET @Operator = '<='          

    ELSE              

    SET @Operator = '>='          

    SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')          

    SET @pos1 = CHARINDEX(',', @SortColumn)        

      IF @pos1 > 0              

    SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)          

    SET @pos2 = CHARINDEX('.', @SortColumn)          

    IF @pos2 > 0          

    BEGIN              

    SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)              

    IF @pos1 > 0                   

    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)              

    ELSE                  

    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)          

    END          

    ELSE          

    BEGIN              

    SET @SortTable = @TableNames              

    SET @SortName = @SortColumn          

    END      

    END      

    ELSE      

    BEGIN          

    SET @SortColumn = @PrimaryKey          

    SET @SortTable = @TableNames          

    SET @SortName = @SortColumn          

    SET @Order = @SortColumn          

    SET @Operator = '>='      

    END        

    DECLARE @type varchar(50)      

    DECLARE @prec int      

    Select @type=t.name, @prec=c.prec       FROM sysobjects o        JOIN syscolumns c on o.id=c.id       JOIN systypes t on c.xusertype=t.xusertype       Where o.name = @SortTable AND c.name = @SortName     

      IF CHARINDEX('char', @type) > 0      

    SET @type = @type + '(' + CAST(@prec AS varchar) + ')'       

      DECLARE @TopRows INT      

    SET @TopRows = @PageSize * @CurrentPage + 1      

    print @TopRows 

    print @Operator      

    EXEC('           DECLARE @SortColumnBegin ' + @type + '           SET ROWCOUNT ' + @TopRows + '           Select @SortColumnBegin=' + @SortColumn + ' FROM   ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' orDER BY ' + @Order + '           SET ROWCOUNT ' + @PageSize + '           Select ' + @Fields + ' FROM   ' + @TableNames + ' ' + @Filter   + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' orDER BY ' + @Order + '           ')           

    IF @RecordCount IS NULL 

    BEGIN      DECLARE @sql nvarchar(4000)     

    SET @sql=N'SELECT @RecordCount=COUNT(*)'          +N' FROM '+@TableNames          +N' '+@Filter     

    EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT 

    END    

    END 

    GO

    (2)实现多表分页的函数(c#代码)

     public static DataTable ExecMultiPageList(string tableName, string iDName, string Fields, int pageSize, int currentPage, string Filter, string Group, string Order, List<SqlParameter> list, out int rowCount)
            {
                rowCount = 0;
                SqlConnection connection = new SqlConnection(connectionString);
                SqlParameter[] parameters = {  
                        new SqlParameter("@TableNames",SqlDbType.VarChar,200), 
                        new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100), 
                        new SqlParameter("@Fields",SqlDbType.VarChar,200), 
                        new SqlParameter("@PageSize",SqlDbType.Int,4), 
                        new SqlParameter("@CurrentPage",SqlDbType.Int,4), 
                        new SqlParameter("@Filter",SqlDbType.VarChar,200), 
                        new SqlParameter("@Group",SqlDbType.VarChar,200), 
                        new SqlParameter("@Order",SqlDbType.VarChar,200), 
                        new SqlParameter("@RecordCount",SqlDbType.Int,4) 
                    };//参数列表 
                parameters[0].Value = tableName;
                parameters[1].Value = iDName;
                parameters[2].Value = Fields;
                parameters[3].Value = pageSize;
                parameters[4].Value = currentPage;
                parameters[5].Value = Filter;
                parameters[6].Value = Group;
                parameters[7].Value = Order;//参数对应值 
                parameters[8].Value = rowCount;
                parameters[8].Direction = ParameterDirection.Output;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = "usp_PagingLarge";//存储过程名 
                cmd.CommandType = CommandType.StoredProcedure;//类型 
                cmd.Parameters.AddRange(parameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                connection.Close();
                rowCount = Convert.ToInt32(parameters[8].Value);//输出 
                return dt;
            }

    (3)调用多表分页的方法

            public string GetFenye(out int recordcount, NameValueCollection form,string loginid,int flag)         {     

            QueryModel queryMdodel = QueryModel.getQueryModel(form);

             string tablename = "Email_MailSender left join Email_MailInfo on Email_MailSender.mailid=Email_MailInfo.Mailid ";       

            string iDName = "Email_MailSender.mailsenderid";            

            string Fields = "Email_MailSender.*,Email_MailInfo.mailtopic,Email_MailInfo.sendtime";            

            int PageSize = queryMdodel.rows;                                         

            int PageIndex = queryMdodel.page == 0 ? 0 : queryMdodel.page - 1;            

            string Filter = "Email_MailSender.senderid = '" + loginid + "' and Email_MailSender.sendstatus ="+flag;            

            string group = "";            

            string order = "Email_MailSender.mailsenderid asc";            

           DataTable dt = DbHelperSQL.ExecMultiPageList(tablename, iDName, Fields, PageSize, PageIndex, Filter, group, order,queryMdodel.listPar ,out recordcount);            

           string strjson = Newtonsoft.Json.JsonConvert.SerializeObject(dt);             return strjson;   

          }

  • 相关阅读:
    SDN第三次作业
    SDN第二次上机作业
    SDN第二次作业
    第七次作业之总结篇
    第八次_计算器重构
    第六次作业之计算器图形界面化
    C++课程 second work _1025
    第五次作业--计算器项目之学习文件读取方式
    C++课程 first work
    第四次作业-计算功能的实现
  • 原文地址:https://www.cnblogs.com/wjcnet/p/3367297.html
Copyright © 2020-2023  润新知