wpf中数据库表的翻页按钮的设计

最新推荐文章于 2023-06-18 08:15:00 发布

aidwen_chen

最新推荐文章于 2023-06-18 08:15:00 发布

阅读量1k

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库 wpf button sql string object

本文链接：https://blog.youkuaiyun.com/aidwen_chen/article/details/7315585

该博客介绍如何在WPF应用中设计数据库表的翻页功能。通过使用SQL语句，每页显示10条记录，点击按钮可以实现前后翻页，首页显示前10条，尾页显示最后10条记录。内容详细讲解了如何通过SQL实现自定义记录提取以达到翻页效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

要实现按键控制翻页功能，最重要的是数据在控件中的显示，例如：我定义在我的可视化界面的控件上要求每页显示10条记录，那么我的“首页”就提取数据库中的前10条记录并显示，而当我点击“下一页”时，控件上将会显示当前记录的后面10条记录，同理点击“上一页”时就会显示当前数据的前10条记录，“尾页”则显示数据库中表的最后10条记录。说白了，要想实现翻页首先得实现数据库记录自定义提取，用SQL语句便可实现，下面就具体讲一下如何实现：

1.在项目中先定义一个类Database，实现数据库的连接、查询、数据提取和显示的功能。源代码如下（至于数据库的连接自己设置）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Configuration;
namespace WpfApplication1
{
    public class Database
    {
        //数据库连接URL
        private static ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["test"];
        private static string url = settings.ConnectionString;
        public Database()
        {
        }
        /*
         * 执行更新操作（Insert, Update, Delete）, 返回受影响的行数
         * sql: SQL语句
         */
        public int Update(string sql)
        {
            int ret = 0;
            using (SqlConnection conn = new SqlConnection(url))
            {
                // Open the connection
                conn.Open();
                // Create Command Environment
                SqlCommand cmd = new SqlCommand(sql, conn);
                // Execute the SQL and return the count of rows which were influenced
                ret = cmd.ExecuteNonQuery();
                return ret;
            }
        }
        /*
         * 执行选择操作（Select）, 返回一个类型化的IDataReader
         * sql: SQL语句
         */
        public SqlDataReader Query(string sql)
        {
            SqlDataReader ret = null;
            using (SqlConnection conn = new SqlConnection(url))
            {
                // Open the connection
                conn.Open();
                // Create Command Environment
                SqlCommand cmd = new SqlCommand(sql, conn);
                // Execute the SQL and return the count of rows which were influenced
                ret = cmd.ExecuteReader();
                return ret;
            }
        }
        /*
         * 执行选择操作（Select）, 返回一个类型化的XMLReader
         * sql: SQL语句
         */
        public XmlReader QueryForXml(string sql)
        {
            XmlReader ret = null;
            using (SqlConnection conn = new SqlConnection(url))
            {
                // Open the connection
                conn.Open();
                // Create Command Environment
                SqlCommand cmd = new SqlCommand(sql, conn);
                // Execute the SQL and return the count of rows which were influenced
                ret = cmd.ExecuteXmlReader();
                return ret;
            }
        }
        /*
         * 执行选择操作（Select）, 返回一个DataTable
         * sql: SQL语句
         */
        public DataTable QueryForTable(string sql)
        {
            DataTable ret = null;
            using (SqlConnection conn = new SqlConnection(url))
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                ret = new DataTable();
                sda.Fill(ret);
                return ret;
            }
        }
        /*
         * 执行计数操作（Select count(*) from）, 返回行数
         * sql: SQL语句
         */
        public object Count(string sql)
        {
            object ret = null;
            using (SqlConnection conn = new SqlConnection(url))
            {
                // Open the connection
                conn.Open();
                // Create Command Environment
                SqlCommand cmd = new SqlCommand(sql, conn);
                // Execute the SQL and return the count of rows which were influenced
                ret = cmd.ExecuteScalar();
                return ret;
            }
        }
    }
}

2.在可视化编辑窗口中定义四个按键，分别为“首页”、“下一页”、“上一页”和“尾页”；然后绑定事件，点击按钮事件即执行，代码如下：

        </StackPanel>
        <StackPanel Grid.Row="1" Height="41" HorizontalAlignment="Center" Margin="12,276,0,0" Name="stackPanel1" VerticalAlignment="Top" Width="1000" Orientation="Horizontal">
            <Button Content="首  页"  Name="buttonFirst" Style="{StaticResource myButton}" Click="buttonFirst_Click" />
            <Button Content="下一页"  Name="buttonNext" Style="{StaticResource myButton}" Click="buttonNext_Click" />
            <Button Content="上一页"  Name="buttonPrev" Style="{StaticResource myButton}" Click="buttonPrev_Click" />
            <Button Content="尾  页"  Name="buttonLast" Style="{StaticResource myButton}" Click="buttonLast_Click" />
            <TextBlock Name="textBlock1" Style="{StaticResource myTextBlock1}" VerticalAlignment="Bottom" />
        </StackPanel>

对应事件的代码：

        private void buttonFirst_Click(object sender, RoutedEventArgs e)
        {
            if (currentPage != 1)
            {
                currentPage = 1;
                DataTable dt = db.QueryForTable("select top " + recordPerPage + " * from " + table + " where (ID<=" + recordPerPage + ")");
                this.listView1.ItemsSource = dt.DefaultView;
                this.textBlock1.Text = " 共" + totalRecords + "个 共" + totalPages + "页 第" + currentPage + "页 每页" + recordPerPage + "个";
            }
            else
            {
                string message = "对不起，已经是首页！";
                string title = "提示";
                MessageBoxButton button = MessageBoxButton.OK;
                MessageBoxImage img = MessageBoxImage.Warning;
                MessageBoxResult result = MessageBox.Show(message, title, button, img);
            }
        }
        private void buttonNext_Click(object sender, RoutedEventArgs e)
        {
            if (currentPage < totalPages)
            {
                currentPage++;
                DataTable dt = db.QueryForTable("select top " + recordPerPage + " * from " + table + "  where (ID not in (select top ( "+recordPerPage +" * " + (currentPage - 1) + ") ID from " + table + " ))");
                this.listView1.ItemsSource = dt.DefaultView;
                this.textBlock1.Text = " 共" + totalRecords + "个 共" + totalPages + "页 第" + currentPage + "页 每页" + recordPerPage + "个";
            }
            else
            {
                string message = "对不起，已经是尾页！";
                string title = "提示";
                MessageBoxButton button = MessageBoxButton.OK;
                MessageBoxImage img = MessageBoxImage.Warning;
                MessageBoxResult result = MessageBox.Show(message, title, button, img);
            }
        }
        private void buttonPrev_Click(object sender, RoutedEventArgs e)
        {
            if (currentPage > 1)
            {
                currentPage--;
                DataTable dt = db.QueryForTable("select top " + recordPerPage + " * from " + table + "  where (ID not in (select top ( " + recordPerPage + " * " + (currentPage - 1) + ") ID from " + table + " ))");
                this.listView1.ItemsSource = dt.DefaultView;
                this.textBlock1.Text = " 共" + totalRecords + "个 共" + totalPages + "页 第" + currentPage + "页 每页" + recordPerPage + "个";
            }
            else
            {
                string message = "对不起，已经是首页！";
                string title = "提示";
                MessageBoxButton button = MessageBoxButton.OK;
                MessageBoxImage img = MessageBoxImage.Warning;
                MessageBoxResult result = MessageBox.Show(message, title, button, img);
            }
        }
        private void buttonLast_Click(object sender, RoutedEventArgs e)
        {
            if (currentPage != totalPages)
            {
                currentPage = totalPages;
                DataTable dt = db.QueryForTable("select top " + recordPerPage + " * from " + table + "  where (ID not in (select top ( " + recordPerPage + " * " + (currentPage - 1) + ") ID from " + table + " ))");
                this.listView1.ItemsSource = dt.DefaultView;
                this.textBlock1.Text = " 共" + totalRecords + "个 共" + totalPages + "页 第" + currentPage + "页 每页" + recordPerPage + "个";
            }
            else 
            {
                string message = "对不起，已经是尾页！";
                string title = "提示";
                MessageBoxButton button = MessageBoxButton.OK;
                MessageBoxImage img = MessageBoxImage.Warning;
                MessageBoxResult result = MessageBox.Show(message, title, button, img);
            }
        }