Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Grab bag of follow up’s for data access, persistence, query engines, and o/r mapping

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:

    /// <summary>

    /// Finds all the instances of a given type with

    /// a property value

    /// </summary>

    public class FindByPropertyQuery : IObjectQuery


        private readonly Type _memberType;

        private readonly string _propertyName;

        private readonly object _propertyValue;


        public FindByPropertyQuery(

            Type memberType,

            string propertyName,

            object propertyValue)


            _memberType = memberType;

            _propertyName = propertyName;

            _propertyValue = propertyValue;



        public IList FindResults(ISession session)


            ICriteria criteria = session.CreateCriteria(_memberType)

                .Add(Expression.Eq(_propertyName, _propertyValue));

            return criteria.List();



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):


    public interface IReaderSource


        DataSet ExecuteDataSet();



        object this[string parameterName]{get; set;}


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.


    public interface IQueryFilter : IParameter


        bool IsActive();

        string GetWhereClause();

        void AttachParameters(IDbCommand command);


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:

<StructureMap.DataAccess.IReaderSource Type=”TemplatedQuery” Key=”GetMatters”>

  <Property Name=”selectAndFromClause”><![CDATA[





                        {MatterTable} INNER JOIN CrossRef ON {MatterTable}.vendor_id = CrossRef.ForeignRef               


  <Property Name=”filters”>

    <Property Type=”Parameterized”>

      <Property Name=”parameterName” Value=”SenderId” />

      <Property Name=”sqlSnippet” Value=”CrossRef.SenderId = {Value}” />


    <Property Type=”Parameterized”>

      <Property Name=”parameterName” Value=”ReceiverId” />

      <Property Name=”sqlSnippet” Value=”CrossRef.ReceiverId = {Value}” />


    <Property Type=”Templated”>

      <Property Name=”parameterName” Value=”MatterId” />

      <Property Name=”sqlSnippet” Value=”matter_id like ‘%{Value}%'” />


    <Property Type=”Templated”>

      <Property Name=”parameterName” Value=”MatterName” />

      <Property Name=”sqlSnippet” Value=”matter_name like ‘%{Value}%'” />




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:

    public class SecurityDecorator : IReaderSource


        private readonly IReaderSource _innerSource;

        private readonly string[] _roles;


        public SecurityDecorator(IReaderSource innerSource, string[] roles)


            _innerSource = innerSource;

            _roles = roles;



        public DataSet ExecuteDataSet()


            IPrincipal principal = Thread.CurrentPrincipal;

            // if the authenticated principal doesn’t belong

            // to one of the allowable roles, throw an exception


            // Set parameters on the inner query object

            // to filter based on the user’s roles

            if (!principal.IsInRole(“Internal”))


                _innerSource[“ViewInternalIssues”] = false;



            return _innerSource.ExecuteDataSet();



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.

Our own Karl Seguin said that he prefers to do O/R mapping manually.  Fair enough, especially for smaller domain models.  For us, NHibernate cuts down a lot of the work in modeling one-to-many and many-to-one relationships and the transparent lazy loading is awfully nice (and nullable types for us .Net 1.1 slowpokes).  If nothing else, it saves the tedious type coercion that you deal with with manual mapping.  Besides, the overhead of something like NHibernate goes down considerably when you move past the learning curve.

About Jeremy Miller

Jeremy is the Chief Software Architect at Dovetail Software, the coolest ISV in Austin. Jeremy began his IT career writing "Shadow IT" applications to automate his engineering documentation, then wandered into software development because it looked like more fun. Jeremy is the author of the open source StructureMap tool for Dependency Injection with .Net, StoryTeller for supercharged acceptance testing in .Net, and one of the principal developers behind FubuMVC. Jeremy's thoughts on all things software can be found at The Shade Tree Developer at http://codebetter.com/jeremymiller.
This entry was posted in Database and Persistence. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • jmiller

    With that Eric, you get the last word. Comments closed.

  • http://www.developernotes.com Nick Parker


    Thanks for clearing that up. My fiancée and I will be out of town this weekend but I prmoise I will post that StructureMap post next week when we get back.

  • http://www.codebetter.com/blogs/eric.wise ewise

    “Take a closer look Eric, I was specifically talking about cases with several optional query options that could be used in any combination. A sproc would drown in nested if/then’s in that case.”

    This is why, per my original post I was in favor of ad-hoc parameterized queries for selecting data from views. When do you need an ad-hoc query? 99% of the time it’s because you have something like a customer list with 1 million customers that you want to filter on zero to many criteria. A view is perfect for this since it can be optimized by a dba to perform all the joins and still allow filtering to take place.

    Credit cards are a great example of this. We have one view that the application has access to that obfusticates credit card numbers. We have a second view for “privileged users” that actually shows the credit card numbers. The difference is that the one with obfusticated data can be used by any old customer service rep and runs as app. The one with the sensitive data accessible runs as the AD Identity of the user. The end result is that even if one of the normal reps found a way to execute the code that grabbed the view, they’d be stopped cold by the database.

  • jmiller

    I mostly meant it for cases where it’s more than role checking.

    There’s always that nasty horizontal versus vertical slicing of the data. A user has certain roles, but belongs to a certain region, or owns certain issues. Role based security alone won’t handle that.

    I wouldn’t be too sure that T-SQL is a standard. I’ve been around plenty of otherwise strong dev’s that don’t grok stored procedures.

    Take a closer look Eric, I was specifically talking about cases with several optional query options that could be used in any combination. A sproc would drown in nested if/then’s in that case.

  • http://www.codebetter.com/blogs/eric.wise ewise

    hrm, so basically you’re going to generate/write/maintain all these config files, insert role checks all over your codebase, and chalk that up as a win over using a stored procedure secured with AD.

    I’m with jay, I’m not seeing the time savings in this approach. If anything, by moving away from the database/T-SQL standard I think you just succeeded in upping the learning curve for new developers on your staff, increasing the cost of doing business.

    Ah well, more than one way to skin a cat.

  • jmiller


    We do, it’s just wrapped in some cases by those dinky query classes as a shorthand. This may go away, but we execute those IObjectQuery classes like you would use a Ruby block or an anonymous method inside the Query() method.


  • http://www.ayene.com/Blog/ Ayende Rahien

    Is there any reason you are not using Expression in NHib?

    Expression.Gt(“Age”, 18))