数据库之 ：分页技术：之点击一次取一次数据
前台xaml文件
<Window x:Class="Supplier.UserI" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:local="clr-namespace:Supplier" Title="查看用户" Height="500" Width="440" Loaded="Window_Loaded" WindowStartupLocation="CenterScreen"> <Grid > <Grid.Resources> <local:TypeConvert x:Key="typeConvert"/> <DataTemplate x:Key="oprate" DataType="User"> <StackPanel Orientation="Horizontal"> <Button Tag="{Binding}" Click="updateUser" Content="修改" Background="Gray" Width="40" Foreground="White"/> <Button Tag="{Binding}" Click="deleteUser" Content="删除" Background="red" Width="40" Foreground="White"/> </StackPanel> </DataTemplate> <!--<DataTemplate x:Key="permis" DataType="User" > <ComboBox x:Name="permission" SelectedIndex="{Binding Type}" > <TextBlock Text="所有操作" /> <TextBlock Text="查看" Margin="10,0,0,0" /> </ComboBox> </DataTemplate>--> </Grid.Resources> <Grid.RowDefinitions> <RowDefinition Height="50"/> <RowDefinition Height="*"/> <RowDefinition Height="100"/> </Grid.RowDefinitions> <TextBlock Grid.Row="0" Text="用户管理" VerticalAlignment="Center" HorizontalAlignment="Center" FontSize="18" FontStyle="Normal" Foreground="Blue"/> <Button Click="addUser" Grid.Row="0" Content="添加" HorizontalAlignment="Right" VerticalAlignment="Center" Margin="0,0,10,0" Background="SkyBlue" Width="50" Foreground="White"/> <!--Beige--> <ListView x:Name="grid" Grid.Row="1" Margin="10,0,10,10"> <ListView.View> <GridView> <GridViewColumn Header="序号" Width="60" DisplayMemberBinding="{Binding Number}"/> <GridViewColumn Header="用户名" Width="80" DisplayMemberBinding="{Binding UserName}"/> <GridViewColumn Header="权限" Width="110" DisplayMemberBinding="{Binding Type,Converter={StaticResource typeConvert}}"/> <GridViewColumn Header="操作" Width="120" CellTemplate="{StaticResource oprate}"/> </GridView> </ListView.View> </ListView> <Grid Grid.Row="2"> <Button Content="上一页" Height="23" HorizontalAlignment="Left" Margin="37,0,0,0" Name="button7" VerticalAlignment="Top" Width="65" Click="PreviousPage" /> <Button Content="下一页" Height="23" HorizontalAlignment="Left" Margin="108,0,0,0" Name="button8" VerticalAlignment="Top" Width="59" Click="NextPage" /> <Label Content="转到" Height="28" HorizontalAlignment="Left" Margin="173,0,0,0" Name="label6" VerticalAlignment="Top" /> <TextBox KeyDown="textBox1_KeyDown" Height="23" HorizontalAlignment="Left" Margin="212,0,0,0" Name="textBox1" VerticalAlignment="Top" Width="53" /> <Button Content="GO" Height="23" HorizontalAlignment="Left" Margin="298,0,0,0" Name="button9" VerticalAlignment="Top" Width="96" Click="JumpPage" /> <Label Content="页" Height="28" HorizontalAlignment="Left" Margin="269,0,0,0" Name="label7" VerticalAlignment="Top" /> <Label Height="28" HorizontalAlignment="Left" Margin="120,40,0,0" Name="label8" VerticalAlignment="Top" Content="【第" /> <Label Content="1" Height="28" HorizontalAlignment="Left" Margin="157,40,0,0" Name="label1" VerticalAlignment="Top" /> <Label Content="页】" Height="28" HorizontalAlignment="Left" Margin="176,40,0,0" Name="label10" VerticalAlignment="Top" /> <Label Content="【共" Height="28" HorizontalAlignment="Left" Margin="242,40,0,0" Name="label11" VerticalAlignment="Top" /> <Label Content="1" Height="28" HorizontalAlignment="Left" Margin="280,40,0,0" Name="label2" VerticalAlignment="Top" /> <Label Content="页】" Height="28" HorizontalAlignment="Left" Margin="298,40,0,0" Name="label13" VerticalAlignment="Top" /> </Grid> </Grid> </Window>
后台
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Shapes; using System.Data; namespace Supplier { /// <summary> /// Edit.xaml 的交互逻辑 /// </summary> public partial class UserI : Window { public int pageSize = Global.PageSize; //每页记录数 private int recordsum = 0; //表中记录总数 private int PageSum = 0; //总页数 private int CurrentPage = 1; //当前页 private List<User> CurrentList; public UserI() { InitializeComponent(); this.Topmost = true;//顶置窗口 } private void Window_Loaded(object sender, RoutedEventArgs e) { //pageIndex 当前页的序号 recordsum = UserManager.getCount(); //判断总页数 if (recordsum % pageSize == 0) PageSum = recordsum / pageSize; else PageSum = recordsum / pageSize + 1; DataGridBind(CurrentPage); } #region 用户操作 private void updateUser(object sender, RoutedEventArgs e) { UserEdit userEdit = new UserEdit(); Button bt = e.OriginalSource as Button; User u = bt.Tag as User; userEdit.user = u; userEdit.hasType = true; bool? result = userEdit.ShowDialog(); if (result != null && (bool)result) { Window_Loaded(sender, e); DataGridBind(CurrentPage); } } private void deleteUser(object sender, RoutedEventArgs e) { if (MessageBox.Show("您确定要删除该用户吗？", "系统提示", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes) { UserEdit userEdit = new UserEdit(); Button bt = e.OriginalSource as Button; User u = bt.Tag as User; UserManager.delete(u.ID); Window_Loaded(sender, e); DataGridBind(CurrentPage); } } private void addUser(object sender, RoutedEventArgs e) { UserAdd userAdd = new UserAdd(); userAdd.hasType = true; bool? result = userAdd.ShowDialog(); if (result != null && (bool)result) { Window_Loaded(sender, e); DataGridBind(PageSum); } } #endregion #region 分页 private void DataGridBind(int pageIndex) { if (pageIndex > 0) { CurrentList = UserManager.getList((pageIndex - 1) * pageSize, pageSize); this.grid.ItemsSource = CurrentList; CurrentPage = pageIndex; label1.Content = CurrentPage; label2.Content = PageSum; } } /// <summary> /// 上一页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void PreviousPage(object sender, RoutedEventArgs e) { if (CurrentPage > 1) DataGridBind(CurrentPage - 1); } /// <summary> /// 下一页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void NextPage(object sender, RoutedEventArgs e) { if (CurrentPage < PageSum) DataGridBind(CurrentPage + 1); } /// <summary> /// 跳转页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void JumpPage(object sender, RoutedEventArgs e) { int page = 0; if (int.TryParse(textBox1.Text, out page)) { if(page<=PageSum) DataGridBind(page); else { MessageBox.Show("对不起，您输入的页面超过当前最大页数", "提示", MessageBoxButton.OK, MessageBoxImage.Information); this.textBox1.Clear(); } } } private void textBox1_KeyDown(object sender, KeyEventArgs e) { if (e.Key == Key.Enter) JumpPage(sender, e); } #endregion } }
数据操作model 层
/// <summary> /// 获取第skipNum到（skipNum+pageSize）内的记录 /// </summary> /// <param name="skipNum"></param> /// <param name="pageSize"></param> /// <returns></returns> public static List<User> getList(int skipNum, int pageSize) { string sql = "select top " + pageSize + " Number= row_number() over(order by id asc), * from Users where id not in " + "(select top " + skipNum + " id from Users order by id asc) order by id asc"; DataTable dataTable = DbConHelper.ExecuteDataTable(sql); List<User> userList = new List<User>(); User userModel; if (dataTable.Rows.Count > 0) { int page = skipNum / pageSize+1; foreach (DataRow row in dataTable.Rows) { userModel = new User() { ID = row["ID"].ToString().Trim(), Number = (int.Parse(row["Number"].ToString().Trim()) * page).ToString(), Password = row["Password"].ToString().Trim(), Type = row["Type"].ToString().Trim(), UserName = row["UserName"].ToString().Trim() }; userList.Add(userModel); } } return userList; } public static int getCount() { string sql = "select count(*) from Users"; return (int)DbConHelper.ExecuteScalar(sql); }
