c# - 带分页的 SQL Select 语句 c#

Question

在 C# Controller 上为分页编写选择语句的正确方法是什么。这是我想出的最好的办法，但我知道它不起作用，因为它在网格的第一页上显示了所有数据...请帮忙

public JsonResult getData(int start, int limit)
{
  List<MyItem> items = new List<MyItem>();
  using (SqlConnection con = new       SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices1"].ConnectionString))
 {
 SqlCommand cmd = con.CreateCommand();
 cmd.CommandText = "SELECT State, Capital FROM MYDBTABLE";
 con.Open();
 SqlDataReader reader = cmd.ExecuteReader();
 while (reader.Read())
 {
   MyItem item = new MyItem();
   item.State = reader[0].ToString(); 
   item.Capital = reader[1].ToString(); 
   items.Add(item);
 }
 con.Close();

 if ((start + limit) > Myitem.Count)
 {
    limit = Myitem.Count - start;
 }
 return Json(new {  myTable = items }, JsonRequestBehavior.AllowGet);
 }
}

Answer 1

这是我喜欢用于分页的存储过程模板。

CREATE PROCEDURE [dbo].[StoredProcName] 
    @page_size INT
    , @page_num INT
AS
BEGIN

    SET NOCOUNT ON;

    ; WITH RESULTS AS
    (
        SELECT *
            , ROW_NUMBER() OVER (ORDER BY <order_col> DESC) AS rn
            , ROW_NUMBER() OVER (ORDER BY <order_col> ASC) AS rn_reversed
        FROM <table>
    )
    SELECT *
        , CAST(rn + rn_reversed - 1 AS INT) AS total_rows
        , CAST(CASE (rn + rn_reversed - 1) % @page_size
            WHEN 0 THEN (rn + rn_reversed - 1) / @page_size
            ELSE ((rn + rn_reversed - 1) / @page_size) + 1 
            END AS INT) AS total_pages
    FROM RESULTS a
    WHERE a.rn BETWEEN 1 + ((@page_num - 1) * @page_size) AND @page_num * @page_size
    ORDER BY rn ASC 

END

您只需将 page_size 和 page_num 传递给存储过程就可以了。