Using Paging in handling the large amount of data

Question

We created the WebAPI for querying an Oracle database. The query returns results that are huge, so it sometimes throws OutOfMemoryException.Currently planning to use the paging so the huge amount of data is returned with out throwing any exception.Below is API controller we are having

public class PDataController : ApiController
  {
    public HttpResponseMessage Getdetails([FromUri] string[] id)
    {
        List<OracleParameter> prms = new List<OracleParameter>();
        string connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
        using (OracleConnection dbconn = new OracleConnection(connStr))
        {
            DataSet userDataset = new DataSet();
            var strQuery = @"SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN(";
             StringBuilder sb = new StringBuilder(strQuery);
             for(int x = 0; x < inconditions.Length; x++)
                 {
                   sb.Append(":p" + x + ",");
                   OracleParameter p = new OracleParameter(":p" + x, OracleType.Int32);
                   p.Value = inconditions[x];
                   prms.Add(p);
                 }
            if(sb.Length > 0) sb.Length--;
            strQuery = strQuery + sb.ToString() + ")";
            using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
              {
                 selectCommand.Parameters.AddRange(prms.ToArray());
                 using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
                {
                    DataTable selectResults = new DataTable();
                    adapter.Fill(selectResults);
                    var returnObject = new { data = selectResults };
                    var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
                    ContentDispositionHeaderValue contentDisposition = null;
                    if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
                    {
                        response.Content.Headers.ContentDisposition = contentDisposition;
                    }
                    return response;
                }
            }

        }
    }

I looking to use the paging concept in my above code. I am new to ASP. NET and C# programming, this concept is completely new to me. When I googled and found in other forums I see that using the Page Size, how would I know the maximum number of records as in the input we will be giving different IDs each time and each time it ll return diff number of rows.

Currently I am calling the API as

http://localhost:80/api/PData?id='A001'&id='A002'

If I am giving the pagesize how would I be adding the page in the input parameter.Can any one please help me with this.

First have a think about why you are sending such huge amounts of data over a web API. What is this data used for? I also see you have `SELECT *`. Do you really need every column?. Anyway on to paging. With paging, you pass the page number through in your parameter. (the first call would use 1). Your API uses this page number to build an appropriate SQL query (like from here: http://stackoverflow.com/questions/241622/paging-with-oracle) which only returns that page of data. The page number needs to be remembered in your calling application, i.e. next call will have page 2 in parameters — Nick.Mc, Aug 04 '16 at 04:21
I explicitly give coulmn names that are required in the Select Statement, sonce they are uge I just gave *. The data is so huge that sometimes it will be returning more that 1,000,000 amount of records. So in each page how many records can be pulled over — trx, Aug 04 '16 at 04:31
But what application uses 1million records over web API? If you are using web api for batch integration - you shouldn't. If you are populating a drop down in a web form - that's ridiculous. Anyway I don't know how many records can be pulled over - you'll have to experiment. — Nick.Mc, Aug 04 '16 at 05:33

score 2 · Accepted Answer · answered Aug 04 '16 at 04:55

In Web API 2.0 you can take advantage of ODATA queries.This allows you to automatically implement paging using the keywords top and skip:

In Visual Studio Package manager console run this command - Install-Package Microsoft.AspNet.Odata
Decorate your WEB API method with [EnableQuery]

If this was my api controller:

public class ValuesController : ApiController
{
    [EnableQuery]
    public IQueryable<string> Get()
    {
        return new string[] { "value1", "value2","values3","values4" }.AsQueryable<string>();
    }
}

To get the first two values I would use:

http://localhost:9658/api/values?$top=2

And to get the next two:

http://localhost:9658/api/values?$top=2&$skip=2

Using Paging in handling the large amount of data

1 Answers1