Winform + ADO.Net 实现分页查询
使用ADO.NET + 存储过程,在winform里实现分页查询的功能。
实现思路:
- 使用存储过程,根据查询条件,查出若干个表;
- 返回DataSet,再去读取DataSet的Table。
以一个超市管理系统里面登录日志的查询为例,做示例代码。
数据库表结构
一部分测试数据:
存储过程编写
1 if exists(select * from sysobjects where name='usp_LoginLogsPagingQuery') 2 drop procedure usp_LoginLogsPagingQuery 3 go 4 5 create procedure usp_LoginLogsPagingQuery 6 @PageSize int,--每页显示数据行数 7 @RecordCount int, 8 @BegainTime dateTime, 9 @EndTime dateTime 10 as 11 declare @CurrentPage int=1--当前页数 12 declare @Temp int= @PageSize*(@CurrentPage-1) 13 while (@Temp<=@RecordCount) 14 Begin 15 select Top (@PageSize) LoginId,LoginName, ServerName,LoginTime,ExitTime from LoginLogs 16 where LoginId not in (select Top (@Temp) LoginId from LoginLogs where LoginTime between @BegainTime and @EndTime order by LoginTime ASC) and LoginTime between @BegainTime and @EndTime order by LoginTime ASC 17 set @CurrentPage=@CurrentPage+1 18 set @Temp = @PageSize*(@CurrentPage-1) 19 End 20 go
SQLHelper
1 public static DataSet GetDataSetByProcedure(string storedProcdureName, SqlParameter[] parameters) 2 { 3 SqlConnection conn = new SqlConnection(connString); 4 SqlCommand cmd = new SqlCommand(storedProcdureName, conn); 5 cmd.CommandType = CommandType.StoredProcedure; 6 SqlDataAdapter da = new SqlDataAdapter(cmd); 7 DataSet ds = new DataSet(); 8 try 9 { 10 conn.Open(); 11 cmd.Parameters.AddRange(parameters); 12 13 da.Fill(ds); 14 return ds; 15 } 16 catch (Exception ex) 17 { 18 throw new Exception("DAL-GetDataSet带参数方法发生错误:" + ex.Message); 19 } 20 finally 21 { 22 conn.Close(); 23 } 24 }
相关实体类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Models { public class LoginLogPageQuery { public DateTime BeginTime { get; set; } public DateTime EndTime { get; set; } public int CurrentPage { get; set; } public int PageSize { get; set; } public int RecordCount { get; set; } public int PageCount { get; set; } } }
数据分页服务:DataPageQueryService
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using Models; 7 using System.Data.SqlClient; 8 using System.Data; 9 10 namespace DAL 11 { 12 public class DataPageQueryService 13 { 14 /// <summary> 15 /// 使用存储过程分页查询LoginLogs 16 /// </summary> 17 /// <param name="objLog"></param> 18 /// <returns></returns> 19 public DataSet GetLoginLogPageQuery(LoginLogPageQuery objLog) 20 { 21 SqlParameter[] parameters = new SqlParameter[] 22 { 23 new SqlParameter("@PageSize",objLog.PageSize), 24 new SqlParameter("@RecordCount",objLog.RecordCount), 25 new SqlParameter("@BeginTime",objLog.BeginTime), 26 new SqlParameter("@EndTime",objLog.EndTime) 27 }; 28 29 try 30 { 31 return SQLHelper.GetDataSetByProcedure("usp_LoginLogsPagingQuery",parameters); 32 } 33 catch (Exception ex) 34 { 35 throw new Exception(ex.Message); 36 } 37 } 38 39 /// <summary> 40 /// 查询总数据量 41 /// </summary> 42 /// <param name="objLog"></param> 43 /// <returns></returns> 44 public int GetRecordCount(LoginLogPageQuery objLog) 45 { 46 string sql = "select count(*) from Loginlogs where LoginTime between @BeginTime and @EndTime"; 47 SqlParameter[] parameters = new SqlParameter[] 48 { 49 new SqlParameter("@BeginTime",objLog.BeginTime), 50 new SqlParameter("@EndTime",objLog.EndTime) 51 }; 52 try 53 { 54 return Convert.ToInt32(SQLHelper.GetSingleResult(sql,parameters)); 55 } 56 catch (Exception ex) 57 { 58 throw new Exception(ex.Message); 59 } 60 } 61 62 } 63 }
数据分页业务逻辑:LoginLogPageQueryManager
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using DAL; using Models; using System.Data; namespace BLL { public class LoginLogPageQueryManager { private DataPageQueryService objPageQuery = new DataPageQueryService(); public DataSet GetLoginLogPageQueryDS(LoginLogPageQuery objLog) { return objPageQuery.GetLoginLogPageQuery(objLog); } public int GetRecordCount(LoginLogPageQuery objLog) { return objPageQuery.GetRecordCount(objLog); } } }
显示界面
各按钮事件
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using Models; 10 using BLL; 11 12 namespace SMManager 13 { 14 public partial class FrmLogQuery : Form 15 { 16 private DataSet objDS = new DataSet(); 17 private LoginLogPageQueryManager objPageQuery = new LoginLogPageQueryManager(); 18 private LoginLogPageQuery objLog = null; 19 public FrmLogQuery() 20 { 21 InitializeComponent(); 22 23 this.cboPageSize.SelectedIndex = 0; 24 this.dgvLogs.AutoGenerateColumns = false; 25 } 26 //提交查询 27 private void btnQuery_Click(object sender, EventArgs e) 28 { 29 objLog = new LoginLogPageQuery() 30 { 31 PageSize = Convert.ToInt32(this.cboPageSize.Text), 32 BeginTime = Convert.ToDateTime(this.dtpStart.Text), 33 EndTime = Convert.ToDateTime(this.dtpEnd.Text).AddDays(1.0) 34 }; 35 try 36 { 37 //获取数据总量 38 objLog.RecordCount = objPageQuery.GetRecordCount(objLog); 39 //获取分页DataSet 40 objDS = objPageQuery.GetLoginLogPageQueryDS(objLog); 41 this.dgvLogs.DataSource = objDS.Tables[objLog.CurrentPage]; 42 } 43 catch (Exception ex) 44 { 45 MessageBox.Show("数据库发生错误,请检查连接或联系管理员!","错误提示"); 46 new ErrorLogManager().ErrorLog(new ErrorLog() 47 { 48 LoginId = Program.objCurrentAdmin.LoginId, 49 Operate=Operate.ErrorHappenedWhenLoginLogPageQuery, 50 ErrorMessage=ex.Message 51 }); 52 return; 53 } 54 55 //计算当前页 56 this.lblCurrentPage.Text = (objLog.CurrentPage + 1).ToString(); 57 //计算总页数 58 int pageCount = objLog.RecordCount / objLog.PageSize; 59 objLog.PageCount = objLog.RecordCount % objLog.PageSize == 0 ? pageCount : pageCount + 1; 60 this.lblPageCount.Text = objLog.PageCount.ToString(); 61 62 if (objLog.PageCount == 0) 63 { 64 MessageBox.Show("该查询下无数据!", "查询提示"); 65 return; 66 } 67 //所有按钮开启 68 this.btnFirst.Enabled = true; 69 this.btnNext.Enabled = true; 70 this.btnPrevious.Enabled = true; 71 this.btnLast.Enabled = true; 72 this.btnGoTo.Enabled = true; 73 //关闭不需要的按钮 74 if (objLog.PageCount == 1) 75 { 76 this.btnFirst.Enabled = false; 77 this.btnNext.Enabled = false; 78 this.btnPrevious.Enabled = false; 79 this.btnLast.Enabled = false; 80 this.btnGoTo.Enabled = false; 81 } 82 } 83 //显示行号 84 private void dgvLogs_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e) 85 { 86 DataGridViewStyle.DgvRowPostPaint(this.dgvLogs, e); 87 } 88 //关闭窗口 89 private void btnClose_Click(object sender, EventArgs e) 90 { 91 this.Close(); 92 } 93 94 #region 分页显示 95 96 //跳转到 97 private void btnGoTo_Click(object sender, EventArgs e) 98 { 99 if (objLog == null)//未查询 100 { 101 return; 102 } 103 int current = Convert.ToInt32(this.txtGoTo.Text.Trim()); 104 if (current < 1 || current > objLog.PageCount) 105 { 106 MessageBox.Show("无该页数据!", "查询提示"); 107 return; 108 } 109 this.lblCurrentPage.Text = current.ToString(); 110 objLog.CurrentPage = current - 1; 111 this.dgvLogs.DataSource = objDS.Tables[objLog.CurrentPage]; 112 //所有按钮开启 113 this.btnFirst.Enabled = true; 114 this.btnNext.Enabled = true; 115 this.btnPrevious.Enabled = true; 116 this.btnLast.Enabled = true; 117 //当到达第一页 118 if (objLog.CurrentPage == 0) 119 { 120 this.btnPrevious.Enabled = false; 121 this.btnFirst.Enabled = false; 122 } 123 //当到达最后一页 124 if (objLog.CurrentPage == objLog.PageCount - 1)//当前页从0开始,页总数从1开始 125 { 126 this.btnNext.Enabled = false; 127 this.btnLast.Enabled = false; 128 } 129 } 130 //第一页 131 private void btnFirst_Click(object sender, EventArgs e) 132 { 133 if (objLog == null)//未查询 134 { 135 return; 136 } 137 this.dgvLogs.DataSource = null; 138 objLog.CurrentPage = 0; 139 this.dgvLogs.DataSource = objDS.Tables[objLog.CurrentPage]; 140 //计算当前页 141 this.lblCurrentPage.Text = (objLog.CurrentPage + 1).ToString(); 142 //开启按钮 143 this.btnLast.Enabled = true; 144 this.btnNext.Enabled = true; 145 //关闭按钮 146 this.btnPrevious.Enabled = false; 147 this.btnFirst.Enabled = false; 148 } 149 //下一页 150 private void btnNext_Click(object sender, EventArgs e) 151 { 152 if (objLog == null)//未查询 153 { 154 return; 155 } 156 this.dgvLogs.DataSource = null; 157 objLog.CurrentPage++; 158 this.dgvLogs.DataSource = objDS.Tables[objLog.CurrentPage]; 159 this.btnFirst.Enabled = true; 160 this.btnPrevious.Enabled = true; 161 //计算当前页 162 this.lblCurrentPage.Text = (objLog.CurrentPage + 1).ToString(); 163 //当到达最后一页 164 if (objLog.CurrentPage == objLog.PageCount - 1)//当前页从0开始,页总数从1开始 165 { 166 this.btnNext.Enabled = false; 167 this.btnLast.Enabled = false; 168 } 169 } 170 //上一页 171 private void btnPrevious_Click(object sender, EventArgs e) 172 { 173 if (objLog == null)//未查询 174 { 175 return; 176 } 177 this.dgvLogs.DataSource = null; 178 objLog.CurrentPage--; 179 this.dgvLogs.DataSource = objDS.Tables[objLog.CurrentPage]; 180 this.btnNext.Enabled = true; 181 this.btnLast.Enabled = true; 182 //计算当前页 183 this.lblCurrentPage.Text = (objLog.CurrentPage + 1).ToString(); 184 //当到达第一页 185 if (objLog.CurrentPage == 0) 186 { 187 this.btnPrevious.Enabled = false; 188 this.btnFirst.Enabled = false; 189 } 190 } 191 //最后页 192 private void btnLast_Click(object sender, EventArgs e) 193 { 194 if (objLog == null)//未查询 195 { 196 return; 197 } 198 this.dgvLogs.DataSource = null; 199 //当前页从0开始,页总数从1开始 200 objLog.CurrentPage = objLog.PageCount - 1; 201 this.dgvLogs.DataSource = objDS.Tables[objLog.CurrentPage]; 202 //计算当前页 203 this.lblCurrentPage.Text = (objLog.CurrentPage + 1).ToString(); 204 //开启按钮 205 this.btnPrevious.Enabled = true; 206 this.btnFirst.Enabled = true; 207 //关闭按钮 208 this.btnNext.Enabled = false; 209 this.btnLast.Enabled = false; 210 } 211 212 #endregion 213 } 214 }
结果展示
该项目还未全部完成,完成后我会上传整个项目的GitHub地址……还请各位指教!
注:以上代码为显示出记录总数,自行查看。