Click here to Skip to main content
15,124,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a search function where I can either fill in a value for exempel: registration number and get data about it, or if i don't enter any parameters, i will get all of the data, with 100 rows per page. Now, I have also created a sort of count where I can see how many rows there is in total, and how many pages this will be if every page has 100 rows. The problem is that I want to return the total count and total pages in the body of the request, so for example here's how a request answer can look:

[
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-20",
        "requestID": 118095,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 54",
        "workshop": "Workshop 55",
        "asignee": null,
        "claimId": "387385",
        "vinnumber": ""
    },
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-203",
        "requestID": 118094,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 10",
        "workshop": "Workshop 3",
        "asignee": null,
        "claimId": "387384",
        "vinnumber": ""
    }

]


And instead of adding 2 rows with total count and total pages in every answer i would like to get these just once, at the top of the request answer in body, so it could look something like this:

[
     {
      "totalcount" : "300",
      "totalpages" : "3"
      },
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-20",
        "requestID": 118095,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 54",
        "workshop": "Workshop 55",
        "asignee": null,
        "claimId": "387385",
        "vinnumber": ""
    },
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-203",
        "requestID": 118094,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 10",
        "workshop": "Workshop 3",
        "asignee": null,
        "claimId": "387384",
        "vinnumber": ""
    }

]


Here's the C# method for the search function:

public async Task<IEnumerable<GetRequestModelOut>> GetRequest2(GetRequestModel model, int pageNumber = 1)
       {
           var parameters = new DynamicParameters();

           parameters.Add("@RegNumber", model.RegNumber == string.Empty ? null :
           model.RegNumber);
           parameters.Add("@Vinnumber", model.Vinnumber == string.Empty ? null :
           model.Vinnumber);
           parameters.Add("@CountryID", model.CountryId == -1 ? (int?)null :
           model.CountryId);
           parameters.Add("@assigneid", model.AssigneId == string.Empty ? null :
           model.AssigneId);
           parameters.Add("@dateFrom", model.dateFrom);
           parameters.Add("@dateTo", model.dateTo);
           parameters.Add("@pageSize", model.pageSize);
           parameters.Add("@PageNumber", model.PageNumber);
           //parameters.Add("@totalCount", model.totalCount);
           //parameters.Add("@totalPages", model.totalPages);

           var getCount = await _sqlconnection.QueryAsync<int>($@"
           SELECT COUNT(Claim.ID)
           FROM Request
           INNER JOIN RequestCrossClaim ON Request.ID =
           RequestCrossClaim.RequestID
           INNER JOIN Claim ON RequestCrossClaim.ClamID = Claim.ID
           INNER JOIN Contract ON Request.ContractID = Contract.ID
           INNER JOIN Vehicle ON Contract.VehicleID = Vehicle.ID
           INNER JOIN Product ON Contract.ProductID = Product.ID
           INNER JOIN Retailer ON Contract.RetailerID = Retailer.ID
           INNER JOIN ClaimHandlingStatus ON Claim.ClaimHandlingStatusID =
           ClaimHandlingStatus.ID
           where (@RegNumber IS NULL OR RegNumber = @Regnumber)
           AND (@Vinnumber IS NULL OR Vinnumber = @Vinnumber)
           AND (@CountryID IS NULL OR Contract.CountryID = @CountryID)
           AND (@assigneid IS NULL OR Claim.CreatedBy = @assigneid)
           AND (@dateFrom IS NULL OR Request.CreatedDate BETWEEN @dateFrom AND
           @dateTo)
           ", parameters);

           model.totalCount = getCount.First();
           model.totalPages = (int)Math.Ceiling(model.totalCount /
           (double)model.pageSize);


           var getReq1 = await _sqlconnection.QueryAsync<GetRequestModelOut>($@"
           SELECT
           ClaimHandlingStatus.Name as ClaimHandlingStatus,
           Request.CreatedDate as requestDate,
           Request.ID as RequestID, Contract.CountryID,
           RegNumber, Product.Name as produkt,
           Retailer.Name as workshop, Claim.CreatedBy as UserID,
           Claim.ID as claimId,
           Vinnumber
           FROM Request
           INNER JOIN RequestCrossClaim ON Request.ID =
           RequestCrossClaim.RequestID
           INNER JOIN Claim ON RequestCrossClaim.ClamID = Claim.ID
           INNER JOIN Contract ON Request.ContractID = Contract.ID
           INNER JOIN Vehicle ON Contract.VehicleID = Vehicle.ID
           INNER JOIN Product ON Contract.ProductID = Product.ID
           INNER JOIN Retailer ON Contract.RetailerID = Retailer.ID
           INNER JOIN ClaimHandlingStatus ON Claim.ClaimHandlingStatusID =
           ClaimHandlingStatus.ID
           where (@RegNumber IS NULL OR RegNumber = @Regnumber)
           AND (@Vinnumber IS NULL OR Vinnumber = @Vinnumber)
           AND (@CountryID IS NULL OR Contract.CountryID = @CountryID)
           AND (@assigneid IS NULL OR Claim.CreatedBy = @assigneid)
           AND (@dateFrom IS NULL OR Request.CreatedDate BETWEEN @dateFrom
           AND @dateTo)
           ORDER BY requestDate desc OFFSET @pageSize * (@PageNumber-1)
           ROWS FETCH NEXT
           @pageSize ROWS ONLY",
           parameters);

           return getReq1;
       }


Controller:

[HttpGet]
        public async Task<IActionResult> GetRequest2([FromQuery] 
        GetRequestModel model, 
        int pageNumber)
        {

            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            try
            {
                var list = await _request.GetRequest2(model, pageNumber);
                /*var metadata = new
                {
                    model.totalCount,
                    model.totalPages
                };*/
                //Response.Headers.Add("X-Pagination", 
                //JsonConvert.SerializeObject(metadata));
                return Ok(list);
            }
            catch (Exception e)
            {
                return BadRequest(e.Message);
            }

            return Ok();
        }


Models:

public class GetRequestModelOut
    {
        public string ClaimHandlingStatus { get; set; }

        public DateTime RequestDate { get; set; }

        public int RequestID { get; set; }

        public int CountryID { get; set; }

        public string RegNumber { get; set; }

        public string Produkt { get; set; }

        public string Workshop { get; set; }

        public string Asignee { get; set; }

        public string claimId { get; set; }

        public string Vinnumber { get; set; }
        
    }


public class GetRequestModel
   {
       public int CountryId { get; set; } = -1;

       public string AssigneId { get; set; } = "";

       [StringLength(50)]
       public string RegNumber { get; set; } = "";

       [StringLength(50)]
       public string Vinnumber { get; set; } = "";

       public DateTime? dateFrom { get; set; } = null;

       public DateTime? dateTo { get; set; } = null;

       // Pagination information
       public int pageSize { get; set; } = 100;

       public int PageNumber { get; set; } = 1;

       public int totalCount { get; set; } = 0;

       public int totalPages { get; set; } = 0;
   }


I have no idea how to solve this without adding 2 parameters to the GetRequestModelOUT and adding totalCount and totalPages there, but then ofcouse every request answer will also get these 2 extra row.
So if anyone have any idea about how I could proceed or how to think about this i would be super grateful.

What I have tried:

I've tried creating new models to seperate totalcount and totalpages but it has not been successful.
Posted
Updated 21-Sep-21 23:47pm

1 solution

The simplest option would be to send the data as a custom response header. It looks like you've already tried that, but the code is commented out:
C#
var metadata = new
{
    model.totalCount,
    model.totalPages
};

Response.Headers.Add("X-Pagination", JsonConvert.SerializeObject(metadata));
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900