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

I'm just curious: does anyone else have such problems?

Yes, I have them too. I guess that anybody that doesn't use ORMs, eventually, goes through the same situation.

How did you solve it?

I avoid too generic solutions, because otherwise I run the risk of implementing my own ORM!!

You may think that you have to make your implementation as generic and abstract as possible. For the sake of the SOLID principles. And, it's true.

But keep in mind that your main goal is the application, not to build a framework during the process.

So, implementing reusable and parameterized code is fine, but only if these characteristics goes acordingly addressed to solve real needs of the project. To go further is overengineering.

With the risk of making less reusable code, one approach is making DAOs less generics. -Generics, at certain rate, are considered anti-patterns too-.

Tailoring DAOs to the needs of the business is a way to do it.

No need to say that components at upper layers should remain unaware to these problems. If not, I suggest to review the design.

Additionally, you might be interested on libraries based on row-mapping. For instance, in java, there's MyBatis which I think is also available for .Net. I would not dare to call MyBatis a ORM as EF or Hibernate are, but as a library to get abstracted from the database which has solved many of the issues that you may be suffering.

If you can not afford the cost of integrating more libraries, in my current project, I have implemented the approach described here (with some customizations). It's working like a charm. It balances reusability and parameterization.

Unfortunately, the Specification is considered by some as anti-pattern. Here the reference.

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