asp.net存储过程实现分页.

最新推荐文章于 2024-09-14 19:00:09 发布

chenjianyong94

最新推荐文章于 2024-09-14 19:00:09 发布

阅读量299

点赞数

文章标签： asp.net 存储 dataset string sql output

本文链接：https://blog.csdn.net/chenjianyong94/article/details/3989452

版权

1.写在sql_server 中

CREATE PROC sp_PageCount
@tbname     sysname,               --要分页显示的表名
@Where    nvarchar(1000)='',     --查询条件
@Count int OUTPUT             --总页数
as
DECLARE @sql nvarchar(4000)
SET @sql=N'SELECT @Count=COUNT(*)'
    +N' FROM '+@tbname
    +N' where '+@Where
EXEC sp_executesql @sql,N'@Count int OUTPUT',@Count OUTPUT
GO

CREATE PROC sp_PageView
@tbname     sysname,               --要分页显示的表名
@PageCurrent int=1,               --要显示的页码
@PageSize   int=10,                --每页的大小(记录数)
@FieldShow nvarchar(1000)='',      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='',      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@Where    nvarchar(1000)=''     --查询条件
AS
declare @topN int
set @topN= (@PageCurrent-1)*@PageSize
exec (N'select top '+@PageSize+N''+@FieldShow+N' from '+@tbname+N' where ID not in(select top '+@topN+N' ID from '+@tbname+N' where '+ @Where+N' '+@FieldOrder+ N')'+N' and '+ @Where+N' '+@FieldOrder)
GO

2.写在页面上

public SqlConnection GetConn()
        {
            string ConnString=ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;//web.config取值
            Conn=new SqlConnection(ConnString);
            return Conn;
        }

public DataSet PageView(string tmpTab, int tmpPagenumber, int tmpPagesize, string tmpField, string tmpOrder, string tmpWhere)
        {
            SqlConnection myConnection = GetConn();
            SqlDataAdapter mySda = new SqlDataAdapter();
            SqlCommand myCommand = new SqlCommand("sp_PageView", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.Add("@tbname", SqlDbType.VarChar, 50).Value = tmpTab;
            myCommand.Parameters.Add("@PageCurrent", SqlDbType.Int).Value = tmpPagenumber;
            myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = tmpPagesize;
            myCommand.Parameters.Add("@FieldShow", SqlDbType.VarChar, 1000).Value = tmpField;
            myCommand.Parameters.Add("@FieldOrder", SqlDbType.VarChar, 1000).Value = tmpOrder;
            myCommand.Parameters.Add("@Where", SqlDbType.VarChar, 1000).Value = tmpWhere;

            DataSet myDs = new DataSet();
            myConnection.Open();
            mySda.SelectCommand = myCommand;
            mySda.Fill(myDs, "tmp");
            myConnection.Close();
            return myDs;
        }

        public int PageCount(string tmpTab, string tmpWhere)
        {
            SqlConnection myConnection = GetConn();
            SqlCommand myCommand = new SqlCommand("sp_PageCount", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.Add("@tbname", SqlDbType.VarChar, 50).Value = tmpTab;
            myCommand.Parameters.Add("@Where", SqlDbType.VarChar, 1000).Value = tmpWhere;
            myCommand.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output;

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            int returnID = (int)myCommand.Parameters["@Count"].Value;
            myCommand.Parameters.Clear();
            myConnection.Close();

            return returnID;
        }

//这些是写好的方法.

3.调用方法（写在load()事件里）

        string where = "";
        AspNetPager1.RecordCount = PageCount("Subject", where);
        DataTable dt = PageView("Subject", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, "*",   "Order by SubjectCode", where).Tables[0];
        Repeater1.DataSource = dt.DefaultView;
        Repeater1.DataBind();

        AspNetPager1.CustomInfoHTML = "记录总数：" + AspNetPager1.RecordCount;
        AspNetPager1.CustomInfoHTML += " 总页数：" + AspNetPager1.PageCount;
        AspNetPager1.CustomInfoHTML += " 当前页：" + AspNetPager1.CurrentPageIndex;