Dynamic DataTables through code
I have the following situation:
I want my program to supply data to a report dynamically, i.e. while it is building. The requirement is that the Report itself must not open SQL connections. It gets all its data from the program, which in turn gets the data from a DB through NHibernate. So what we do is, we take user-created SQL statements, called queries, execute them through NHibernate, and register the resulting DataTable with the report before we design/show/print the report. This is no problem for simple statements without parameters, RegisterData() works fine.
However, we also have queries with paramters, which are supplied through subqueries. For example, we would have a query that creates a DataTable from the "Customers" table. Then for each customer in that set, we take that customer's ID as a parameter for the subquery, which would draw data e.g. from the "Articles" table.
So what we want to do in the report is that when the report is previewed/printed etc., for each displayed customer record, it adds one or more corresponding entries from the Articles table.
Example SQL:
Query: "SELECT * FROM Customers WHERE Name = 'Smith' "
SubQuery: "SELECT * FROM Articles WHERE CustID = :ID"
possibly SubSubQueries etc....
So the subquery has a parameter ":ID", which refers to the ID of the selected row of its superquery.
Now the difficulty, as I see it, is that the report may not open an SQL connection but must somehow communicate with my program to get what it needs.
My suspicion and preferred approach would be to use some special Data class that fires events when accessed which I can intercept at the program level. Though I am happy for any approach that gets this solved.
Have I explained the question properly and do you know any approach to this?
I want my program to supply data to a report dynamically, i.e. while it is building. The requirement is that the Report itself must not open SQL connections. It gets all its data from the program, which in turn gets the data from a DB through NHibernate. So what we do is, we take user-created SQL statements, called queries, execute them through NHibernate, and register the resulting DataTable with the report before we design/show/print the report. This is no problem for simple statements without parameters, RegisterData() works fine.
However, we also have queries with paramters, which are supplied through subqueries. For example, we would have a query that creates a DataTable from the "Customers" table. Then for each customer in that set, we take that customer's ID as a parameter for the subquery, which would draw data e.g. from the "Articles" table.
So what we want to do in the report is that when the report is previewed/printed etc., for each displayed customer record, it adds one or more corresponding entries from the Articles table.
Example SQL:
Query: "SELECT * FROM Customers WHERE Name = 'Smith' "
SubQuery: "SELECT * FROM Articles WHERE CustID = :ID"
possibly SubSubQueries etc....
So the subquery has a parameter ":ID", which refers to the ID of the selected row of its superquery.
Now the difficulty, as I see it, is that the report may not open an SQL connection but must somehow communicate with my program to get what it needs.
My suspicion and preferred approach would be to use some special Data class that fires events when accessed which I can intercept at the program level. Though I am happy for any approach that gets this solved.
Have I explained the question properly and do you know any approach to this?