DataList数据分页
DAL层:
DBSqlHelp.cs
namespace DAL
{
public class SqlHelp
{
protected static SqlConnection Sqlconn()
{
string connstring = ConfigurationManager.ConnectionStrings["sqlconn"].ToString();
SqlConnection conn = new SqlConnection(connstring);
return conn;
}
public static DataSet Query(string sqlstr,params SqlParameter[] parments)
{
using (SqlConnection conn=Sqlconn())
{
conn.Open();
SqlCommand comm = new SqlCommand(sqlstr,conn);
comm.Parameters.AddRange(parments);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public static object GetSingle(string strsql, params SqlParameter[] Parmeters)
{
using (SqlConnection conn = Sqlconn())
{
conn.Open();
SqlCommand cmd = new SqlCommand(strsql, conn);
if (Parmeters == null)
{
cmd.Parameters.AddRange(Parmeters);
}
object obj = cmd.ExecuteScalar();
return obj;
}
}
}
}
IndexService.cs
namespace DAL
{
public class Indexservice
{
public DataSet Query()
{
string sqlstr = "select top 2 CategoryTitle from NewsCategory";
return SqlHelp.Query(sqlstr);
}
public DataSet GetPageByNumber(int pagenumber)
{
//分页SQL语句
int pagesize=3;
int start=(pagenumber-1)*pagesize+1;
int end=(pagenumber*pagesize);
string sqlstring = "select * from (select *,Row_Number() over(order by id) RowNumber from Publisher ) t where t.RowNumber>=@start and t.RowNumber<=@end";
SqlParameter[] parments = { new SqlParameter("@start", start), new SqlParameter("@end", end) };
return SqlHelp.Query(sqlstring, parments);
}
public int GetRowCount()
{
string sqlstr = "select count(*) from Publisher";
return (int)SqlHelp.GetSingle(sqlstr);
}
}
}
BLL层:
IndexManager.cs
namespace BLL
{
public class IndexManager
{
Indexservice indexservice = new Indexservice();
public DataSet Query()
{
indexservice = new Indexservice();
return indexservice.Query();
}
public DataSet GetPageByPageNumber(int pagenumber)
{
return indexservice.GetPageByNumber(pagenumber);
}
//得到总页数
public int GetPageCount()
{
int pagesize=3;
int rows = indexservice.GetRowCount();
int pagecount =(int)Math.Ceiling(rows / (double)pagesize);
return pagecount;
}
}
}
UI层:
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using BLL;
public partial class _Default : System.Web.UI.Page
{
IndexManager indexmanager = new IndexManager();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind(1);
}
}
protected void Bind(int pagenumber)
{
//得到总页数
pagecountlab.Text = indexmanager.GetPageCount().ToString();
DataSet ds= indexmanager.GetPageByPageNumber(pagenumber);
DataList1.DataSource = ds;
DataList1.DataBind();
}
protected void prepage_Click(object sender, EventArgs e)
{
int pageindex = Convert.ToInt32(currentpage.Text);
prepage.Enabled = true;
nextpage.Enabled=true;
if (pageindex == 2)
{
pageindex = 1;
prepage.Enabled = false;
}
else
{
pageindex--;
}
Bind(pageindex);
currentpage.Text = pageindex.ToString();
}
protected void nextpage_Click(object sender, EventArgs e)
{
nextpage.Enabled = true;
prepage.Enabled = true;
int pageindex = Convert.ToInt32(currentpage.Text);
if (pageindex == int.Parse(pagecountlab.Text)-1)
{
pageindex = int.Parse(pagecountlab.Text);
nextpage.Enabled = false;
}
else
{
pageindex++;
}
Bind(pageindex);
currentpage.Text = pageindex.ToString();
}
}
Model层不写了