How to use start and limit for paging

Question

I am trying to use paging with grid. The following method gives everything in the table to the grid. How do I account for start and limit where start is the page number and limit is the records per page. Basically extjs toolbar looks for my method to return start and limit on demand. I have tried so many solutions but just can't seem to work. That's why I am putting this out here in the simple way.

this is my c# end

public string myRecord(int start, int limit)
{
    List<gridPaging> result = new List<gridPaging>();
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices2"].ConnectionString))
    {
        SqlCommand cmd = con.CreateCommand();

        cmd.CommandText = "SELECT * FROM myTable ORDER BY Q1";
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            gridPaging gp = new gridPaging();

            gp.Column1 = reader["Column1"].ToString().Trim();
            gp.Column2 = reader["Column2"].ToString().Trim();
            gp.Column3 = reader["Column3"].ToString().Trim();
            gp.Column4 = reader["Column4"].ToString().Trim();
            result.Add(gp);
        }

        return JsonConvert.SerializeObject(result);
    }
}

Answer 1

In T-SQL, you have two built-ins that help you here; the first is the Row_Number function, which assigns a unique, increasing ordinal to each row of a result set as ordered by a special ORDER BY clause, and the second is the TOP keyword, which limits the maximum number of rows to be returned.

Basically, your query should look like this:

SELECT TOP @limit * FROM myTable WHERE (ROW_NUMBER() OVER (ORDER BY Q1)) > @limit * @start ORDER BY Q1

You then plug in values for the parameters @start and @limit from your C# code using Command.CreateParameter. For, say, the third page of results (using a zero-indexed start value of 2) with 15 results per page, this evaluates to the statement

SELECT TOP 15 * FROM myTable WHERE (ROW_NUMBER() OVER (ORDER BY Q1)) > 30 ORDER BY Q1

... which provides rows from the overall query from 31 to 45, the first two pages' queries having produced rows 1-15 and 16-30 respectively.