三层怎么调用存储过程分页？

Question

我现在用存储过程分页可以实现，当想用三层来调用时，却发现有很多问题！
这是存储过程：
ALTER PROCEDURE page
@orderid nvarchar(50),--id
@tablename nvarchar(50),--表名
@term nvarchar(50)='*',--条件
@currentpage int,--当前页
@sort int,--当sort等于0时，id按desc排序，为1时，id asc排序
@pagesize int,--每页记录数
@rowcount int output,--总记录
@pagecount int output--总页数
AS
declare @sql nvarchar(200)
declare @sqlcount nvarchar(100)
declare @sqlsub nvarchar(200)
declare @totalcount nvarchar(200)
declare @temporderid nvarchar(50)set @sqlcount='select @totalcount=count(*) from '+@tablename
exec sp_executesql @sqlcount,N'@totalcount int out',@rowcount  outputif @term is null or @term=''
set @term='*'if @orderid is null or @orderid=''
set @temporderid=''
else
begin
if @sort=0
set @temporderid='order by '+@orderid+' desc'
else
set @temporderid='order by ' +@orderid+' asc'
endif @rowcount%@pagesize>0--计算总记录数
set @pagecount=(@rowcount/@pagesize)+1
else
set @pagecount=@rowcount/@pagesize
--row_number()的写法
set @sqlsub='SELECT ROW_NUMBER() OVER('+@temporderid+') as rowNUM,* FROM '+@tablename
set @sql= 'SELECT * FROM ('+@sqlsub+')as b where rowNUM BETWEEN' +str((@currentpage-1)*@pagesize+1)+' AND '+str(@currentpage*@pagesize)+' '+@temporderid
exec(@sql)

Answer 1

下面是我调用的过程：
        private static DAL.DET det = new DAL.DET();
        private static SqlConnection conn = det.Getconn();//调用数据库连接符
        protected void Page_Load(object sender, EventArgs e)
        {            int pagesize = 3;//当前记录
            int rowcount;//总记录
            int pagecount;//总页数
            int currentpage;//当前页
            if (Request["CurrentPage"] == null || Convert.ToInt32(Request["CurrentPage"]) < 1)
            {
                currentpage = 1;
            }
            else
            {
                currentpage = Convert.ToInt32(Request["CurrentPage"]);
            }
            SqlCommand comm = new SqlCommand("page", conn);
            comm.CommandType = CommandType.StoredProcedure;
            SqlParameter[] param ={
                                     new SqlParameter("@orderid",SqlDbType.NVarChar,50),
                                     new SqlParameter("@tablename",SqlDbType.NVarChar,50),
                                     new SqlParameter("@currentpage",SqlDbType.Int,50),
                                     new SqlParameter("@term",SqlDbType.NVarChar,50),
                                     new SqlParameter("@pagesize",SqlDbType.Int),
                                     new SqlParameter("@sort",SqlDbType.Int),
                                     new SqlParameter("@rowcount",SqlDbType.Int),
                                     new SqlParameter("@pagecount",SqlDbType.Int)
                                 };
            param[0].Value = "id";
            param[1].Value = "new";
            param[2].Value = currentpage;
            param[3].Value = "*";
            param[4].Value = pagesize;
            param[5].Value = 1;
            param[6].Direction = ParameterDirection.Output;
            param[7].Direction = ParameterDirection.Output;            foreach (SqlParameter par in param)
            {
                comm.Parameters.Add(par);
            }
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            da.SelectCommand = comm;
            conn.Close();
            da.Fill(ds);
           
            rowcount = (int)comm.Parameters["@rowcount"].Value;
            pagecount = (int)comm.Parameters["@pagecount"].Value;            lblCurrent.Text = Convert.ToString(currentpage);
            lblPageCount.Text = Convert.ToString(pagecount);
            lblRowCount.Text = Convert.ToString(rowcount);            //if (currentpage > pagecount)
            //{
            //    Response.Redirect("Default.aspx?CurrentPage=" + Convert.ToString(pagecount));
            //}            hyFirst.NavigateUrl = "Default.aspx?CurrentPage=1";
            hyPrev.NavigateUrl = "Default.aspx?CurrentPage=" + Convert.ToString(currentpage - 1);
            hyNext.NavigateUrl = "Default.aspx?CurrentPage=" + Convert.ToString(currentpage + 1);
            hyLast.NavigateUrl = "Default.aspx?CurrentPage=" + Convert.ToString(pagecount);
            if (Convert.ToInt32(currentpage) == 1)
            {
                hyFirst.Enabled = false;
                hyPrev.Enabled = false;
            }
            if (Convert.ToInt32(currentpage) == pagecount)
            {
                hyNext.Enabled = false;
                hyLast.Enabled = false;
            }            DataList1.DataSource = ds.Tables[0];
            DataList1.DataBind();
        }    }
}

Answer 2

把调用存储过程返回数据集的代码提取到一个数据访问类里，定义为方法，存储过程的参数可以定义为方法的参数或者用个集合对象可以单独建个类库项目，然后再web项目中添加引用，实例化类，调用方法 、传参

Answer 3

分页控件+自定义存储过程。。然后调用就是相当于你在方法中执行你sql语句啊

Answer 4

！我现在建了个三层，BLL,DAL,Model.,Unicty,我现在会用三层架构的存储过程，进行增，改，删，查！至于分页这里觉得不好调用，麻烦大家帮忙。。

Answer 5

你的 存储过程里面 是 有参数的 比如 当前页  总页数  每页记录数等等 这样子的话 可以写个公用的方法 放到 DAL/DBHelper 里面 写个公用的方法就OK了具体的步骤请看：
http://tech.163.com/06/0105/10/26MP7O290009159H_3.html
同时具有返回值、输入参数、输出参数的存储过程 这里刚好也是分页的 你可以套用一下

Answer 6

再贴出我前台的代码！<asp:DataList ID="DataList1" runat="server" Width="474px">
     <ItemTemplate>
       <table>
          <tr>
               <td><%#Eval("id") %></td>
               <td><%#Eval("name") %> </td>
               <td><%#Eval("message") %></td>
               <td><a href="del.aspx?id=<%#Eval("id") %>">删除</a></td>
               <td><a href="update.aspx?id=<%#Eval("id") %>"> 修改</a></td>
          </tr>
       </table>
     </ItemTemplate>
    </asp:DataList>
      当前<asp:Label ID="lblCurrent" runat="server"></asp:Label>页
      总<asp:Label ID="lblPageCount" runat="server" ></asp:Label>页数/
       共<asp:Label ID="lblRowCount" runat="server"></asp:Label>记录
        <asp:HyperLink ID="hyFirst" runat="server">首页</asp:HyperLink>
         <asp:HyperLink ID="hyPrev" runat="server">上一页</asp:HyperLink>
           <asp:HyperLink ID="hyNext" runat="server">下一页</asp:HyperLink>
             <asp:HyperLink ID="hyLast" runat="server">末页</asp:HyperLink>
    

Answer 7

页数
(1-1)*5 0 当前页索引*每页的行数
(2-1)*5 5
(3-1)*5 10create procedure proc_Page
@pageSize int,
@currentPageIndex int,
@order varchar(20)
as
declare
@str varchar(100)
begin
 set @str = 'select top ' + convert(varchar,@pageSize) + ' * from goods where id not in (select top '
 set @str = @str + convert(varchar,@currentPageIndex *@pageSize) + ' id from goods order by '
 set @str = @str + @order + ') order by ' + @order
 execute (@str)
end
这是存储过程 
//下面是DAL里面的调用
 public static List<Goods> GetGoodsByPage(int pageSize, int currentPageIndex, string order)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@currentPageIndex",currentPageIndex),
                new SqlParameter("@order",order)};            DataSet ds = DbHelp.GetDataSet("proc_Page", parms);
            List<Goods> goods = new List<Goods>();            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Goods gs = new Goods();
                gs.Id = Convert.ToInt32(dr["id"]);
                gs.Img = dr["img"].ToString();
                gs.Impersonator = dr["impersonator"].ToString();
                gs.Name = dr["name"].ToString();
                gs.Point = Convert.ToInt32(dr["point"]);
                gs.Synopsis = dr["synopsis"].ToString();
                gs.AddTime = Convert.ToDateTime(dr["addtime"]);
                gs.ClickNum = Convert.ToInt32(dr["clicknum"]);
                gs.Director = dr["director"].ToString();
                gs.GoodsCategory = CategoryService.GetCategoryById(dr["categoryid"].ToString());                goods.Add(gs);
            }            return goods;
        }

Answer 8

这里是我用的DBHelper：using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
/// <summary>
/// DBHelper 的摘要说明
/// </summary>
namespace OADal
{
    public class DBHelper
    {
        private SqlConnection conn;
        public SqlConnection Conn
        {
            get
            {
                if (conn == null)
                    conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OA"].ToString());
                return conn;
            }
        }        /// <summary>
        /// 打开连接池
        /// </summary>
        public void OpenConn()
        {
            if (Conn.State == ConnectionState.Closed)
                Conn.Open();
        }        /// <summary>
        /// 关闭连接池
        /// </summary>
        public void CloseConn()
        {
            if (Conn.State == ConnectionState.Open)
                Conn.Close();
        }        public bool ExecuteNonQuery(SqlConnection newconn, SqlTransaction tran, string sql, SqlParameter[] paras, CommandType type)
        {
            try
            {
                //创建命令对象
                SqlCommand cmd = new SqlCommand(sql, newconn);
                //设置当前所在事务
                cmd.Transaction = tran;
                cmd.CommandType = type;
                //添加参数集合
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                int result = cmd.ExecuteNonQuery();
                if (result > 0)
                    return true;
                else
                    return false;
            }
            catch { }
            return false;
        }        /// <summary>
        /// 返回 DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public  DataTable GetDataSets(string sql, SqlParameter[] values, CommandType type)
        {
            DataSet ds = new DataSet();
            SqlCommand com = new SqlCommand(sql, Conn);
            com.CommandType = type;
            if (values != null && values.Length > 0)
            {
                com.Parameters.AddRange(values);
            }
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            adapter.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 执行增、删、改操作的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string sql, SqlParameter[] paras, CommandType type)
        {
            try
            {
                //打开连接池
                OpenConn();
                //创建命令对象
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //添加参数集合
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                int result = cmd.ExecuteNonQuery();
                CloseConn();
                if (result > 0)
                    return true;
                else
                    return false;
            }
            catch { }
            return false;
        }
        /// <summary>
        /// 返回单行单列的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ExecuteScaler(string sql, SqlParameter[] paras, CommandType type)
        {
            try
            {
                OpenConn();
                //创建命令对象
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //添加参数集合
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                object obj = cmd.ExecuteScalar();
                CloseConn();
                if (obj != null)
                    return obj.ToString();
                return "";
            }
            catch
            { }
            return "";        }
        /// <summary>
        /// 返回SqlDateReader的方法(在调用该方法后，必须关闭sqldatareader和连接池)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteDataReader(string sql, SqlParameter[] paras, CommandType type)
        {
            try
            {
                OpenConn();
                //创建命令对象
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //添加参数集合
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                return cmd.ExecuteReader();
            }
            catch { }
            return null;
        }
        /// <summary>
        /// 返回DataSet的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, SqlParameter[] paras, CommandType type)
        {
            try
            {
                //创建命令对象
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //添加参数集合
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch { }
            return null;
        }
    }
}

Answer 9

那么在bll和.cs页面是怎么的调用的啊！也贴出来看看