DataSources based on dynamic sql queries

edited November 2022 in FastReport .NET

Greetings,

Is it possible to create datasources in runtime based on dynamic sql quires which

are stored as plain text and keeps some hierarchy?

 

I got huge database with thousands of tables and views.

Each table has millions of rows and some relations.

 

My datamodel for report is something like:

 

master dataset 1:

- parent: null

sql:

select a.*, a.id as parent_id

from some_huge_master_table a

where id in (someparameter-from-entry-call)

 

detail dataset 1.1:

-parent: master dataset 1

sql:

select b.*, b.id as id

from another_huge_table b

where master_id = :parent_id

 

 

detail dataset 1.2:

-parent: master dataset 1

sql:

select c.*, c.id as id

from and_another_huge_table c

where master_id = :parent_id

 

...

 

Those tables cannot be fetched by Table/View Datasources (they are too big and

somehow dynamic) - but I can say before executing report how this model looks

like (what column and datatypes there are). I just cannot fetch the data

beforehand. And I need to open detail datasets after fetching rows in Masterdataset

because of given relation, detail datasets depend on actual data of master data

rows.

 

Is this reachable somehow? Is it reachable by standard objects (Table/View

DataSources or by BusinessObjectDatasource)? Or should I prepare my own descendant

of BaseDataSource? Or should I prepare some fake Connection?


Regards,

Martin

Comments

  • Hello!

    When using dynamic SQL queries configured manually outside the designer (for example, when creating a DataSet or TableDataSource in code), nested Master-Detail DataBands work correctly provided that a DataRelation is explicitly defined and both data sources are included:

    var ds = new DataSet();
    // Add the Master and Detail tables with the MasterId foreign key
    ds.Relations.Add(‘Master_Detail’, ds.Tables[‘Master’].Columns[‘Id’], ds.Tables[“Detail”].Columns[‘MasterId’]);
    
    report.RegisterData(ds, ‘Data’);
    report.GetDataSource(‘Master’).Enabled = true;
    report.GetDataSource(‘Detail’).Enabled = true;
    masterBand.DataSource = report.GetDataSource(‘Master’);
    detailBand.DataSource = report.GetDataSource(‘Detail’);
    detailBand.Relation = report.Dictionary.Relations.FindByName(‘Master_Detail’);
    

    If the query is parameterised on the TableDataSource side within the .frx file, update the SelectCommand and CommandParameterCollection parameters before calling Prepare(), rather than replacing the table with an incompatible schema.

This discussion has been closed.