分页查询,很早就听过了,尤其是在“高校”的项目中更是听得比较多,一直想着自己研究一下,看看到底分页查询有什么神秘之处。但是,一直因为自己在做其他的内容,所以一直没有好好的学习一下。现在,做统计查询的时候,因为查询的数据量比较大,差不多一秒一条数据,甚至是一秒钟600条数据的时间查询,所以不得不采用分页查询了。
我们常说的分页查询分为两种。一种是假分页,也就是一次性的把需要的数据全部查出,然后在显示的时候是分页显示的。这样做的好处就是直观的给用户显示出需要的内容,而不用采用滚动条来滚动查看。另外就是,分页的速度是比较快的。
在这里我主要想总结的就是另一种分页方式——真分页。所谓真分页,就是所见即所得,我们看到的数据,就是我们当前查询到的数据。这样做的好处就是,当我们查询的数据量非常大的时候,比如当我们查询一万条数据的时候,可以非常迅速的查询出先展示给用户的那部分数据,而不用去额外的查询此刻不需要的内容。因此,这样做大大增加了查询的速度,提高了系统的性能,给用户以非常好的用户体验。
以下就是我在项目中做的一个实例代码,供大家参考:
<span style="font-size:24px;"> public ucDataPage()
{
InitializeComponent();
pIndex = 1;
Loaded += delegate
{
//首页
btnFirst.MouseLeftButtonUp += new MouseButtonEventHandler(btnFirst_Click);
//上一页
btnPrev.MouseLeftButtonUp += new MouseButtonEventHandler(btnPrev_Click);
//下一页
btnNext.MouseLeftButtonUp += new MouseButtonEventHandler(btnNext_Click);
//末页
btnLast.MouseLeftButtonUp += new MouseButtonEventHandler(btnLast_Click);
btnGo.Click += new RoutedEventHandler(btnGo_Click);
};
}
private DataTable _dt = new DataTable();
//每页显示多少条
public static int pageNum = 10;
//当前是第几页
public static int pIndex = 1;
//对象
public DataGrid grdList;
//最大页数
public static int MaxIndex = 1;
//总记录数
public static int allNum = 0;
public static int Count = 0;
/// <summary>
/// 初始化数据
/// </summary>
/// <param name="grd"></param>
/// <param name="ds"></param>
/// <param name="Num"></param>
public void ShowPages(DataGrid grd, DataTable ds, int Num)
{
if (ds == null || ds.Rows.Count == 0)
{
return;
}
if (ds.Rows.Count == 0)
{
return;
}
DataTable dt = ds;
_dt = dt.Clone();
grdList = grd;
pageNum = Num;
pIndex = 1;
foreach (DataRow r in dt.Rows)
{
_dt.ImportRow(r);
}
SetMaxIndex();
ReadDataTable();
if (MaxIndex > 1)
{
pageGo.IsReadOnly = false;
btnGo.IsEnabled = true;
}
}
/// <summary>
/// 画数据
/// </summary>
private void ReadDataTable()
{
DisplayPagingInfo();
}
/// <summary>
/// 画每页显示等数据
/// </summary>
private void DisplayPagingInfo()
{
if (pIndex == 1)
{
btnPrev.IsEnabled = false;
btnFirst.IsEnabled = false;
}
else
{
btnPrev.IsEnabled = true;
btnFirst.IsEnabled = true;
}
if (pIndex == MaxIndex)
{
btnNext.IsEnabled = false;
btnLast.IsEnabled = false;
}
else
{
btnNext.IsEnabled = true;
btnLast.IsEnabled = true;
}
tbkRecords.Text = string.Format("每页{0}条/共{1}条", pageNum, allNum);
int first = (pIndex - 4) > 0 ? (pIndex - 4) : 1;
int last = (first + 9) > MaxIndex ? MaxIndex : (first + 9);
grid.Children.Clear();
for (int i = first; i <= last; i++)
{
ColumnDefinition cdf = new ColumnDefinition();
grid.ColumnDefinitions.Add(cdf);
TextBlock tbl = new TextBlock();
tbl.Text = i.ToString();
tbl.Style = FindResource("PageTextBlock3") as Style;
tbl.MouseLeftButtonUp += new MouseButtonEventHandler(tbl_MouseLeftButtonUp);
tbl.MouseLeftButtonDown += new MouseButtonEventHandler(tbl_MouseLeftButtonDown);
if (i == pIndex)
{
tbl.IsEnabled = false;
}
Grid.SetColumn(tbl, grid.ColumnDefinitions.Count - 1);
Grid.SetRow(tbl, 0);
grid.Children.Add(tbl);
}
}
/// <summary>
/// 首页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnFirst_Click(object sender, System.EventArgs e)
{
pIndex = 1;
RaisePageChanged();
ReadDataTable();
}
/// <summary>
/// 首页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnFirst_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
e.Handled = true;
}
/// <summary>
/// 上一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPrev_Click(object sender, System.EventArgs e)
{
if (pIndex <= 1)
{
return;
}
pIndex--;
RaisePageChanged();
ReadDataTable();
}
/// <summary>
/// 上一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPrev_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
e.Handled = true;
RaisePageChanged();
}
/// <summary>
/// 下一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnNext_Click(object sender, System.EventArgs e)
{
if (pIndex >= MaxIndex)
{
return;
}
pIndex++;
RaisePageChanged();
ReadDataTable();
}
/// <summary>
/// 下一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnNext_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
e.Handled = true;
}
/// <summary>
/// 末页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLast_Click(object sender, System.EventArgs e)
{
pIndex = MaxIndex;
RaisePageChanged();
ReadDataTable();
}
/// <summary>
/// 末页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLast_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
e.Handled = true;
}
/// <summary>
/// 设置最大页
/// </summary>
private void SetMaxIndex()
{
//多少页
int Pages = Count / pageNum;
if (Count != (Pages * pageNum))
{
if (Count < (Pages * pageNum))
{
Pages--;
}
else
{
Pages++;
}
MaxIndex = Pages;
allNum = _dt.Rows.Count;
}
}
/// <summary>
/// 跳转到多少页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnGo_Click(object sender, RoutedEventArgs e)
{
if (IsNumber(pageGo.Text))
{
int pageNum = int.Parse(pageGo.Text);
if (pageNum > 0 && pageNum <= MaxIndex)
{
pIndex = pageNum;
RaisePageChanged();
ReadDataTable();
}
else if (pageNum > MaxIndex)
{
pIndex = MaxIndex;
RaisePageChanged();
ReadDataTable();
}
}
pageGo.Text = "";
}
/// <summary>
/// 分页数字的点击触发事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tbl_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
TextBlock tbl = sender as TextBlock;
if (tbl == null)
{
return;
}
int index = int.Parse(tbl.Text.ToString());
pIndex = index;
if (index > MaxIndex)
{
pIndex = MaxIndex;
}
if (index < 1)
{
pIndex = 1;
}
RaisePageChanged();
ReadDataTable();
}
void tbl_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
e.Handled = true;
}
private static Regex RegNumber = new Regex("^[0-9]+$");
/// <summary>
/// 判断是否为数字
/// </summary>
/// <param name="valString"></param>
/// <returns></returns>
public static bool IsNumber(string valString)
{
Match m = RegNumber.Match(valString);
return m.Success;
}
#region 字段、属性、委托
public delegate void PageChangedEventHandler(object sender, PageChangedEventArgs args);
private PageChangedEventArgs pageChangedEventArgs;
#endregion
/// <summary>
/// 引发分页更改事件
/// </summary>
private void RaisePageChanged()
{
if (pageChangedEventArgs == null)
{
pageChangedEventArgs = new PageChangedEventArgs(PageChangedEvent, pageNum, pIndex);
}
else
{
pageChangedEventArgs.PageSize = pageNum;
pageChangedEventArgs.PageIndex = pIndex;
}
RaiseEvent(pageChangedEventArgs);
}
public static readonly RoutedEvent PageChangedEvent = EventManager.RegisterRoutedEvent("PageChanged", RoutingStrategy.Bubble, typeof(PageChangedEventHandler), typeof(ucDataPage));
public class PageChangedEventArgs : RoutedEventArgs
{
public int PageSize { get; set; }
public int PageIndex { get; set; }
public PageChangedEventArgs(RoutedEvent routeEvent, int pageSize, int pageIndex)
: base(routeEvent)
{
PageSize = pageSize;
PageIndex = pageIndex;
}
}
public event PageChangedEventHandler PageChanged
{
add
{
AddHandler(PageChangedEvent, value);
}
remove
{
RemoveHandler(PageChangedEvent, value);
}
}
}
}</span>
以上就是主要的计算逻辑,其中真分页和假分页的最大区别就是通过获得当前页的索引、每页容量等内容来作为查询条件来实现查询功能。另外,一个区别就是需要通过委托来将分页的功能引入到项目中,最后的几个方法就是委托的实现。
接下来就是怎么加入到项目中了,首先是将分页的功能作为用户控件添加到项目中,前台代码如下:
<span style="font-size:24px;"><pre name="code" class="html"> <my:ucDataPage Grid.Row="1" HorizontalAlignment="Left" Margin="10,0,0,0" x:Name="ucDataPage" PageChanged="dataPager_PageChanged" VerticalAlignment="Bottom" Width="1114" /></span>
后台的委托方法:
<span style="font-size:24px;"><pre name="code" class="csharp"> </span>
<span style="font-size:24px;">private void dataPager_PageChanged(object sender, ucDataPage.PageChangedEventArgs args)
{
#region 查询数据
try
{
System.Data.DataTable dt = new System.Data.DataTable();
dt = Query(StrWhere, PageSize, ucDataPage.pIndex);
int PIndex = ucDataPage.pIndex;
//dt.DefaultView.Sort = "S_MONITORINGTIME DESC"; // 将查询到的结果按倒序排列显示
if (dt.Rows.Count == 0)
{
MessageBox.Show("您查询的时间段内没有符合条件的信息,请重新选择查询时间!", "提示");
dataGrid1.ItemsSource = dt.DefaultView;
btnQuery.IsEnabled = true;
return;
}
else
{
//风速小数位数设置
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
if (row["I_ALARMWINDSPEED"] != null || row["I_ALARMWINDSPEED"].ToString() != "")
{
row["I_ALARMWINDSPEED"] = Convert.ToDouble(row["I_ALARMWINDSPEED"]).ToString("F1");
}
if (Convert.IsDBNull(row["I_WINDSPEED1"]) == false)
{
row["I_WINDSPEED1"] = Convert.ToDouble(row["I_WINDSPEED1"]).ToString("F1");
}
if (Convert.IsDBNull(row["I_WINDSPEED2"]) == false)
{
row["I_WINDSPEED2"] = Convert.ToDouble(row["I_WINDSPEED2"]).ToString("F1");
}
}
dataGrid1.ItemsSource = dt.DefaultView;
ucDataPage.ShowPages(dataGrid1, dt, PageSize);
btnQuery.IsEnabled = true;
ucDataPage.pIndex = PIndex;
}
DT = dt;
}
catch (Exception ex)
{
MessageBox.Show("查询风速信息时出现问题,错误信息为:" + ex.Message);
LogInfo.WriteLog(LogInfo.LogLevel.Error, ex.StackTrace + "<br>" + ex.Message + "<br>" + "引发该操作的位置是:" + this.GetType() + "btnQuery_Click()方法!");
return;
}
finally
{
btnQuery.IsEnabled = true;
}
#endregion
}
</span>
总结,以上就是真分页的具体实现方法了,整个过程下来也是比较容易的,我觉得比较复杂的也就算是那个委托的封装了,实现了委托,也就是实现了真分页……
<span style="font-family:Arial, Helvetica, sans-serif;font-size:24px;"><span style="white-space: normal;">
</span></span>