利用三層架構實現對資料庫數據的分頁功能和點擊每個頁碼實現不同分頁面之間的跳轉 ...
一、業務需求:
利用三層架構實現對資料庫數據的分頁功能和點擊每個頁碼實現不同分頁面之間的跳轉,效果如下圖所示:
二、三層結構代碼詳細示例
1、表現層代碼
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Demo3.aspx.cs" Inherits="Chapter05.Demo3" %> 2 3 <!DOCTYPE html> 4 5 <html xmlns="http://www.w3.org/1999/xhtml"> 6 <head runat="server"> 7 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 8 <title></title> 9 <%--引入css樣式表--%> 10 <link href="PageBar.css" rel="stylesheet" /> 11 </head> 12 <body> 13 <form id="form1" runat="server"> 14 <div> 15 <table> 16 17 <asp:Repeater ID="Repeater1" runat="server"> 18 <HeaderTemplate> 19 <tr> 20 <th>登錄名</th> 21 <th>密碼</th> 22 <th>是否激活</th> 23 </tr> 24 </HeaderTemplate> 25 <ItemTemplate> 26 <tr> 27 <td><%#Eval("LoginName") %></td> 28 <td><%#Eval("Pwd") %></td> 29 <td><%#Eval("IsEnabled") %></td> 30 </tr> 31 </ItemTemplate> 32 </asp:Repeater> 33 </table> 34 <%-- <a href="Demo3.aspx?pageIndex=1">1</a> 35 <a href="Demo3.aspx?pageIndex=2">2</a> 36 <a>3</a> 37 <a>4</a> 38 <a>5</a>--%> 39 <%--調用後臺PageBar類中的代碼--%> 40 <%=PageBar %> 41 </div> 42 </form> 43 </body> 44 </html>View Code
2、表現層後臺代碼
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 8 using Chapter05.BLL; 9 using Chapter05.Models; 10 using System.Text; 11 12 namespace Chapter05 13 { 14 public partial class Demo3 : System.Web.UI.Page 15 { 16 //定義公共屬性PageBar 17 public string PageBar { get; set; } 18 protected void Page_Load(object sender, EventArgs e) 19 { 20 if (!IsPostBack) 21 { 22 //當前頁 23 int pageIndex = 1; 24 //每頁行數 25 int pageSize = 5; 26 //總頁數 27 int pageCount = 0; 28 //總行數 29 int rowCount = 0; 30 //判斷當前頁是否為空 31 if (Request.QueryString["pageIndex"] != null) 32 { 33 34 pageIndex = Convert.ToInt32(Request.QueryString["pageIndex"].ToString()); 35 } 36 37 LoginInfoBLL bll = new LoginInfoBLL(); 38 List<LoginInfo> list = bll.GetPageData(pageIndex, pageSize, ref rowCount, ref pageCount); 39 //綁定數據源 40 this.Repeater1.DataSource = list; 41 this.Repeater1.DataBind(); 42 //調用分頁方法PageBar 43 this.PageBar = Chapter05.PageBar.CreatePageBar(pageIndex, pageCount); 44 } 45 46 } 47 48 49 50 } 51 }View Code
3、邏輯判斷層代碼
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Linq; 5 using System.Text; 6 using System.Threading.Tasks; 7 using Chapter05.DAL; 8 using Chapter05.Models; 9 10 namespace Chapter05.BLL 11 { 12 public class LoginInfoBLL 13 { 14 public DataSet GetAll() 15 { 16 LoginInfoDAL dal = new LoginInfoDAL(); 17 return dal.GetAll(); 18 } 19 20 public List<LoginInfo> GetAllList() 21 { 22 LoginInfoDAL dal = new LoginInfoDAL(); 23 return dal.GetAllList(); 24 } 25 26 public bool DoDelete(string id) 27 { 28 LoginInfoDAL dal = new LoginInfoDAL(); 29 return dal.Delete(id) > 0; 30 } 31 32 public LoginInfo GetEntityById(string id) 33 { 34 return new LoginInfoDAL().GetEntityById(id); 35 } 36 37 public bool ModifyLoginInfo(LoginInfo info) 38 { 39 return new LoginInfoDAL().Update(info) > 0; 40 } 41 //存儲過程分頁 42 public List<LoginInfo> GetPageData(int pageIndex, int pageSize, ref int rowCount, ref int pageCount) { 43 44 return new LoginInfoDAL().GetPageData(pageIndex, pageSize, ref rowCount, ref pageCount); 45 } 46 } 47 48 49 }View Code
4、數據訪問層代碼
——LoginInfoDAL
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Linq; 5 using System.Text; 6 using System.Threading.Tasks; 7 using Chapter05.Models; 8 using System.Data.SqlClient; 9 10 namespace Chapter05.DAL 11 { 12 public class LoginInfoDAL 13 { 14 15 #region 獲取全部數據 16 17 /// <summary> 18 /// 19 /// </summary> 20 /// <returns></returns> 21 public DataSet GetAll() 22 { 23 //1.定義SQL 24 string sql = "select * from LoginInfo"; 25 return SqlHelper.GetDataSet(sql, CommandType.Text); 26 } 27 28 public List<LoginInfo> GetAllList() 29 { 30 List<LoginInfo> result = new List<LoginInfo>(); 31 DataSet ds = new DataSet(); 32 ds = GetAll(); 33 if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) 34 { 35 foreach (DataRow dr in ds.Tables[0].Rows) 36 { 37 LoginInfo temp = DataRowToEntity(dr); 38 // 轉換的結果 ,添加到 集合中 39 result.Add(temp); 40 } 41 } 42 else 43 { 44 result = null; 45 } 46 return result; 47 } 48 49 50 #endregion 51 52 #region 查詢單個實體 53 54 public LoginInfo GetEntityById(string id) 55 { 56 LoginInfo result = null; 57 string sql = "select * from LoginInfo where id=@id"; 58 SqlParameter param = new SqlParameter("@id", SqlDbType.VarChar, 50); 59 param.Value = id; 60 61 DataSet ds = SqlHelper.GetDataSet(sql, CommandType.Text, param); 62 if (ds.Tables[0].Rows.Count > 0) 63 { 64 result = DataRowToEntity(ds.Tables[0].Rows[0]); 65 } 66 return result; 67 } 68 69 #endregion 70 71 #region 分頁數據 72 //定義集合類型的方法,並傳入參數,聲明輸出參數 73 public List<LoginInfo> GetPageData(int pageIndex, int pageSize, ref int rowCount, ref int pageCount) 74 { 75 //定義數據對象集合 76 List<LoginInfo> result = new List<LoginInfo>(); 77 //調用存儲過程 78 string sql = "PROC_PageData_LoginInfo"; 79 80 SqlParameter[] parameters = new SqlParameter[] { 81 new SqlParameter("@pageIndex",SqlDbType.Int), 82 new SqlParameter("@pageSize",SqlDbType.Int), 83 new SqlParameter("@rowCount",SqlDbType.Int), 84 new SqlParameter("@pageCount",SqlDbType.Int) 85 }; 86 87 parameters[0].Value = pageIndex; 88 parameters[1].Value = pageSize; 89 //聲明參數類型:輸出參數 90 parameters[2].Direction = ParameterDirection.Output; 91 parameters[3].Direction = ParameterDirection.Output; 92 //調用sqlhelper方法,得到數據集合,命令類型為存儲過程 93 DataSet ds = SqlHelper.GetDataSet(sql, CommandType.StoredProcedure, parameters); 94 //給輸出參數賦值 95 rowCount = Convert.ToInt32(parameters[2].Value); 96 pageCount = Convert.ToInt32(parameters[3].Value); 97 //判斷數據集是否為空和數據集合第一張表中是否有數據 98 if (ds != null && ds.Tables[0].Rows.Count > 0) 99 { 100 //遍歷第一張表中的每一行數據 101 foreach (DataRow dr in ds.Tables[0].Rows) 102 { 103 //實例化公共模型LoginInfo 104 LoginInfo temp = new LoginInfo(); 105 //調用方法將每一行數據轉換為對象,並賦值給temp對象 106 temp = DataRowToEntity(dr); 107 //將temp對象賦給對象集合result 108 result.Add(temp); 109 } 110 } 111 else 112 { 113 result = null; 114 } 115 //返回對象集合 116 return result; 117 } 118 119 #endregion 120 121 #region 把數據行轉化成 對象 122 123 private LoginInfo DataRowToEntity(DataRow dr) 124 { 125 LoginInfo temp = new LoginInfo(); 126 temp.ID = dr["ID"].ToString(); 127 temp.LoginName = dr["LoginName"].ToString(); 128 temp.Pwd = dr["Pwd"].ToString(); 129 temp.IsEnabled = dr["IsEnabled"].ToString(); 130 temp.IsDelete = Convert.ToInt32(dr["IsDelete"]); 131 return temp; 132 } 133 134 #endregion 135 136 #region 根據主鍵刪除數據 137 public int Delete(string id) 138 { 139 string sql = "delete from LoginInfo where id=@id"; 140 141 SqlParameter param = new SqlParameter("@id", SqlDbType.VarChar, 50); 142 param.Value = id; 143 return SqlHelper.ExecuteNonquery(sql, CommandType.Text, param); 144 145 } 146 147 148 #endregion 149 150 #region 修改數據 151 152 public int Update(LoginInfo loginInfo) 153 { 154 string sql = "update LoginInfo set LoginName=@loginName,Pwd=@pwd,IsDelete=@isDelete where Id=@id"; 155 156 SqlParameter[] paramters = new SqlParameter[] { 157 new SqlParameter("@loginName",SqlDbType.VarChar,50), 158 new SqlParameter("@pwd",SqlDbType.VarChar,16), 159 new SqlParameter("@isDelete",SqlDbType.Int), 160 new SqlParameter("@id",SqlDbType.VarChar,50) 161 }; 162 163 paramters[0].Value = loginInfo.LoginName; 164 paramters[1].Value = loginInfo.Pwd; 165 paramters[2].Value = loginInfo.IsDelete; 166 paramters[3].Value = loginInfo.ID; 167 168 return SqlHelper.ExecuteNonquery(sql, CommandType.Text, paramters); 169 } 170 171 #endregion 172 } 173 }View Code
——SQLHelper
1 using System; 2 using System.Collections.Generic; 3 using System.Configuration; 4 using System.Data; 5 using System.Data.SqlClient; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 10 namespace Chapter05.DAL 11 { 12 public class SqlHelper 13 { 14 /// <summary> 15 /// 獲取鏈接字元串 16 /// </summary> 17 private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; 18 19 /// <summary> 20 /// 執行查詢操作 21 /// </summary> 22 /// <param name="sql">SQL 語句</param> 23 /// <param name="type">CommandType 命令的類型【text=>sql 腳本,StoredProcedure=>存儲過程】</param> 24 /// <param name="pars">參數</param> 25 /// <returns>返回結果集 dataset</returns> 26 public static DataSet GetDataSet(string sql, CommandType type, params SqlParameter[] pars) 27 { 28 //1.創建鏈接 29 SqlConnection conn = new SqlConnection(connStr); 30 //2.打開鏈接 31 conn.Open(); 32 //3.創建命令對象 33 SqlCommand cmd = new SqlCommand(sql, conn); 34 //4.創建 適配器 35 SqlDataAdapter da = new SqlDataAdapter(cmd); 36 // 當傳入的參數不為空時,直接添加到Cmd 對象的Parameters 屬性上 37 if (pars != null) 38 { 39 foreach (var item in pars) 40 { 41 item.Value = item.Value == null ? DBNull.Value : item.Value; 42 cmd.Parameters.Add(item); 43 } 44 //cmd.Parameters.AddRange(pars); 45 } 46 //根據 使用者 傳入的命令類型,給 Cmd 對象的CommandType 屬性賦值; 47 cmd.CommandType = type; 48 DataSet ds = new DataSet(); 49 da.Fill(ds); 50 //關閉鏈接 51 conn.Close(); 52 return ds; 53 } 54 55 56 /// <summary> 57 /// 執行SQL語句:返回 影響行數 58 /// </summary> 59 /// <param name="sql">SQL 語句</param> 60 /// <param name="type">CommandType 命令的類型【text=>sql 腳本,StoredProcedure=>存儲過程】</param> 61 /// <param name="pars">參數</param> 62 /// <returns></returns> 63 public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars) 64 { 65 using (SqlConnection conn = new SqlConnection(connStr)) 66 { 67 using (SqlCommand cmd = new SqlCommand(sql, conn)) 68 { 69 if (pars != null) 70 { 71 foreach (var item in pars) 72 { 73 item.Value = item.Value == null ? DBNull.Value : item.Value; 74 cmd.Parameters.Add(item); 75 } 76 //cmd.Parameters.AddRange(pars); 77 78 } 79 cmd.CommandType = type; 80 conn.Open(); 81 return cmd.ExecuteNonQuery(); 82 } 83 } 84 } 85 86 /// <summary> 87 /// 執行查詢,並返回查詢所返回的結果集中第一行的第一列 88 /// </summary> 89 /// <param name="sql"></param> 90 /// <param name="type"></param> 91 /// <param name="pars"></param> 92 /// <returns></returns> 93 public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] pars) 94 { 95 using (SqlConnection conn = new SqlConnection(connStr)) 96 { 97 using (SqlCommand cmd = new SqlCommand(sql, conn)) 98 { 99 if (pars != null) 100 { 101 //cmd.Parameters.AddRange(pars); 102 foreach (var item in pars) 103 { 104 item.Value = item.Value == null ? DBNull.Value : item.Value; 105 cmd.Parameters.Add(item); 106 } 107 } 108 cmd.CommandType = type; 109 conn.Open(); 110 return cmd.ExecuteScalar(); 111 } 112 } 113 } 114 } 115 }View Code
5、封裝的實體類--PageBar代碼(實現點擊數字切換分頁頁面)
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Web; 6 7 namespace Chapter05 8 { 9 public class PageBar 10 { 11 /// <summary> 12 /// 分頁方法 13 /// </summary> 14 /// <param name="pageIndex"></param>