Pagination on WCF Service with JSON

Question

I've made a WCF Service that sends a JSON string to some client. I've been requested to make some kinda pagination so it doesn't just send all the data at once, but I'm unsure on how best to go about this. The stuff I've read online so far seems either pretty vague or not really applicable to my specific application, atleast not in a way I understand.

Here's how my WCF works:

Got an IService1:

        [OperationContract()]
    [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare, RequestFormat = WebMessageFormat.Json, UriTemplate = "/BP?vCaseNr={vCaseNr}")]
    Stream BoligPraesentation(string vCaseNr);

Service1:

public Stream BoligPraesentation(string vSagsNr)
{
 SqlConnection con = new SqlConnection();
 con = Database();

 SqlDataAdapter dau = new SqlDataAdapter(*long sql here*, con);

 DataTable dtu = new DataTable();
 dau.Fill(dtu);
 string json = GetJson(dtu)

            if (json != "[]")
            {
                json = json.Replace("[", "");
                json = json.Replace("]", "");
                json = "{\"Things\": [" + json + "]}";
            }

 WebOperationContext.Current.OutgoingResponse.ContentType = 
 "application/json; charset=utf-8";
 return new MemoryStream(Encoding.UTF8.GetBytes(json));
}

The GetJSon looks like this:

private string GetJson(DataTable dt)
    {
        System.Web.Script.Serialization.JavaScriptSerializer Jserializer = new System.Web.Script.Serialization.JavaScriptSerializer();
        List<Dictionary<string, object>> rowsList = 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, dr[col]);
            }

            rowsList.Add(row);
        }

        return Jserializer.Serialize(rowsList);
    }

The JSON looks kinda like this after it's been returned:

{"Things": [{"CaseNr":6,"Titel":"some place V","Areal":null,"Adresse1":"Hsome address","Rum":2,"Etage":0,"PostNr":"9999","ByNavn":"city","EjendomNavn":"name here","Status":"LEDIG","Leje":"343434","AntalBilleder":0,"AntalProspekter":0,"AntalKort":0,"AntalTegninger":0},{"CaseNr":4,"Titel":"big place","Areal":null,"Adresse1":"adress 555","Rum":null,"Etage":3,"PostNr":"2222","ByNavn":"Germacity","EjendomNavn":"HSbuilding ApS","Status":"OPTAGET","Leje":"0.00","AntalBilleder":0,"AntalProspekter":0,"AntalKort":0,"AntalTegninger":0}

Instead of sending all Things over at the same time(there can be way more than the two I showed) I would like to send 20 at a time, or perhaps let the client decide how many. I'm not at all sure how to make this work though, any advice?

PS: Please say if I need to show more info.

score 1 · Accepted Answer · answered Jun 20 '18 at 08:40

Here are the generic steps that you need to follow for pagination.

Get the pageNumber and pageSize (indicates the number of objects in a single page, 20 in your case) in the request that the client would make.
In your *long sql* use these numbers to filter out the results and hence get the page. An example of this using LINQ would look like : var requestedPage = dbEntities.Skip(pageNumber*pageSize).Take(pageSize)
Convert such a page into JSON or your required format (stream?) and return.

It is also a good idea to send back the pageNumber, pageSize and total (number of all entities in your table), along with the page. This will simply the logic to request next, previous or any page for the client.

Here are more variations of the what I mentioned above LINQ and pagination

score 0 · Answer 2 · answered Jun 26 '18 at 10:25

Thank you Bit, I've got it now.

Basically I have two SQL queries. One that gets meta data like this:

declare @pageindex int select @pageindex = 2
declare @pagerows int select @pagerows = 10

select count(distinct u.Id) as NumberofRows, @pagerows as PageRows, @pageindex as PageIndex 
from SomeTable u
inner join ...
where ...

And my big one with this added:

declare @pageindex int select @pageindex = 2
declare @pagerows int select @pagerows = 10

select * from(select ROW_NUMBER() over(order by u.Id) Row, *other selects*
inner join...
where...
) a where Row between(@pageRows* @pageindex) -@pageRows + 1 and(@pageRows * @pageindex)

Then I just add the two jsons together like so:

                json = "{\"Meta\":" + GetJson(dtrows);
                json = json.Replace("[", "");
                json = json.Replace("]", "");

                json += ", \"Things\":" + GetJson(dtu) + "}";

Pagination on WCF Service with JSON

2 Answers2