We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to perform custom paging with stored procedure on Grid

Platform: ASP.NET MVC |
Control: Grid |
Published Date: September 15, 2017 |
Last Revised Date: September 15, 2017


This knowledge base document explains the way of how to perform custom paging with stored procedure.

  1. Render the Grid with Adaptor.



        .Datasource(ds=> ds.URL("DataSource").Adaptor(AdaptorType.UrlAdaptor))


        .PageSettings(p=> p.PageSize(10).CurrentPage(3))

        .Columns(col =>










  1. Create Stored Procedure to perform custom paging


CREATE PROCEDURE [dbo].[CustomPaging]

                @SkipCount int,

                @PageSize int,

                @TotalRows int output




                SELECT @TotalRows = COUNT(*)

    FROM Orders


                SELECT * FROM Orders ORDER BY OrderID OFFSET (@SkipCount) ROWS FETCH NEXT @PageSize ROWS ONLY






  1. Pass the skip and take to the stored procedure from the controller.


public ActionResult DataSource(DataManager dm)


            DataOperations operation = new DataOperations();

            int count;

            DataTable dt = new DataTable("Order");

            string constring = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString;

            using (SqlConnection con = new SqlConnection(constring))


                using (SqlCommand cmd = new SqlCommand("CustomPaging", con))


                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@SkipCount", dm.Skip);

                    cmd.Parameters.AddWithValue("@PageSize", dm.Take);

                    cmd.Parameters.Add("@TotalRows", SqlDbType.Int, 4);

                    cmd.Parameters["@TotalRows"].Direction = ParameterDirection.Output;


                    SqlDataReader dr = cmd.ExecuteReader();

                    dt.Load(dr); // Load into the dataTable



                    count = Convert.ToInt32(cmd.Parameters["@TotalRows"].Value);



            //Convet the dataTable into list

            List<EditableOrder> resList = new List<EditableOrder>();

            resList = (from DataRow dr in dt.Rows

                      select new EditableOrder()


                             OrderID = Convert.ToInt32(dr["OrderID"]),

                             CustomerID = dr["CustomerID"].ToString(),

                             EmployeeID = Convert.ToInt32(dr["EmployeeID"]),

                             Freight = Convert.ToDecimal(dr["Freight"]),

                             ShipCity = dr["ShipCity"].ToString()



            return Json(new { result = resList, count = count }, JsonRequestBehavior.AllowGet);



The following screenshot display the custom paging request and response.


Figure 1: Request header


Figure 2: Response header


Figure 3: Output


You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon