初学asp.net MVC3所写分页代码,数据库为mysql,采用Razor视图引擎
数据表staff结构如下:
+----------+-------------+------+-----+---------+----------------
| Field | Type | Null | Key | Default | Extra
+----------+-------------+------+-----+---------+----------------
| id | int(11) | NO | PRI | NULL | auto_increment
| sname | varchar(32) | NO | | |
| spwd | varchar(32) | NO | | |
| age | int(11) | NO | | 0 |
| birthday | date | NO | | NULL |
| salay | double | NO | | NULL |
+----------+-------------+------+-----+---------+----------------
1. DAL(数据访问层)
public class MysqlDAL
{
//从配置文件中获取数据库连接字符串
private static string conString = ConfigurationManager.ConnectionStrings["mysqlCon"].ConnectionString;
/// <summary>
/// 执行dml
/// </summary>
/// <param name="sql">执行dml的sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public static int ExecuteToNonQuery(string sql,params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(conString))
{
conn.Open();
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行dql,返回一行一列的记录
/// </summary>
/// <param name="sql">执行dql的sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public static object ExecuteToScalar(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(conString))
{
conn.Open();
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 执行dql语句,返回datatable对象
/// </summary>
/// <param name="sql">执行dql的sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public static DataTable ExecuteToDataTable(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(conString))
{
conn.Open();
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
DataSet dataset=new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
}
2. BI(service层)
public class StaffService
{
/// <summary>
/// 带分页的职员信息查询
/// </summary>
/// <param name="pageInfo">分页相关信息</param>
/// <returns>职员信息datatable</returns>
public DataTable StaffListByPage(PageInfo pageInfo)
{
string sql = "select id,sname,age,birthday,salay from staff limit " + (pageInfo.PageNow - 1) * pageInfo.PageSize + " ," +pageInfo.PageSize;
try
{
return MysqlDAL.ExecuteToDataTable(sql);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 获取总的记录数
/// </summary>
/// <returns>总记录数</returns>
public int GetTotalCount()
{
string sql = "select count(*) from staff";
try
{
return Convert.ToInt32(MysqlDAL.ExecuteToScalar(sql));
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 初始化PageInfo
/// </summary>
/// <param name="pageInfo"></param>
/// <param name="pageNow">当前页数</param>
/// <returns></returns>
public PageInfo InitPageInfo(PageInfo pageInfo, string pageNow)
{
pageInfo.PageNow = Convert.ToInt32(pageNow);
pageInfo.RowCount = this.GetTotalCount();
pageInfo.PageSize = 15;
pageInfo.PageCount = ((pageInfo.RowCount - 1) / pageInfo.PageSize + 1);
if (pageInfo.PageNow >= pageInfo.PageCount)
{
pageInfo.PageNow = pageInfo.PageCount;
}
if (pageInfo.PageNow <= 1)
{
pageInfo.PageNow = 1;
}
pageInfo.DtInfo = this.StaffListByPage(pageInfo);
return pageInfo;
}
}
3. Controller层
public class StaffListController : Controller
{
/// <summary>
/// 显示所有职员分页
/// </summary>
/// <param name="pageNow">当前页数</param>
/// <returns>返回StaffListByPage视图</returns>
public ActionResult StaffListBypage(string pageNow)
{
if (string.IsNullOrEmpty(pageNow))
{
pageNow = 1+"";
}
StaffService staffService=new StaffService();
StaffListByPageModel staffListByPageModel=new StaffListByPageModel();
PageInfo pageInfo=new PageInfo();
staffListByPageModel.PageInfors = staffService.InitPageInfo(pageInfo,pageNow);
return View(staffListByPageModel);
}
}
4. Model层
a. 分页datatable数据model
/// <summary>
/// 分页显示职员Models
/// </summary>
public class StaffListByPageModel
{
private PageInfo pageInfors;
public PageInfo PageInfors { set; get; }
}
b. 分装分页信息model
/// <summary>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageCount">总页数</param>
/// <param name="pageNow">当前页数</param>
/// <param name="rowCount">总记录数</param>
/// <param name="dtInfo">记录datatable集合</param>
/// </summary>
public class PageInfo
{
private int pageSize;
private int pageCount;
private int pageNow;
private int rowCount;
private DataTable dtInfo;
public int PageSize { set; get; }
public int PageCount { set; get; }
public int PageNow { set; get; }
public int RowCount { set; get; }
public DataTable DtInfo { set; get; }
}
5. View(视图层)
@using System.Data
@model MvcPractice.Models.StaffListByPageMode
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<title>StaffListByPage</title>
<style type="text/css">
a{
text-decoration: none;
}
a:hover {
text-decoration: underline;
color: red;
}
span {
color: red;
}
</style>
</head>
<body>
<div>
<h1>职员信息列表(分页)</h1>
<p><a href="http://localhost:25882/">返回主页面</a></p>
<table style="border: 1px solid green;width:500px;">
@{
<tr style="border: 1px solid green"><th>姓名</th><th>年龄</th><th>生日</th><th>薪水</th><th>操作</th></tr>
for (int i = 0; i < @Model.PageInfors.DtInfo.Rows.Count; i++)
{
DataRow dr = @Model.PageInfors.DtInfo.Rows[@i];
<tr style="border: 1px solid green"><td style="border: 1px solid green">@dr["sname"]</td>
<td style="border: 1px solid green">@dr["age"]</td>
<td style="border: 1px solid green">@dr["birthday"]</td>
<td style="border: 1px solid green">@dr["salay"]</td>
<td style="border: 1px solid green"><a href="javascript:void(0)" οnclick="check(@dr["id"],@Model.PageInfors.PageNow)">删除</a>
<a href="javascript:void(0)" οnclick="gotoupdate(@dr["id"],@Model.PageInfors.PageNow)">编辑</a></td></tr>
}
}
</table>
<div style="width:500px;text-align: right;margin-top: 10px;">
@Html.ActionLink("首页", "StaffListBypage")
@Html.ActionLink("上一页", "StaffListBypage", new { pageNow = (@Model.PageInfors.PageNow - 1) })
@Html.ActionLink("下一页", "StaffListBypage", new { pageNow = (@Model.PageInfors.PageNow + 1) })
@Html.ActionLink("末页", "StaffListBypage", new { pageNow = @Model.PageInfors.PageCount })
当前第 <span>@Model.PageInfors.PageNow</span> 页,共 <span>@Model.PageInfors.PageCount</span> 页
</div>
</div>
<script type="text/javascript">
function check(deleteid,pageNow) {
if (window.confirm("确定要删除吗?")) {
window.location.href = "/StaffList/DeleteStaff?deleteid="+deleteid+"&pageNow="+pageNow;
}
}
function gotoupdate(updateid,pageNow) {
window.location.href='/StaffList/GetStaff?updateid=' + updateid + '&pageNow=' + pageNow;
}
</script>
</body>
</html>