In the latest project I was involved with, there was a need to present database tables data directly in the Umbraco’s backoffice, where all CRUD operations must be supported. This tables could be something like 1000 to 1 million rows and both the website and database would be hosted in Azure, in a WebApp and SQL Database respectively. Users should be able to create complex filters and order the data as they wish as well as update the data individually and in batch.
From the get-go there were some challenges we needed to overcome in order to create an usable system, that was user-friendly, fast, reliable, and where no conflitcts would come up when data was being updated from multiple users.
The solution was a new Umbraco Package we called Search Manager (since the database tables ultimately feed an Azure Search index) that uses Telerik’s Kendo UI Grid, WebAPI and Entity Framework. The following presents the process it took to reach the end result.
Step 1
We created a simple Umbraco Package that implemented a Kendo UI Grid which got its data from a WebAPI method that returned all rows from a table using Petapoco.
This allowed us to have the complete table data in memory using a Kendo UI Grid. It worked great but had major problems:
- A table with around 40k rows was actually a 40 MBs json download in order to populate the grid
- Users could be working with outdated data, since it was loaded in memory and changes could have been made to the source
Step 2
We started using the server paging feature of the Kendo Grid. This dropped the download size greatly, but we still had problems:
- Even though we are working with only a subset of results client-side, we were still getting the complete table data server-side, using petapoco.
We needed an easy way to translate the client-side queries, such as filters, paging, order, etc to SQL queries.
Step 3
We changed the type of queries being generated by the Grid to OData. Using the OData WebAPI nuget package we are able to map the odata query to a C# object and then directly apply the query to any IQueryable data.
Petapoco does not provide any IQueryable data support, so we changed our ORM to Entity Framework, which does, and voilá!
Finally
With everything in place, we are able to have a filtered subset of results in a Grid, that gets exactly what it needs to display from a dynamically generated SQL query.
- It is fast because we only get exactly what we need:
An OData query generated by the Kendo UI Grid looks like this:
http://localhost:14231/umbraco/backoffice/SearchManager/PricesApi/Read?%24inlinecount=allpages&%24top=20&%24skip=40&%24orderby=ProviderName+desc&%24filter=(NatureProcCode+eq+%27exames%27+and+AvgAmtClaimed+gt+10)
Which is translated to the follwing SQL query:
exec sp_executesql N'SELECT
[Project1].[Approved] AS [Approved],
[Project1].[DiffWeightedRate] AS [DiffWeightedRate],
[Project1].[SubmitPrvId] AS [SubmitPrvId],
[Project1].[PracticeSeq] AS [PracticeSeq],
[Project1].[NatureProcCode] AS [NatureProcCode],
[Project1].[GroupProcCode] AS [GroupProcCode],
[Project1].[Network] AS [Network],
[Project1].[AmtClaimedWeighted] AS [AmtClaimedWeighted],
[Project1].[ManualPrice] AS [ManualPrice],
[Project1].[IsImported] AS [IsImported],
[Project1].[DiffCalculatedRate] AS [DiffCalculatedRate],
[Project1].[AmtContract] AS [AmtContract],
[Project1].[AvgAmtClaimed] AS [AvgAmtClaimed],
[Project1].[ProviderName] AS [ProviderName],
[Project1].[ClinicName] AS [ClinicName]
FROM ( SELECT
[Extent1].[Approved] AS [Approved],
[Extent1].[DiffWeightedRate] AS [DiffWeightedRate],
[Extent1].[SubmitPrvId] AS [SubmitPrvId],
[Extent1].[PracticeSeq] AS [PracticeSeq],
[Extent1].[NatureProcCode] AS [NatureProcCode],
[Extent1].[GroupProcCode] AS [GroupProcCode],
[Extent1].[Network] AS [Network],
[Extent1].[AmtClaimedWeighted] AS [AmtClaimedWeighted],
[Extent1].[ManualPrice] AS [ManualPrice],
[Extent1].[IsImported] AS [IsImported],
[Extent1].[DiffCalculatedRate] AS [DiffCalculatedRate],
[Extent1].[AmtContract] AS [AmtContract],
[Extent1].[AvgAmtClaimed] AS [AvgAmtClaimed],
[Extent1].[ProviderName] AS [ProviderName],
[Extent1].[ClinicName] AS [ClinicName]
FROM (SELECT
[PriceGrid].[Approved] AS [Approved],
[PriceGrid].[DiffWeightedRate] AS [DiffWeightedRate],
[PriceGrid].[SubmitPrvId] AS [SubmitPrvId],
[PriceGrid].[PracticeSeq] AS [PracticeSeq],
[PriceGrid].[NatureProcCode] AS [NatureProcCode],
[PriceGrid].[GroupProcCode] AS [GroupProcCode],
[PriceGrid].[Network] AS [Network],
[PriceGrid].[AmtClaimedWeighted] AS [AmtClaimedWeighted],
[PriceGrid].[ManualPrice] AS [ManualPrice],
[PriceGrid].[IsImported] AS [IsImported],
[PriceGrid].[DiffCalculatedRate] AS [DiffCalculatedRate],
[PriceGrid].[AmtContract] AS [AmtContract],
[PriceGrid].[AvgAmtClaimed] AS [AvgAmtClaimed],
[PriceGrid].[ProviderName] AS [ProviderName],
[PriceGrid].[ClinicName] AS [ClinicName]
FROM [dbo].[PriceGrid] AS [PriceGrid]) AS [Extent1]
WHERE ([Extent1].[NatureProcCode] = @p__linq__0) AND ([Extent1].[AvgAmtClaimed] > @p__linq__1)
) AS [Project1]
ORDER BY [Project1].[ProviderName] DESC, [Project1].[AmtClaimedWeighted] ASC, [Project1].[AmtContract] ASC, [Project1].[Approved] ASC, [Project1].[AvgAmtClaimed] ASC, [Project1].[ClinicName] ASC, [Project1].[DiffCalculatedRate] ASC, [Project1].[DiffWeightedRate] ASC, [Project1].[GroupProcCode] ASC, [Project1].[IsImported] ASC, [Project1].[ManualPrice] ASC, [Project1].[NatureProcCode] ASC, [Project1].[Network] ASC, [Project1].[PracticeSeq] ASC, [Project1].[SubmitPrvId] ASC
OFFSET @p__linq__2 ROWS FETCH NEXT @p__linq__3 ROWS ONLY option (Recompile)',N'@p__linq__0 varchar(8000),@p__linq__1 decimal(2,0),@p__linq__2 int,@p__linq__3 int',@p__linq__0='exames',@p__linq__1=10,@p__linq__2=40,@p__linq__3=20
- It’s user-friendly and reliable because users can filter the data as if they were using an excel file on steroids
- It avoids conflicts because we only get a subset of the results each time and each change is commited in a single transaction
Code samples:
grid = $("#grid").kendoGrid({
dataSource: new kendo.data.DataSource({
transport: transport,
schema: schema,
serverFiltering: true,
serverPaging: true,
serverSorting: true,
type: "odata",
pageSize: 20
}),
dataBound: onDataBound,
pageable: true,
filterable: true,
sortable: true,
scrollable: false,
navigatable: true,
resizable: true,
batch: true,
columnMenu: true,
editable: true,
toolbar: [
{ template: '<a class="k-button k-button-icontext k-grid-save-batch" href="\\#""><span class="k-icon k-i-update"></span>Guardar Alterações</a>' },
{ name: "cancel", text: "Cancelar" },
],
columns: [...]
});
[HttpGet]
public IHttpActionResult Read(ODataQueryOptions<PriceGrid> opts)
{
Mapper.CreateMap<PriceGrid, PriceGridBO>();
using (var context = new EntitiesContext())
{
using (var qh = new HintScope(context, QueryHintType.Recompile))
{
List<PriceGrid> results = opts
.ApplyTo(context.PriceGrids)
.Cast<PriceGrid>()
.ToList();
List<PriceGridBO> boEntities = results.Select(x => Mapper.Map<PriceGrid, PriceGridBO>(x)).ToList();
PriceResponse response = new PriceResponse()
{
Entities = boEntities,
Total = Request.ODataProperties().TotalCount.HasValue ? Request.ODataProperties().TotalCount.Value : 0
};
return Ok(response);
}
}
}
