- 数据库数据-User表：
- 分页查询存储过程：
create proc [dbo].[selectuser]
@pagesize int,
@pageindex int
as
select top (@pagesize) * from (select row_number() over(order by ID asc) as rownumber,* from [User]) temp_row where rownumber>((@pageindex-1)*@pagesize)
注：@pagesize:每一页显现的行数；@pageindex当前页数；
- 获取user表总行数存储过程：
create proc countuser
as
select count(*) from [User]
- Model层下的User类
public class User
{
public int ID { get; set; }
public string Name{get; set; }
public int Pwd { get; set; }
}
- 采用三层架构 DAL层下的Service类
#region 分页查询用户user表
/// <summary>
/// 分页查询用户user表
/// </summary>
/// <returns></returns>
public List<User> selectuser(int pageindex,int pagesize)
{
string sql = "selectuser";
SqlParameter[] sp =
{
new SqlParameter("@pagesize",SqlDbType.Int),
new SqlParameter("@pageindex",SqlDbType.Int)
};
sp[0].Value = pagesize;
sp[1].Value = pageindex;
DataTable table = DBHelper.GetTable(sql, sp);
List<User> users = new List<User>();
foreach(DataRow item in table.Rows)
{
User user = new User();
user.ID = (int)item["ID"];
user.Name = item["Name"].ToString();
user.Pwd = (int)item["Pwd"];
users.Add(user);
}
return users;
}
#endregion
#region 获取user表总行数
/// <summary>
/// 获取user表总行数
/// </summary>
/// <returns></returns>
public int countuser()
{
string sql = "countuser";
return (int)DBHelper.GetTable(sql, null).Rows[0][0];
}
#endregion
DAL下的DBHelper类
static class DBHelper
{
static string str = ConfigurationManager.ConnectionStrings["SQLCon"].ConnectionString;//链接数据库web.config配置（注DBHelper引用System.configuration）
/// <summary>
/// 返回一张表
/// </summary>
/// <param name="sql"></param>
/// <param name="sp"></param>
/// <returns></returns>
public static DataTable GetTable(string sql, SqlParameter[] sp)
{
using (SqlConnection SqlCon = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand(sql, SqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
if (sp != null)
{
cmd.Parameters.AddRange(sp);
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable ds = new DataTable();
try
{
sda.Fill(ds);
}
catch (Exception ex)
{
}
finally
{
SqlCon.Close();
}
return ds;
}
}
}
/// <summary>
/// 受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="sp"></param>
/// <returns></returns>
internal static int ExecuteNonQuery(string sql, SqlParameter[] sp)
{
using (SqlConnection sqlcon = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand(sql, sqlcon))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
if (sp != null)
{
cmd.Parameters.AddRange(sp);
}
sqlcon.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
return 0;
}
finally
{
sqlcon.Close();
}
}
}
}
/// <summary>
/// 第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="sp"></param>
/// <returns></returns>
internal static object ExecuteScalar(string sql, SqlParameter[] sp)
{
using (SqlConnection SqlCon = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand(sql, SqlCon))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
if (sp != null)
{
cmd.Parameters.AddRange(sp);
}
SqlCon.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
return null;
}
finally
{
SqlCon.Close();
}
}
}
}
}
web.config
<connectionStrings>
<add name="SQLCon" connectionString="Server=127.0.0.1;Initial CataLog=JinPinLiNong02;User ID=sa;Pwd=123;" providerName="System.Data.SqlClient" />
</connectionStrings>
- BLL层下的Manager类
public class Manager
{
Service service = new Service();
public List<User> selectuser(int pagesize,int pageindex)
{
return service.selectuser(pageindex,pagesize);
}
public int countuser()
{
return service.countuser();
}
}
- 页面SelectUser.aspx部分代码
<table class="table">
<tbody>
<tr>
<th width="50">ID</th>
<th width="150">用户姓名</th>
<th width="100">用户密码</th>
<th width="50">删除</th>
<th width="50">编辑</th>
</tr>
<%foreach (Model.User item in useres )
{%>
<tr style="text-align:center">
<td align="left"><%=item.ID %></td>
<td><%=item.Name %></td>
<td><%=item.Pwd %></td>
<td><input type="button" class="delebtn btn" did="<%=item.ID %>" value="删除"/></td>
<td><input type="button" class="updatebtn btn" uid="<%=item.ID %>" value="编辑" /></td>
</tr>
<%} %>
</tbody>
</table>
<div class="page">
<asp:Button ID="shou" runat="server" Text="首页" class="btn" OnClick="shou_Click" />
<asp:Button ID="Up" runat="server" Text="上一页" class="btn" OnClick="Up_Click" />
<asp:Button ID="Down" runat="server" Text="下一页" class="btn" OnClick="Down_Click"/>
<asp:Button ID="wei" runat="server" Text="尾页" class="btn" OnClick="wei_Click"/>
<asp:Label ID="Label1" runat="server" Text="页次："></asp:Label>
<asp:Label ID="Label2" runat="server" Text="1"></asp:Label>
<asp:Label ID="Label3" runat="server" Text="共"></asp:Label>
<asp:Label ID="Label4" runat="server" Text=""></asp:Label>
<asp:Label ID="Label5" runat="server" Text="页"></asp:Label>
</div>
- SelectUser.aspx.cs代码
namespace webform.houtai.Log
{
public partial class SelectUser : System.Web.UI.Page
{
Manager manager = new Manager();
public List<Model.User> useres = new List<Model.User>();
int pagesize = 5;//每一页显示五条数据
int pageindex;//记录当前页
int pagecount;//总页数
protected void Page_Load(object sender, EventArgs e)
{
int pageese = manager.countuser();//获取总行数
if (pageese % pagesize == 0)
{
pagecount = pageese / pagesize;
}
else
{
pagecount = (pageese / pagesize) + 1;
}
Label4.Text = pagecount.ToString();
DBuser(1);//加载时第一页
}
//获取数据
public void DBuser(int PageIndex)
{
useres.Clear();
useres = manager.selectuser(pagesize,PageIndex);
}
//首页
protected void shou_Click(object sender, EventArgs e)
{
pageindex = 1;
Label2.Text = pageindex.ToString();
DBuser(pageindex);
}
//上一页
protected void Up_Click(object sender, EventArgs e)
{
pageindex = int.Parse(Label2.Text);
if (pageindex <= 1)
{
pageindex = 1;
Label2.Text = pageindex.ToString();
DBuser(pageindex);
}
else
{
pageindex = pageindex - 1;
Label2.Text = pageindex.ToString();
DBuser(pageindex);
}
}
//下一页
protected void Down_Click(object sender, EventArgs e)
{
pageindex = int.Parse(Label2.Text);
pagecount = int.Parse(Label4.Text);
if (pageindex >= pagecount)
{
pageindex = pagecount;
Label2.Text = pageindex.ToString();
DBuser(pageindex);
}
else
{
pageindex = pageindex + 1;
Label2.Text = pageindex.ToString();
DBuser(pageindex);
}
}
//尾页
protected void wei_Click(object sender, EventArgs e)
{
pagecount = int.Parse(Label4.Text);
pageindex = pagecount;
Label2.Text = pageindex.ToString();
DBuser(pageindex);
}
}
}
8.三层（DAL,BLL,UI）之间的引用：DAL引用Model,BLL引用DAL、Model，UI引用BLL、Model
浙公网安备 33010602011771号