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

Question

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?

Answer 1

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.

Answer 2

One factor that usually complicates the maintenance of an application is unnecessary complexity in the design of the solutions. Wrong abstractions or the lack of them are among the things that more complexity adds to the source code. Complexity I would dare to call unnecessary because it's likely there will be simpler alternatives.

Do or don't, there's no try.

Note the emphasis on or the lack of them (abstractions). When you said if we want to reuse some piece of SQL (...) we keep them as string variables sounds like you tried but failed at solving the real problem. The solution scratches the surface of an underlying problem but it doesn't address it. It's a palliative solution that might have worked for a while, but it doesn't pass well the test of the time.

Times and changes challenge the designs and their implementation. As the changes happen, designs should prove to be still valid, resilient and profitable. Otherwise, they should be reviewed and changed according to present needs.

Keep things simple. Avoid implementing in-expresso or one-fits-all solutions, unless you have the time, the resources and the understanding required for it. Keep in mind your goal which is, most of the time, making the business work. Don't waste resources on half-solutions. Rather solve the immediate problem first, make the solution sophisticated and reusable (if possible) later, as the problem shows you its patterns.

Code is read more times than it's written

This might sound controversial but consider undoing your "not so generic" solutions. Don't try saving a couple of lines of code (or strings) at expenses of readability. Making the code easy to reason about and easy to read is more valuable. It's likely to save more time-money than your variables. Don't let DRY principle fool you. DRY urges you to don't repeat decisions, to don't repeat solutions. In your case, I'm reluctant to believe in the capacity of variables to satisfy your needs for DRY.

Don't reinvent the wheel

Consider existing solutions based on row-mapping. For example MyBatis. Compared with other ORMs, MyBatis and the likes are reasonably simpler, easy to integrate with and yet functional. It works with a sort of prepared statements. I say "a sort of" because these can also be dynamic.

Alternatively, I have implemented the approach described here^* (with some customizations). It works like a charm, balancing reusability and parameterization. Some times I end up with a couple of lines that look very similar, but it's irrelevant for my goals.

---

^{* Unfortunately, the Specification is considered by some as an anti-pattern. Here the reference but in my opinion, it depends if it passes the test of the time or not.}

Answer 3

I would recommend incrementally refactoring the code using a micro-ORM like Dapper. It's light-weight, high-performant, and will allow you to continue to use traditional SQL statements, if that's what you prefer. Dapper is my goto choice when I can't use or don't want to use something heavier like Entity Framework or Nhibernate.

Answer 4

One way to do it is to fully embrace SQL, instead of mixing your metaphors.

For example:

var user = context.ExecuteQuery("SELECT * FROM User WHERE UserID = {0}", userID);

It returns a List<User> collection (containing one record, in this case).

Dapper and Entity Framework are both capable of queries like this. You can make them as elaborate as you like, run multiple queries and make complex data objects this way.

You could even code-generate CRUD methods, if you are so inclined.

Answer 5

I have search in the property. The Entity will have many properties. Class PropInt will have the return value and also how to build search. How you build up a search on Int is the same - you just need to know the table and column name.

User iEnumerable and DataReader

public iEnumerable<SomeEntity> GetEntities(SomeEntity someEntity);