Paging in SOA Setup

[Shafi]

Hi!

I have my solution setup as follows (image also attached):

Data <--> Business <--> WCF Service <--> Intranet <--> WCF Client <--> Website

I have attempted to implement paging but it is not the most optimal way. Here is the code that starts with the website and ends in the Data Layer:

View:

@(Html.Kendo().Grid(Model.SearchResults)
    .Name("GridSearchResults")
    .HtmlAttributes(new { style = "height: 400px" })
    .EnableCustomBinding(true)
    .Columns(columns =>
    {
        columns.Bound(p => p.ParkingCardId).Title("Id").Width(80).ClientTemplate("<a href='" + Url.Action("ParkingCardDetail", "CardRequest", new { parkingCardId = "#= ParkingCardId #'" }) + ">#= ParkingCardId #</a>");
        columns.Bound(p => p.Name).Title("Name");
        columns.Bound(p => p.Company).Title("Company");
        columns.Bound(p => p.CardType).Title("Card Type").Width(100);
        columns.Bound(p => p.DrivingLicenseNo).Title("License #");
        columns.Bound(p => p.SeasonPassNo).Title("Season Pass #");
        columns.Bound(p => p.Status).Title("Status");
        columns.Bound(p => p.ReceiptNo).Title("Receipt #").Width(140).ClientTemplate("<a href='" + Url.Action("Report1", "Ajax", new { billNo = "#= ReceiptNo #'" }) + ">#= ReceiptNo #</a>");
    })
    .Pageable()
    .Scrollable()
    .DataSource(dataSource => dataSource
        .Ajax()
        .PageSize(30)
        .Read(read => read
            .Action("IndexResults", "CardRequest")
            .Data("searchExtraParameters")
        )
    )
)

Controller:

public async Task<ActionResult> Index()
{
    ViewBag.Module = (int)PageModule.SearchCardRequests;
    await GetMasterLookForRequest(lookupManager, billingManager);

    return View(new ParkingCardSearchViewModel());
}

public async Task<ActionResult> IndexResults([DataSourceRequest(Prefix = "Grid")]  DataSourceRequest request, int? companyId, int? categoryId, int? serviceId, int? paymentModeId, int? statusId, int? extraSearch, string extraSearchKeywords)
{
    ViewBag.Module = (int)PageModule.SearchCardRequests;

    #region Apply paging

    if (request.PageSize == 0)
    {
        request.PageSize = 30;
    }

    var pager = new PagingHelper { Skip = 0, Take = request.PageSize };

    if (request.Page > 0)
    {
        pager.Skip = (request.Page - 1) * request.PageSize;
    }

    #endregion

    // Get results
    var results = await searchManager.GetCardRequestForSearch(pager, companyId, categoryId, serviceId, paymentModeId, statusId, extraSearch, extraSearchKeywords);

    var result = new DataSourceResult()
    {
        Data = results.Item2, // Process data (paging and sorting applied)
        Total = results.Item1 // Total number of records
    };

    //Return the result as JSON.
    return Json(result);
}

Client:

public async Task<Tuple<int, IList<SearchCardRequest>>> GetCardRequestForSearch(PagingHelper pager, int? companyId, int? categoryId, int? serviceId, int? paymentMode, int? statusId, int? extraSearchId, string searchKeywords)
{
    try
    {
        var result = await proxy.CallAsync(m => m.GetCardRequestForSearch(pager, companyId, categoryId, serviceId, paymentMode, statusId, extraSearchId, searchKeywords));
        return result;
    }
    catch (FaultException<CarParkFault> fault)
    {
        throw new ClientException("Exception from server for GetCardRequestForSearch: " + fault.Message);
    }
    catch (Exception exp)
    {
        throw new ClientException("General exception from server for GetCardRequestForSearch: " + exp.Message);
    }
}

Service:

[OperationContract]
[FaultContract(typeof(CarParkFault))]
Tuple<int, IList<BDO.SearchCardRequest>> GetCardRequestForSearch(BDO.PagingHelper pager, int? companyId, int? categoryId, int? serviceId, int? paymentMode, int? statusId, int? extraSearchId, string searchKeywords);

public Tuple<int, IList<BDO.SearchCardRequest>> GetCardRequestForSearch(BDO.PagingHelper pager, int? companyId, int? categoryId, int? serviceId, int? paymentMode, int? statusId, int? extraSearchId, string searchKeywords) {
    Tuple<int, IList<BDO.SearchCardRequest>> objMaster;

    try
    {
        using (var context = new BLL.SearchLogic())
        {
            objMaster = context.GetCardRequestForSearch(pager, companyId, categoryId, serviceId, paymentMode, statusId, extraSearchId, searchKeywords);
        }
    }
    catch (Exception ex)
    {
        throw new FaultException<CarParkFault>(
            new CarParkFault { Message = ex.Message, Result = false, Description = ex.ToString() },
            new FaultReason("Service failed to serve GetCardRequestForSearch()")
        );
    }

    return objMaster;
}

Business:

public Tuple<int, IList<BDO.SearchCardRequest>> GetCardRequestForSearch(BDO.PagingHelper pager, int? companyId, int? categoryId, int? serviceId, int? paymentMode, int? statusId, int? extraSearchId, string searchKeywords)
{
    Tuple<int, IList<BDO.SearchCardRequest>> responseObject;

    try
    {
        responseObject = searchRepo.GetCardRequestForSearch(pager, companyId, categoryId, serviceId, paymentMode, statusId, extraSearchId, searchKeywords);
    }
    catch (Exception exp)
    {
        throw new BusinessLayerException("GetCardRequestForSearch Failed: " + exp.Message, exp);
    }

    return responseObject;
}

Data:

public Tuple<int, IList<BDO.SearchCardRequest>> GetCardRequestForSearch(BDO.PagingHelper pager, int? companyId, int? categoryId, int? serviceId, int? paymentMode, int? statusId, int? extraSearchId, string searchKeywords)
{
    #region Setup
    IList<BDO.SearchCardRequest> result;
    var sqlConnection = new SqlConnection { ConnectionString = WebConfigurationManager.ConnectionStrings["BlaBla"].ConnectionString };
    var sqlDataAdapter = new SqlDataAdapter { SelectCommand = new SqlCommand { CommandType = CommandType.Text, Connection = sqlConnection } };
    var dataSet = new DataSet();
    #endregion
    try
    {
        var sqlSearch = @"
            SELECT  ParkingCards.ParkingCardId ,
                    ParkingCards.Name ,
                    ParkingCards.CompanyId ,
                    ParkingCards.SeasonPassNo ,
                    Companies.NameE AS Company ,
                    ParkingCards.Nationality ,
                    ParkingCards.Status ,
                    ParkingCards.CardType ,
                    ParkingCards.DrivingLicenseNo ,
                    ParkingCards.Amount ,
                    ParkingCards.PaymentMode ,
                    ParkingCards.ReceiptNo ,
                    ParkingCards.VehicleNo ,
                    ParkingCards.IsBlock
        ";
        if (categoryId != null)
        {
            sqlSearch = sqlSearch + " FROM  ParkingCards LEFT OUTER JOIN  Companies  ON ParkingCards.CompanyId = Companies.CompanyId ";
        }
        else
        {
            sqlSearch = sqlSearch + " FROM Category  INNER JOIN Companies  ON Category.CategoryId = Companies.CategoryId RIGHT OUTER JOIN ParkingCards  ON Companies.CompanyId = ParkingCards.CompanyId ";
        }
        sqlSearch = sqlSearch + " where ParkingCardId > 0 and status in (0,2,4,5,6,7,15)";
        #region Dynamic "Where"s
        if (categoryId != null)
        {
            sqlSearch = sqlSearch + " and Companies.CategoryId = " + categoryId.Value;
        }
        if (companyId != null)
        {
            sqlSearch = sqlSearch + " and ParkingCards.CompanyId = " + companyId.Value;
        }
        if (serviceId != null)
        {
            sqlSearch = sqlSearch + " and ParkingCards.ServiceCode = " + serviceId.Value;
        }
        if (paymentMode != null)
        {
            sqlSearch = sqlSearch + " and ParkingCards.PaymentMode = " + paymentMode.Value;
        }
        if (statusId != null)
        {
            sqlSearch = sqlSearch + " and ParkingCards.Status = " + statusId.Value;
        }
        if (extraSearchId != null && !string.IsNullOrWhiteSpace(searchKeywords))
        {
            if (extraSearchId.Value == 1)
            {
                // ParkingCardId
                sqlSearch = sqlSearch + " AND ParkingCards.ParkingCardId = '" + searchKeywords + "'";
            }
            else if (extraSearchId.Value == 2)
            {
                // AirportPassNumber
                sqlSearch = sqlSearch + " and ParkingCards.AirportPassNumber = '" + searchKeywords + "'";
            }
            else if (extraSearchId.Value == 3)
            {
                // StaffNo
                sqlSearch = sqlSearch + " and ParkingCards.StaffNo = '" + searchKeywords + "'";
            }
            else if (extraSearchId.Value == 4)
            {
                // SeasonPassNo
                sqlSearch = sqlSearch + " and ParkingCards.SeasonPassNo like '%" + searchKeywords + "%'";
            }
            else if (extraSearchId.Value == 5)
            {
                // Name
                sqlSearch = sqlSearch + " and ParkingCards.Name like '%" + searchKeywords + "%'";
            }
        }
        #endregion
        sqlConnection.Open();
        sqlDataAdapter.SelectCommand.CommandText = sqlSearch + " Order by ParkingCardID desc";
        sqlDataAdapter.Fill(dataSet);
        result = new List<BDO.SearchCardRequest>();
        if (dataSet.Tables.Count == 1)
        {
            foreach (DataRow dataRow in dataSet.Tables[0].Rows)
            {
                var newRecord = new BDO.SearchCardRequest();
                try
                {
                    newRecord.ParkingCardId = dataRow.Field<int>("ParkingCardId");
                    newRecord.Name = dataRow.Field<string>("Name");
                    newRecord.CompanyId = dataRow.Field<int?>("CompanyId");
                    newRecord.SeasonPassNo = dataRow.Field<string>("SeasonPassNo");
                    newRecord.Company = dataRow.Field<string>("Company");
                    newRecord.Nationality = dataRow.Field<string>("Nationality");
                    newRecord.StatusId = dataRow.Field<byte?>("Status");
                    newRecord.Status = CommonFunctions.GetStatusAgainstStatusId(newRecord.StatusId);
                    newRecord.CardTypeId = dataRow.Field<int>("CardType");
                    newRecord.CardType = CommonFunctions.GetCardTypeAgainstCardTypeId(newRecord.CardTypeId);
                    newRecord.DrivingLicenseNo = dataRow.Field<string>("DrivingLicenseNo");
                    newRecord.Amount = dataRow.Field<decimal?>("Amount");
                    newRecord.PaymentModeId = dataRow.Field<int>("PaymentMode");
                    newRecord.PaymentMode = CommonFunctions.GetPaymentModeAgainstPaymentModeId(newRecord.PaymentModeId);
                    newRecord.ReceiptNo = dataRow.Field<string>("ReceiptNo");
                    newRecord.VehicleNo = dataRow.Field<string>("VehicleNo");
                    newRecord.IsBlock = dataRow.Field<bool?>("IsBlock");
                    newRecord.IsBlockS = CommonFunctions.GetYesNoForBool(newRecord.IsBlock);
                }
                catch (Exception e)
                {
                    throw new RecordReadingException("Error adding new SearchCardRequest: " + e.Message + "\nFaulting Row: " + dataRow.ItemArray.DebugArray(), e);
                }
                result.Add(newRecord);
            }
        }
    }
    finally
    {
        dataSet.Dispose();
        sqlDataAdapter.Dispose();
        sqlConnection.Dispose();
    }
    return new Tuple<int, IList<BDO.SearchCardRequest>>(result.Count, result.Skip(pager.Skip).Take(pager.Take).ToList());
}

Result:
It seems that the data on the grid is not changing when changing page and everytime, I'm reading the whole data just to get a new page. Is there a better way to do this?

[Viktor Tachev]

Hi Shafi,

In order to return only the items for the current page of the Grid you can implement custom paging. Please check out the Custom Ajax Binding example in the offline sample application that illustrates how you can implement the functionality.

• http://docs.telerik.com/aspnet-mvc/introduction#sample-application

Regards,
Viktor Tachev
Telerik by Progress

[Shafi]

I studied that example but it doesn't solve my issue. I have a series of layers including a point where a service is wrapping the actual database.

• In the said example, the website has direct access to Entity Framework. In my case, my frontend has no database related references.
• In the sample, data is inferred int an IQueryable and then Kendo's filtering, sorting, and grouping is used based on the request parameter on the API. is finally executed once is called. I cannot pass Kendo classes all the way back to the data layer.

var dataContext = new SampleEntities();

// Convert to view model to avoid JSON serialization problems due to circular references.
IQueryable<OrderViewModel> orders = dataContext.Orders.Select(o => new OrderViewModel
{
    OrderID = o.OrderID,
    ShipCity = o.ShipCity,
    ShipCountry = o.ShipCountry,
    ShipName = o.ShipName
});

orders = orders.ApplyOrdersFiltering(request.Filters);

This code is being executed on the controller. I need to have the bring in the data from the from a service through a client. It already has to . Classes like .Filters are not available on server->DAL side.

[Viktor Tachev]

Hello Shafi,

In the provided code you are using Data to pass additional information to the Read Action. Having that information on the server you can use it in the Controller to build the query and request the data from the service.

Then, you would need to ensure that the custom logic implemented in the service is using the additional parameters for querying the database.

Moreover, if you would like to further customize the way data is retrieved you can handle the paging, sorting, grouping, filtering operations manually. Please examine the following article that describes the approach:

• http://docs.telerik.com/aspnet-mvc/helpers/grid/binding/custom-binding

Using the parameters and passing the data back to the Grid component will be similar. The different part would be accessing the data from your custom service. However, that would depend on the custom implementation you have in place.


Regards,
Viktor Tachev
Telerik by Progress