CODEWITHSUNDEEP
c#sqlsql-server-2008selectpagination
EagleFox
EagleFox

Reputation: 1367

Sql Select Statement with Paging c#

What is the correct way of writing a select statment on c# controller for paging. This is the best I came up with, but I know it doesn't work because it's showing all data on my first page on the grid... please help

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);
 }
}

Upvotes: 1

Views: 7984

Answers (3)

Jack Hughes
Jack Hughes

Reputation: 206

You could use Linq and use Skip() and Take() like this:

public JsonResult getData(int page, int limit)
{
    DataTable dt = new DataTable();
    using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices1"].ConnectionString))
    {
        using (SqlCommand cmd = cnn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT State, Capital FROM MYDBTABLE";
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
    }
    int start = (page * limit) - limit;
    List<MyItem> items  = (from DataRow row in dt.Rows
                           select new MyItem
                           {
                                State = row["State"].ToString(),
                                Capital = row["Capital"].ToString()
                           }).Skip(start - 1).Take(limit).ToList();
    return Json(new {  myTable = items }, JsonRequestBehavior.AllowGet);
}

Upvotes: 3

KeithS
KeithS

Reputation: 71573

The basic template I use is:

SELECT ROW_NUMBER(), <Rest of your columns>
FROM <Your Tables/Joins>
WHERE ROW_NUMBER() >= (@PageNum * @RowsPerPage)
AND ROW_NUMBER() < (@PageNum+1 * @RowsPerPage)

Upvotes: 0

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Here's the template for stored procs that I like to use for paging.

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

You just need to pass in page_size and page_num to the stored proc and you're good to go.

Upvotes: 5

Related Questions