汎用SQLページング・ストレージ・プロシージャ

注意:ストレージ・プロシージャのソートにはプライマリ・キーが必要です.そうしないと、ページングが成功しない可能性があります.
 
ストアド・プロシージャ1:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--    -------------------------------------------------------------
/**//*
@strTable --            
@strField --         ,*      
@intTop --       
@pageSize --         
@pageIndex --         
@strWhere --    ,  where
@strSortKey --       
@strSortField --    ， ：id desc (  id desc,dt asc)
@strOrderBy --  ,0-  ,1-  
@pageCount --           
@RecordCount --        
@UsedTime --       
*/
Create PROCEDURE [dbo].[ThePagerIndex]
@strTable varchar(1000) = '[dbo].[ttable]',
@strField varchar(1000) = '*',
@intTop int = 5000,
@pageSize int = 20,
@pageIndex int = 1,
@strWhere varchar(1000) = '1=1',
@strSortKey varchar(1000) = 'id',
@strSortField varchar(500) = 'id DESC',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(200),@where1 varchar(200),@where2 varchar(200)
    IF @strWhere is null or rtrim(@strWhere)=''
        BEGIN--      
            SET @where1=' WHERE '
            SET @where2=' '
        END
    ELSE
        BEGIN--     
            SET @where1=' WHERE ('+@strWhere+') AND ' --           
            SET @where2=' WHERE ('+@strWhere+') ' --            
        END
    --SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
    IF @intTop<=0
        BEGIN
            SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
        END
    ELSE
        BEGIN
            SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(200)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
        END
    --print @sql

EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --      
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --     
SELECT @RecordCount = @sqlcount --      
    IF @pageIndex=1 --   
        BEGIN
            SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where2+'ORDER BY '+ @strSortField
        END
    Else
        BEGIN
            IF @strOrderBy=0
                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+ ' FROM '+
                    @strTable+@where1+@strSortKey+'>(SELECT MAX('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+
                    CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+@strSortKey+' FROM '+@strTable+@where2+
                    'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
            ELSE
                SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where1+
                    @strSortKey+'<(SELECT MIN('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+
                    @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
        END
print @sql
EXEC(@sql)
print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as   
Return @sqlcount
End

GO

ストアド・プロシージャ2:
 

CREATE PROCEDURE SP_Pagination
/*
***************************************************************
**                                   **
***************************************************************
    :
1.Tables             :   ,  
2.PrimaryKey         :    
3.Sort               :    ，  Order By   ：NewsID Desc,OrderRows Asc
4.CurrentPage        :    
5.PageSize           :    
6.Filter             :    ，  Where
7.Group              :Group  ,  Group By
@PageCount --           
@RecordCount --        
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@PageCount int OUTPUT,
@RecordCount int OUTPUT
)
AS

DECLARE @sql nvarchar(1000), @strWhere nvarchar(1000)
Declare @sqlcount INT
IF @Filter is null or rtrim(@Filter)=''
BEGIN--      
SET @strWhere=' '
END
ELSE
BEGIN--     
SET @strWhere=' WHERE ('+@Filter+') ' --            
END


SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@Fields+' from '+ @Tables + @strWhere +') As tmptab'


EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --      
SELECT @PageCount=CEILING((@sqlcount+0.0)/@pageSize) --     
SELECT @RecordCount = @sqlcount --      

/*    */
IF @Sort IS NULL OR @Sort = ''
 SET @Sort = @PrimaryKey

DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*      .*/
IF CHARINDEX('DESC',@Sort)>0
 BEGIN
  SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
  SET @operator = '<='
 END
ELSE
 BEGIN
  IF CHARINDEX('ASC', @Sort) > 0
   SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
  ELSE
   SET @strSortColumn = @Sort
  SET @operator = '>='
 END


IF CHARINDEX('.', @strSortColumn) > 0
 BEGIN
  SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
  SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
 END
ELSE
 BEGIN
  SET @SortTable = @Tables
  SET @SortName = @strSortColumn
 END

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 @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*     */
IF @CurrentPage < 1
 SET @CurrentPage = 1

/*      .*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))

/*        .*/
IF @Filter IS NOT NULL AND @Filter != ''
 BEGIN
  SET @strFilter = ' WHERE ' + @Filter + ' '
  SET @strSimpleFilter = ' AND ' + @Filter + ' '
 END
ELSE
 BEGIN
  SET @strSimpleFilter = ''
  SET @strFilter = ''
 END
IF @Group IS NOT NULL AND @Group != ''
 SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
 SET @strGroup = ''
 
/*      */
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO

 
使用方法:
 
まずデータモデルを定義します.

public class Question_model
{
    int qid;

    public int Qid
    {
        get { return qid; }
        set { qid = value; }
    }
    string qtitle;

    public string Qtitle
    {
        get { return qtitle; }
        set { qtitle = value; }
    }

}

 
BLL層コード
 

//           1   ，    2  ，    sqlCommand SqlParameter  
public IList<Question_model> GetPage(int pageindex, int _pageSize, out int pageCount, out int RecordCount)
{
    pageCount = 0;
    RecordCount = 0;
    IList<Question_model> list = new List<Question_model>();
    using (SqlConnection conn = new SqlConnection(PubConstant.ConnectionString))
    {
       SqlCommand objcmd = new SqlCommand(".ThePagerIndex", conn);

       objcmd.CommandType = CommandType.StoredProcedure;

       SqlParameter[] para ={

       new SqlParameter("@strTable",SqlDbType.VarChar,-1),

       new SqlParameter("@strField",SqlDbType.VarChar,-1),

       new SqlParameter("@pageSize",SqlDbType.Int),

       new SqlParameter("@pageIndex",SqlDbType.Int),

       new SqlParameter("@strSortKey",SqlDbType.VarChar,-1),

       new SqlParameter("@strSortField",SqlDbType.VarChar,-1),

       new SqlParameter("@strOrderBy",SqlDbType.Bit),

       new SqlParameter("@pageCount",SqlDbType.Int),

       new SqlParameter("@RecordCount",SqlDbType.Int),

       new SqlParameter("@inttop",SqlDbType.Int,-1)

       };

       para[0].Value = "question";

       para[1].Value = "*";

       para[2].Value = _pageSize;

       para[3].Value = pageindex;

       para[4].Value = "qid";

       para[5].Value = "qtime desc";

       para[6].Value = 1;

       para[7].Value = pageCount;

       para[7].Direction = ParameterDirection.Output;

       para[8].Value = RecordCount;

       para[8].Direction = ParameterDirection.Output;

       para[9].Value = -1;

      objcmd.Parameters.AddRange(para);

      conn.Open();

      using (SqlDataReader reader = objcmd.ExecuteReader(CommandBehavior.CloseConnection))
      {

         while (reader.Read())

         {

          Question_model model = new Question_model();

          model.Qid = Convert.ToInt32(reader["qid"]);

          model.Qtitle = Convert.ToString(reader["qtitle"]);

          list.Add(model);
          }

      }
      RecordCount = Convert.ToInt32(objcmd.Parameters["@RecordCount"].Value);
      pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
      conn.Close();

      conn.Dispose();

   }
   return list;
}

 
2番目のストアド・プロシージャを使用する場合は、次のように変更されます.

SqlCommand objcmd = new SqlCommand(".SP_Pagination", conn);

                objcmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] para ={

                   new SqlParameter("@Tables",SqlDbType.VarChar,-1),

                   new SqlParameter("@PrimaryKey",SqlDbType.VarChar,-1),

                   new SqlParameter("@Sort",SqlDbType.VarChar,-1),

                   new SqlParameter("@CurrentPage",SqlDbType.Int),

                   new SqlParameter("@PageSize",SqlDbType.Int),

                   new SqlParameter("@Fields",SqlDbType.VarChar,-1),

                   new SqlParameter("@Filter",SqlDbType.VarChar,-1),

                   new SqlParameter("@Group",SqlDbType.VarChar,-1),

                   new SqlParameter("@PageCount",SqlDbType.Int),

                   new SqlParameter("@RecordCount",SqlDbType.Int)

                   };

                para[0].Value = "DataTable";

                para[1].Value = "dataid";

                para[2].Value = "NodeData desc";

                para[3].Value = pageindex;

                para[4].Value = _pageSize;

                para[5].Value = "*";

                para[6].Value = "";

                para[7].Value = "";

                para[8].Value = pageCount;

                para[8].Direction = ParameterDirection.Output;

                para[9].Value = RecordCount;

                para[9].Direction = ParameterDirection.Output;

 
 
ASPXコード:【aspnetpagerはhttp://www.webdiyer.com/までダウンロードできます】

<div>
      <asp:Repeater ID="Repeater1" runat="server">
         <HeaderTemplate>
               <br />
         </HeaderTemplate>
         <ItemTemplate>
            <span style="width:100">  ：<%#Eval("qid")%>&nbsp;&nbsp;&nbsp;&nbsp;<%#Eval("qtitle")%></span><br />
         </ItemTemplate>
         <FooterTemplate>
          <asp:Label ID="lblFooterTemplate" runat="server" Text="     " Visible="<%#bool.Parse((Repeater1.Items.Count==0).ToString())%>"></asp:Label>
          </FooterTemplate>
      </asp:Repeater>
        <webdiyer:AspNetPager ID="AspNetPager1" runat="server" OnPageChanged="PageChanged" FirstPageText="  " LastPageText="  "
          NextPageText="   " PrevPageText="   " ShowInputBox="Always" Font-Size="13px" ShowPageIndexBox="Never" PageSize="5">
        </webdiyer:AspNetPager>
</div>

 
最後のCSコード:

public partial class Page_Test2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            Bind();
        }
    }

    int currPage = 1;
    int PageSize = 5;

    public void Bind()
    {

        int pageCount;
        int RecordCount;

        Repeater1.DataSource = GetPage(currPage, PageSize, out pageCount, out RecordCount);

        Repeater1.DataBind();

        this.AspNetPager1.RecordCount = RecordCount;

        this.AspNetPager1.CurrentPageIndex = currPage;

        this.AspNetPager1.PageSize = PageSize;

    }
    protected void PageChanged(object sender, EventArgs e)
    {

        int pageCount;
        int RecordCount;

        Repeater1.DataSource = GetPage(this.AspNetPager1.CurrentPageIndex, PageSize, out pageCount, out RecordCount);

        Repeater1.DataBind();

    }
}