Just catching up on questions and comments from the Why I do not use Stored Procedures post yesterday.

Sachin Rao asked my take about using an O/R mapper for for reporting.  I'd say "it depends," but in a purely reporting system I would probably opt for the simple "get dataset, slap into datagrid, rinse, and repeat" strategy.  I don't like sproc's for reports that have a multitude of optional search criteria, but more on that below.

Nick Parker (who owes me a post on StructureMap) asked about the OO query engine that we use and how it relates to NHibernate.  There's nothing special going on with this.  The class that we use as the innermost gateway to NHibernate has this method IList Query(IObjectQuery query)  that accepts an IObjectQuery object.  The implementations of IObjectQuery are just syntactical sugar over NHibernate's ICriteria objects.  Here's an example:

Come to think of it, this would make a good example of a fluent interface.  You can use these dinky little objects to make your code easier to read by making the logic of a query apparent without getting bogged down by the NHibernate machinery.  Sql where clauses are very often business logic, and hence need to be tested.  Many times it's easier to test that the middle tier creates and passes the correct queries by checking the state of these dinky little query objects than it would be to test the middle tier all the way through the backend.

When I mentioned the OO query engine I really just meant to put an Object Oriented structure around the creation of the sql for reports.  There are a handful of certainties in the career of any developer.  You will at some time write an Invoice class, an Order class, a dozen Address classes, and write a sql generator.  All of the "data sources" for our reports are implementations of this interface (partial definition):

The actual implementation could be a stored procedure, parameterized sql, or something else altogether.  As far as the reporting module is concerned everything is just an IReaderSource with a number of parameters.  Just take the options from the submitted query form and call myDataSource["State"] = queryView.StateCode.  For reports with a large number of optional query options we use an implementation of IReaderSource that consists of a select clause and an array of objects that model an optional piece of the where clause.

When the report is executed with any number of query options the IReaderSource scans through its collection of possible IQueryFilter objects for the ones that are IsActive(), and calls GetWhereClause() and AttachParameters() to build out the sql where clause and IDbCommand object. 

Unsurprisingly, we configure and construct these query graphs with StructureMap.  In this case we put the configuration for the queries in embedded resource files like this:

Jay R. Wren asked about the security model.  Just for fun, here's a strategy I've used before with some success (assuming you don't get stupid with overgeneralization).  If you use something like my IReaderSource interface that represents a "query" for a report, you could happily wrap the query objects in a decorator pattern class that governs security rules.  The security decorator might take a look at the IPrincipal object on the thread and do security assertions based on the allowed roles for the named query, or more powerfully, might transparently do something like this:

As an aside, one of the most painful things I've ever witnessed was a system that pulled every single record into memory, then applied security filtering row by row.  That strategy sure bogs down when the table in question runs into 10000+ rows plus.