Building SQL Dynamic WHERE Condition???


Author
Message
Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
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!!!

Edhy Rijo

Ivan George Borges
Ivan George Borges
Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)Strategic Support Team Member (2.2K reputation)
Group: StrataFrame MVPs
Posts: 1.9K, Visits: 21K
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
Attachments
Report.jpg (185 views, 121.00 KB)
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search