如何在C#中实现SQL Server的分页查询

在C#中实现SQL Server的分页查询，可以使用以下几种方法：

1. 使用ROW_NUMBER()函数：

using System;
using System.Data.SqlClient;

namespace SqlServerPagination
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "your_connection_string";
            int pageNumber = 1;
            int pageSize = 10;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                string sql = $@"
                    SELECT * FROM (
                        SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
                        FROM YourTable
                    ) AS PagedTable
                    WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@PageNumber", pageNumber);
                    command.Parameters.AddWithValue("@PageSize", pageSize);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"Id: {reader["Id"]}, Name: {reader["Name"]}");
                        }
                    }
                }
            }
        }
    }
}

2. 使用OFFSET和FETCH子句（仅适用于SQL Server 2012及更高版本）：

using System;
using System.Data.SqlClient;

namespace SqlServerPagination
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "your_connection_string";
            int pageNumber = 1;
            int pageSize = 10;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                string sql = $@"
                    SELECT *
                    FROM YourTable
                    ORDER BY Id
                    OFFSET (@PageNumber - 1) * @PageSize ROWS
                    FETCH NEXT @PageSize ROWS ONLY";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@PageNumber", pageNumber);
                    command.Parameters.AddWithValue("@PageSize", pageSize);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"Id: {reader["Id"]}, Name: {reader["Name"]}");
                        }
                    }
                }
            }
        }
    }
}

请注意，将your_connection_string替换为你的SQL Server连接字符串，并将YourTable替换为你要查询的表名。这两个示例都使用了分页参数pageNumber和pageSize，你可以根据需要调整这些值。