C#利用存储过程进行高速分页[包括前台和后台代码]

存储过程如下：

CREATE Procedure PageData
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页(必须是唯一性的字符，比如标识种子)
@colorder varchar(50), --需要排序的字段(为空，则默认为col)
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800)--查询条件
--@pages int OUTPUT --总页数
--@sqlout nvarchar(4000),---返回sql语句

AS
SET NOCOUNT ON
Declare @intResult Int
Begin Tran
declare @pages int
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @colorder is null or rtrim(@colorder)=''
set @colorder = @col
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' Where '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' Where ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' Where ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='Select @intResult=COUNT(*) FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
Select @pages=CEILING((@intResult+0.0)/@pagesize)--计算总页数
IF @orderby=0
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@tb+@where1+@col+'>(Select MAX('+@col+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) orDER BY '+@colorder
ELSE
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+@where1+@col+'<(Select MIN('+@col+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) orDER BY '+@colorder+' DESC'
IF @page=1--第一页
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@colorder+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--set @sqlout = @sql
EXEC(@sql)
--print 'Sql语句输出为: ' + @sqlout
-------------------------------------------------------------------------------------------------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
select -1
End
Else
Begin
Commit Tran
select @intResult
End

GO

后台代码：

totalcount = sql_function.PageDS.recordcounts("select * from lockip");

        //当前页
        //计算总页数;totalpage
        if (totalcount / PageSize == 0)
        {
            totalpage = totalcount / PageSize;
        }
        else
        {
            totalpage = totalcount / PageSize + 1;
        }
        if (Request.QueryString["page"] == null)///指地址栏中没有PAGE字符
        {
            currentpage = 1;
        }

        if (Request.QueryString["page"] != null)
        {
            // 判断是否为数值
            if (sql_function.publicfunction.isNumber(Request.QueryString["page"]))
            {
                currentpage = 1;
            }
            else///是数值
            {
                if (Request.QueryString["page"].Length > 10 || Request.QueryString["page"].Length < 1)///防止Convert.ToInt32抛出异常或者防止地址栏所得到的page=这样的值
                {
                    currentpage = 1;
                }
                else
                {
                    if (Convert.ToInt32(Request.QueryString["page"]) > totalpage)///是否大于总页数
                    {
                        currentpage = totalpage;
                    }
                    else
                    {
                        if (Convert.ToInt32(Request.QueryString["page"]) <= 1)///是否小于页数1
                        {
                            currentpage = 1;
                        }
                        else
                        {
                            currentpage = Convert.ToInt32(Request.QueryString["page"]);
                        }
                    }
                }
            }
        }
        else
        {
            currentpage = 1;
        }

 

        IpList.DataSource = sql_function.SQLDB.getvalues_Datatable("PageData 'LockIP','LockIP_ID','LockIP_ID',1,'*'," + PageSize + "," + currentpage + ",''");//填充数据

        IpList.DataBind();

        pagelist.Text = sql_function.PageDS.pagination(totalcount, PageSize, currentpage, "?ModuleID=" + Request["ModuleID"] + "", 0, currentpage, PageSize).ToString();//分页

命名空间：

namespace sql_function
{
    public class publicfunction
    {
        /// <summary>
        /// 类querystring判断是否为数字   TRUE代表不是数字，False代表是数字
        /// </summary>
        /// <param name="s">字符串</param>
        /// <returns></returns>
        public static bool isNumber(string s)
        {
            int Flag = 0;
            char[] str = s.ToCharArray();
            for (int i = 0; i < str.Length; i++)
            {
                if (Char.IsNumber(str[i]))
                {
                    Flag++;
                }
                else
                {
                    Flag = -1;
                    break;
                }
            }
            if (Flag > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

    }

   //pageDS用来处理分页
    public class PageDS
    {

        public static int recordcounts(string sqls) ///计算总共有多少条记录
        {
            string strsqls = sqls;
            int allcount = 0;

            SqlConnection conn = sql_function.SQLDB.opendatabase();
            SqlCommand da = new SqlCommand(sqls, conn);
            SqlDataReader cms = da.ExecuteReader();
            while (cms.Read())
            {
                allcount += 1;
            }
            return allcount;

        }

        /// <summary>
        /// 绑定数据，并且实现分页功能
        /// </summary>
        /// <param name="sqls"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static DataView datas(string sqls, int currentpage, int pagesize, string table)
        {
            int startcount;
            if (currentpage < 1)
            {
                startcount = currentpage * pagesize;
            }
            else
            {
                startcount = (currentpage - 1) * pagesize;
            }

            SqlConnection conn = sql_function.SQLDB.opendatabase();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqls, conn);
            da.Fill(ds, startcount, pagesize, table.ToString());
            return ds.Tables[0].DefaultView;
        }

        /// </summary>
        /// 对文本内容实现分页
        /// <param name="total">总记录数</param>
        /// <param name="per">每页记录数</param>
        /// <param name="page">当前页数</param>
        /// <param name="query_string">Url参数</param>
        /// <param name="ID">参数</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="PageSize">每页分页大小</param>
        public static string pagination(int total, int per, int page, string query_string, int ID, int currentpage, int PageSize)
        {
            int allpage = 0;
            int next = 0;
            int pre = 0;
            int startcount = 0;
            int endcount = 0;
            string pagestr = "";
            if (page < 1) { page = 1; }
            //计算总页数
            if (per != 0)
            {
                allpage = (total / per);
                allpage = ((total % per) != 0 ? allpage + 1 : allpage);
                allpage = (allpage == 0 ? 1 : allpage);
            }
            next = page + 1;
            pre = page - 1;
            startcount = (page + 5) > allpage ? allpage - 9 : page - 4;//中间页起始序号
            //中间页终止序号
            endcount = page < 5 ? 10 : page + 5;
            if (startcount < 1) { startcount = 1; } //为了避免输出的时候产生负数，设置如果小于1就从序号1开始
            if (allpage < endcount) { endcount = allpage; }//页码+5的可能性就会产生最终输出序号大于总页码，那么就要将其控制在页码数之内
            pagestr = "共<font color=/"green/">" + allpage + "</font>页&nbsp;共有<font color=/"green/">" + total + "</font>条记录&nbsp;当前页<font color=/"green/">" + currentpage + "</font>/" + allpage + "&nbsp;每页<font color=/"green/">" + PageSize + "</font>条&nbsp;";

            pagestr += page > 1 ? "<a href=/"" + query_string + "&page=1/">首&nbsp页</a>&nbsp;<a href=/"" + query_string + "&page=" + pre + "/">上一页</a>" : "首&nbsp页 上一页";
            //中间页处理，这个增加时间复杂度，减小空间复杂度
            for (int i = startcount; i <= endcount; i++)
            {
                pagestr += page == i ? "&nbsp;<font color=/"green/">" + i + "</font>" : "&nbsp;<a href=/"" + query_string + "&page=" + i + "/">" + i + "</a>";
            }
            pagestr += page != allpage ? "&nbsp;<a href=/"" + query_string + "&page=" + next + "/">下一页</a>&nbsp;<a href=/"" + query_string + "&page=" + allpage + "/">尾&nbsp页</a>" : " 下一页 尾&nbsp页";

            return pagestr;
        }
    }

public class SQLDB
    {

    public static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        /// #region 数据库连接对象操作
        /// <summary>
        /// 打开数据库连接 ///针对ACCESS数据库
        /// </summary>
        public static SqlConnection opendatabase()
        {
           
            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

          /// <summary>
        /// 返回指定Sql语句的返回DataTable
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public static DataTable getvalues_Datatable(string strSQL)
        {
            try
            {
                SqlConnection conn = opendatabase();
                DataTable table = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
                da.Fill(table);
                closedatabase(conn);
                return table;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

    /// <summary>
        /// 关闭数据库连接释放资源
        /// </summary>
        /// 数据库连接对象

        public static void closedatabase(SqlConnection conn)
        {
            try
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

   }

}

前台代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ipmanager.aspx.cs" Inherits="webmanager_ip_ipmanager" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>IP管理</title>
    <LINK href="../css/index.css" type=text/css rel=stylesheet>
</head>
<body>
    <form id="selform" runat="server" name="selform">
    <div>

    <table class="tableBorder" cellspacing="1" cellpadding="2" width="100%" align=center border=0>
      <tbody>
         <tr>
          <th colspan=3> 日志管理</th>
        </tr>
        <tr>
          <th style="height: 25px"><div align="center">日志标题</div></th>
          <th style="height: 25px"><div align="center">日志内容</div></th>
          <th style="height: 25px"><div align="center">添加时间</div></th>
        </tr>
      
   <asp:Repeater ID="IpList" runat="server">
   <HeaderTemplate></HeaderTemplate>
   <ItemTemplate>
        <tr align=right style="cursor:hand">
          <td class=forumRowHighlight style="height: 22px"><div align="center"><%#Eval("LockIP_ID")%> <%#Eval("LockIP_startip")%></div></td>
          <td class=forumRowHighlight style="height: 22px"><div align="center"><%#Eval("LockIP_endip")%></div></td>
          <td class=forumRowHighlight style="height: 22px"><div align="center"><%#Eval("typeip") %></div></td>
        </tr>
        </ItemTemplate>
        <FooterTemplate></FooterTemplate>
   </asp:Repeater>
    <tr>
            <td class="forumRowHighlight" colspan=3 style="height: 22px"><div align="right" style="padding-right:5px;"><asp:Label ID="pagelist" runat="server"></asp:Label></div></td>
    </tr>
      <tr>
        <td class=forumrow colspan=3 height=22>
          </td>
      </tr>
    </table>
    </div>
    </form>
</body>
</html>