News combit Dev Blog

Supporting Cross-Datasource Relations

Posted: Thursday, September 28, 2017 | 0 comment(s)
 
The .NET DataProvider concept allows to bind to almost any data source. Basically, it mimics a relational database management system containing tables, relations, sort orders etc. However, often you'll find yourself needing to combine data from different sources, e.g. a server log file that contains customer logins and a SQL customer database that contains all pertinent information about the customers.

Until LL23, there was no easy way to combine such data relationally, as relations could only be defined within the same data source, not across several different ones. With the advent of version 23, we'll support cross provider relations to change this.

This feature is internally enabled by setting filters on the child table. We introduced filtering at database level in version 20, which makes the process lightning fast. The child of a cross provider relation just needs to support the ApplyFilter method of the ITable interface. This is automatically supported e.g. for all SQL data sources, DataSets, Objects (via LINQ), OData and others. If you've written your own provider that supports ApplyFilter, you're immediately in the game, too.

Setting up such a cross relation is easy:


 // Prepare data providers
CsvDataProvider csvFile = new CsvDataProvider("ServerLog.log", true, "InetData", ';');
SqlConnectionDataProvider sqlServer = new SqlConnectionDataProvider(new SqlConnection("Server=SampleServer;Database=CRMDatabase"));

// Combine both data providers in a DataProviderCollection to be able to add relations between them
DataProviderCollection combinedDatasource = new DataProviderCollection { csvFile, sqlServer };

// Use a table from SQL Server as child table for rows from a CSV files (InetData -> Customers)
combinedDatasource.AddCrossProviderRelation("InetData2Customers",
/* Parent: */ csvFile,   "InetData", "CustomerID",
/* Child:  */ sqlServer, "Customers",   "CustomerID");

using (var LL = new ListLabel())
{
    LL.DataSource = combinedDatasource;
    LL.Design();
} 

In the Designer, this relation is treated just like a "normal" relation and is supported in report containers, charts, drill down etc. As long as the parent table also supports ApplyFilter() you can even access the 1:1 data of the parent from within the child table.

This makes the data binding a lot more versatile and doesn't need the (not so well scaling) workaround of an in-memory database as outlined here anymore – it just works directly out of the box :).




Author: Jochen Bartlau
Head of Development List & Label

Jochen Bartlau leads the development at combit as Managing Director. He's a Microsoft .NET enthusiast driving innovation & agile project management. The mobile devices geek who used to be a physicist in his first life loves to spend his spare time with his family.


  

+1 800 256 3608 (toll-free North America only)