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.

 

@(Html.EJ().Grid<object>("Editing")

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

        .AllowPaging()

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

        .Columns(col =>

        {

            col.Field("OrderID").Add();

            col.Field("CustomerID").Add();

            col.Field("Freight").Add();

            col.Field("EmployeeID").Add();

        })

)

 

 

  1. Create Stored Procedure to perform custom paging

 

CREATE PROCEDURE [dbo].[CustomPaging]

                @SkipCount int,

                @PageSize int,

                @TotalRows int output

AS

BEGIN

SET NOCOUNT ON;

                SELECT @TotalRows = COUNT(*)

    FROM Orders

 

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

 

END

               

RETURN 0

 

  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;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

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

                    dr.Close();

                    con.Close();

                    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()

                             

                         }).ToList();

            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

 

ADD COMMENT
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