Winform + ADO.Net 实现分页查询

使用ADO.NET + 存储过程，在winform里实现分页查询的功能。

实现思路：

  1. 使用存储过程，根据查询条件，查出若干个表；
  2. 返回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
View Code

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         }
View Code

相关实体类

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; }
    }
}
View Code

数据分页服务：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 }
View Code

数据分页业务逻辑：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);
        }
    }
}
View Code

显示界面

各按钮事件

  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 }
View Code

结果展示

 

 该项目还未全部完成，完成后我会上传整个项目的GitHub地址……还请各位指教！

注：以上代码为显示出记录总数，自行查看。

 

posted @ 2018-01-03 10:24  EasonDongH  阅读(1303)  评论(0)  编辑  收藏  举报