How to define the seams between the domain layer and a database full of stored procedures?

Question

We have a legacy database full of stored procedures.

These stored procedures are somewhat CRUD-categorized but upon inspection of some procedures, they are not "simple" as they might update, delete, whatever other records in other tables.

So to my mind (correct me if I'm wrong) it would seem that using an ORM would be pretty difficult since

1. I can't access the tables directly: I must use a stored procedure
2. The stored procedures would not map to CRUD

I'm leaning toward defining repositories to isolate the domain layer from the database but I fear that I would need to define a lot of infrastructure (caching, unit of work and the like) that I get for "free" in a ORM.

Any insights?

Answer 1

I'm in almost the same situation; our database relies entirely on stored procs and we cannot introduce an ORM as everything would have to be rewritten to make use of it. What I did was write an abstraction layer, basically an implementation of the Table Data Gateway pattern (although I call it a Repository, it's not the true DDD definition) that abstracts away the call to SqlCommand and friends. Then I have a Service Layer sitting over that which just taps into the repository and calls the methods on it, returning business objects (essentially DTOs), sometimes with an inner collection of another business object (the mapping handled by the sproc and mapped in code).

It's not perfect but it nicely hides the messy details of using a stored procedure.

Answer 2

We use a DAL layer that not only does the SQL stuff but handles the abstraction. The DAL layer's responsibility is to take in a generic object called a data transfer object. The data transfer object contains a list of DAL POCOs. The DAL looks at each POCO (we decorate the class with attributes) and dynamically generates a mapper class based on the object type. From there, it is just mapping POCO properties to stored procedure parameters. The DAL is very simple, just Save, Delete, and Query on the interface along with a custom command when you want to do something else (call a stored procedure other than the basic Insert, Update, Delete, Query ones) for some extendability.

The DAL doesn't really care about the Domain, its tied to the schema. So, the POCOs that the DAL uses may be mapped to different domain POCOs in the domain layer. So there could be an intermediate layer in some cases, but you could use the POCOs that the DAL is using as they are just state objects and contain no methods or logic in them, but somethimes the domain and DAL don't really match up so thats when the domain would be responsible for converting the domain object to a DAL object/POCO.

From a DAL standpoint, currently we are supporting 50 out of planned ~200 tables with this pattern, I guess this is an home grown ORM lite. With some code generators we implemented, most and the mapping code (tedious glue code) is handled automatically, so we can concentrate on the business and presentation logic.

But that's how we did it with 100% Sprocs.