Supply current page and page size to data manager for JSON AJAX local data request

[Neil]

Hi,

How do you supply the currently selected page and page size and then apply them to a DataManager local JSON AJAX request for the data i.e. via a skip and take parameter?

so that the grid doesn't need to fetch the full data set initially?

[Madhu Sudhanan P]

Hi Neil,

 

Thanks for using Syncfusion products.

 

Normally when binding grid with the local data from a json array, the whole json data will be provided to the grid initially and no request will be made by datamanager.

 

But in remote url binding, when allowPaging is set as true in ejGrid, by default the datamanager will fetch the first 12 records. We can define the number of records to be fetched by setting the  pageSize property of the pageSettings as follow.

 

 

$("#Grid").ejGrid({

                dataSource: ej.DataManager({ url:"http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/"}),

                . . . . . . .

                allowPaging: true,

                pageSettings: { pageSize: 10 }, //Setting the pageSize as 10 and hence first 10 records will be requested

              

            });

 

 

When using the above code snippet, the request send will be as follows.

By default, with allowPaging set as true, the request made will be as follows.

 

When the Offline property of the ej.DataManager is set to true, the data will be fetched from the server only once; further actions will be performed on the client side. No further request will be made to the server.

We can set the offline property of the datamanager as follow.

 

 

$("#Grid1").ejGrid({

                dataSource: ej.DataManager({

url:"http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/",

offline: true

}),

                . . . . . . .

              

            });

 

 

The request made when the offline property is set as true is as follows,

 

Please let us know if we misunderstood you requirement and provide us clarification regarding the highlighted section in the following query.

 

“then apply them to a DataManager local JSON AJAX request for the data i.e. via a skip and take parameter?”

 

The information provided would be more helpful for us to analyze the requirement and provide solution as early as possible.

 

Regards,

Madhu Sudhanan. P

[Neil]

Ok that clarifies things a bit, thanks for the reply.

The querystring parameters of "$top" and "$skip" are what I can use.

I use ASP.NET MVC, which usually model binds the querystring parameters.. but because the $ is not valid in controller actions I will have to do this sort of thing below:

public ActionResult GetData([Bind(Prefix = "$top")]int top = 0, [Bind(Prefix = "$skip")]int skip = 0)

What I would still like to know is when not using local data and requesting only part of the data result.. where can I set a separate request to calculate the correct "Total number of records"?

i.e. if I have 1000 results, but only 50 results have been requested for page 1.. is there any support to make a separate data request for a total count of records i.e. doing SELECT COUNT(*) FROM [MyTable] and returning the count so that it can be correctly displayed in the grid?

because obviously its not correct if the grid paging information shows "page 1 of 20 (50 items)" if the number in the brackets is meant to represent the full data.

[Madhu Sudhanan P]

Hi Neil,

 

Thanks for your valuable update.

 

To perform paging in grid  properly, we should pass the records that we have fetched from the database along with total number of records in the database. For your convenience, we have created a simple MVC sample in which the grid will be loaded with the data from the action “/Grid/GetData” and the same can be downloaded from the below location.

 

Sample Location: MVCUsingSQLConnection.zip

 

The Action “GetData” will be as follow.

 

 

public JsonResult GetData([Bind(Prefix = "$top")]int top = 24, [Bind(Prefix = "$skip")]int skip = 0)

        {

            List<OrderView> list = new List<OrderView>();

            int count = 0;

            string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString();

            using (SqlConnection cn = new SqlConnection(connectionStr))

            {

                cn.Open();

 

                //Getting the records based on skip and top value.

                SqlCommand cmd = new SqlCommand("SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) as row FROM Orders) a  WHERE row > " + skip + " and row <=" + (top + skip) + "", cn);

               

               using (SqlDataReader dr = cmd.ExecuteReader())

                {

                    while (dr.Read())

                        list.Add(new OrderView() { OrderID = (int)dr.GetValue(0), CustomerID = (string)dr.GetValue(1), EmployeeID =              (int)dr.GetValue(2) });

                }

 

                //Get the total record count

                SqlCommand cmd1 = new SqlCommand("SELECT COUNT(*) FROM Orders", cn);

                using (SqlDataReader dr1 = cmd1.ExecuteReader())

                {

                    while (dr1.Read())

                        count = (int)dr1.GetValue(0);

                }

 

                cn.Close();

            }

            return Json(new { result = list, count= count  }, JsonRequestBehavior.AllowGet); //result – records from DB, count-total records in DB

        }

 

 

In the above code snippet, you can see that in the result, we have passed the record (total 10 records) along with the total records count (count = 830 in our sample) in the count to the client.

 

The returned json from the server will be as follow.

 

When performing paging, based on the “$skip” and “$top” value the corresponding records and the total records count will be returned.

 

Please let us know if you have any queries.

 

Regards,

Madhu Sudhanan. P

[Neil]

Thank you, your sample was very helpful. 

The key point obviously being what you highlighted and that the ej.DataManager can consume a JSON object with a result and count.

Thanks again Madhu

[Madhu Sudhanan P]

Hi Neil,

Thanks for your appreciation.

Yes, ejDataManager can consume JSON object with result and count.

Please get back to us if you require further assistance.

Regards,
Madhu Sudhanan. P

[Pankaj]

AS you said  ejDataManager can consume JSON object with result and count but when I used following line of code,

            JSONString = JsonConvert.SerializeObject(dt); //dt is my datatable

And when I return it in following way.

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

It's not work.So please suggest me any solution.The objective of doing like this is the column of grid are not fixed.

[Balaji Marimuthu]

Hi Neil, 

 

We need to bind the dataSource to Grid in form of object (the result data should be in form of array of objects). In following code example we have converted the datatable to IEnumerable and please refer to the below code example. 

 

 

public JsonResult GetData([Bind(Prefix = "$top")]int top = 24, [Bind(Prefix = "$skip")]int skip = 0) 

        { 

            DataTable dt = new DataTable(); 

            int count = 0; 

 

            string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString(); 

            using (SqlConnection cn = new SqlConnection(connectionStr)) 

            { 

                cn.Open(); 

 

                //Getting the records based on skip and top value. 

                using (SqlDataAdapter da = new SqlDataAdapter("SELECT OrderID, CustomerID, EmployeeID FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) as row FROM Orders) a WHERE row > " + skip + " and row <=" + (top + skip) + "", cn)) 

                { 

                    da.Fill(dt); 

                } 

 

                cn.Close(); 

            } 

            var data = DataTableToJson(dt);  //get data 

            return Json(new { result = data, count = count }, JsonRequestBehavior.AllowGet); //result - records, count-total records from database 

        } 

 

        public static IEnumerable<Dictionary<string, object>> DataTableToJson(DataTable dt) 

        { 

            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>(); 

            Dictionary<string, object> row = null; 

 

            foreach (DataRow dr in dt.Rows) 

            { 

                row = new Dictionary<string, object>(); 

                foreach (DataColumn col in dt.Columns) 

                { 

                    row.Add(col.ColumnName.Trim(), dr[col]); 

                } 

                rows.Add(row); 

            } 

            return rows; 

        } 

 

Regards, 

Balaji Marimuthu