C# Grid Paging and ExtJS 4.1

Question

I am trying to implement paging toolbar on extjs grid pangel using c# backend... How do I start doing about that... I have tried using start and limit but I am not sure how it works. could someone please help me on how I can just send about 20 records per page in the grid, because my grid is getting all 500 datas, which is slowing my application as well below is my controller that sends json to my store.. 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();

     return Json(new {  myTable = items }, JsonRequestBehavior.AllowGet);
   }
}

and this is my store

this.store = Ext.create('Ext.data.JsonStore', {
        autoLoad: true,
        storeId: 'mystore1',
        pageSize: 20,
        fields: [
    { name: 'State' },
    { name: 'Capital' } 
    ],
        sorters: [
            {
                property: 'State',
                direct: 'ASC'
            }],
        scope: this,
        proxy: {
            type: 'ajax',
            scope: this,
            url: 'StateC/getData',
            autoLoad: { params: {
                start: 0,
                limit: 20
            }
            },
            reader: {
                type: 'json',
                root: 'myTable'
            }
        }
    });

Answer 1

I'm currently using MySQL & PHP so definitely error check my syntax on this, but the general logic is similar, however the syntax is a little more vague in MsSQL for the query. I would suggest this link for the a primer on the MsSQL recomended syntax:

http://www.codeguru.com/csharp/.net/net_data/article.php/c19611/Paging-in-SQL-Server-2005.htm

First you need to get the parameters that are passed by extjs on the ajax calls:

int limit = Request.QueryString["limit"];
int page= Request.QueryString["page"];
int upperBound = limit * page;
int lowerBound = limit * (page - 1) + 1;

I think that should work out to something like this for your query as well:

SELECT * FROM (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY person) AS rownum, 
            MYDBTABLE.State, 
            MYDBTABLE.Capital 
        FROM MYDBTABLE
    ) AS States
WHERE  States.rownum >= {lowerBound} AND States.rownum <= {upperBound}