StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Building SQL Dynamic WHERE Condition???

Building SQL Dynamic WHERE Condition???

Edhy Rijo — Mon, 18 Jan 2016 08:45:57 GMT

Hi all,
I normally will build a MS-SQL Stored Procedure to get the data from all my reports, but in this case, I am working on a project that would have a gazillion filter conditions. We are re-writing an application from a Visual FoxPro and the way this was handle was by using the old good VFP macro expansion technique by testing each conditions in the query or filter form and building the "WHERE - filter" string.

I can easily replicate that kind of logic using and SqlCommand object, but was wondering if there was a much better way to build this dynamic query? I have not looking at the Browser Dialog source but I would imagine it uses a more generic object oriented approach to build the query and grab the data.

As always any suggestion is greatly appreciated.

Thanks!!!

RE: Building SQL Dynamic WHERE Condition???

Ivan George Borges — Mon, 18 Jan 2016 08:45:57 GMT

Hi Edhy.

Remember the BrowseDialog ended up having only an AND possibility between all arguments, guess it is a price for the generic approach.
In the beginning, I tried to build something generic too, but after a while gave it up, maybe too soon, and now for each XYZ report, I will have at least an XYZReportCriteria form, an XYZDataSource class file, an XYZBBS business binding source and an XYZLayout.xml.

The ReportCriteria form will call a Populate method in the DataSource class, which will check all conditions from the arguments passed and build the Command to query the database, returning back the business binding source populated so the form can then use a ReportViewer using the .xml layout set for it.

Attached is a picture of a basic sample. Some reports, of course, will involve lots of BOs.

Abraços.

Ivan