C#实现SqlServer数据表分页查询

在系统开发中，如果数据库中某张数据表的数据量较大，我们一般会进行分页查询。SQL Server中的分页查询较为常用的是利用ROW_NUMBER()进行分页，从SQL Server 2012开始，又新增了一种fetch方法进行分页，下面给出这两种分页方法的具体实现代码。在测试数据库中有一张道路名称数据表——TRoad，其中包含了多条数据，如下图：

方法一：pageIndex表示第几页，pageNumber表示一页多少行

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private static string ConnectionString = "Data Source=LENOVO-PC;Initial Catalog=Test;User ID=sa;Password=123456";

        public Form1()
        {
            InitializeComponent();
        }
        
        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = MethodOne(1, 10);
        }

        // 方法一
        private DataTable MethodOne(int pageIndex, int pageNumber)
        {
            string sql = "select * from (select ROW_NUMBER() OVER(Order by Id) as RowId,* from TRoad) as b where b.RowId BETWEEN (@pageIndex-1)*@pageNumber+1 and @pageIndex*@pageNumber order by Id";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@pageIndex", pageIndex),
                    new SqlParameter("@PageNumber", pageNumber)
                };
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnectionString))
            {
                adapter.SelectCommand.Parameters.AddRange(parameters);
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                adapter.SelectCommand.Parameters.Clear();
                return dataTable;
            }
        }
    }
}

运行结果如下所示：

方法二：pageIndex表示第几页，pageNumber表示一页多少行

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private static string ConnectionString = "Data Source=LENOVO-PC;Initial Catalog=Test;User ID=sa;Password=123456";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = MethodTwo(2, 10);
        }

        // 方法二
        private DataTable MethodTwo(int pageIndex, int pageNumber)
        {
            string sql = "select * from [TRoad] order by Id offset (@pageIndex-1)*@pageNumber rows fetch next @pageNumber rows only";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@pageIndex", pageIndex),
                    new SqlParameter("@PageNumber", pageNumber)
                };
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnectionString))
            {
                adapter.SelectCommand.Parameters.AddRange(parameters);
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                adapter.SelectCommand.Parameters.Clear();
                return dataTable;
            }
        }
    }
}

运行结果如下所示：

其实我个人比较喜欢第二种方法，简洁明了，据说效率比方法一略高一些~如果项目服务器的数据库还是08版的sqlserver，那还是用方法一比较好。