Data layer without ORM – how to work with it in efficient way

netsql

I work on a big .NET project. It started more than 10 years ago, there is no ORM (NHibernate or EF I mean) and there is no chance to migrate to some widely used ORM (and for complex SQL queries ORM doesn't suite anyway). We use ADO.NET MS SQL provider with custom facade for datareader. We build select queries as strings so, for instance, if we have big query and want to have some customizations – it looks like this:

public List<SomeEntity> GetEntities(string titleFilter, bool joinsometable, bool selectAll...)

and inside this method we have string builder which builds query according to input conditions and parameters. Additionally if we want to reuse some piece of SQL (like list of repeating joins or conditions) we keep them as string variables. So finally it's very hard to support it. I'm just curious: does anyone else have such problems? How did you solve it?

Best Answer

Object relational mapping without ORM can be painful and somewhat repetitive. A book wouldn't be sufficient to address all the aspects of this problem, and be assured that you're far from alone in this situation.

I understand from your question that you're most concerned about complex queries. For this specific topic, you could consider the query object design pattern. It could allow you to reuse complex SQL queries. In this design, criteria are also objects (composition) that could, for example, help to generate the SQL WHERE clauses. This proves to be much more convenient to use than generating the SQL commands with tedious string concatenation here and there in the code.

In this regard, I can highly recommend you "Patterns of enterprise architecture" from Martin Fowler, which gives nice examples of this pattern, and addresses many other useful points in typical OO application with heady database integration.

Related Topic