Relational databases and object oriented environment

Question

As every body knows that Object oriented languages provide reusability features.

I have a simple three tier application:

1. presentation layer
2. business layer is designed to reap the benefits of reusability
3. datalayer is a dumb ado.net library(by dumb I meant that it has no business logic in place.)

I have been struggling to enforce code reusability in this datalayer. I am pasting a pseudo pattern in one of my methods in datalayer.

create connection object
open connection to database
create a transaction object
begin transaction
create command object
execute it
.
.
.
.
create nth command object
execute it
commit transaction
close connection

In reality this code swells to around 300 to 400 lines of code and it becomes impossible to read this code.

In this series of command executions we are selecting / inserting / updating queries on different tables. If it were not to be in transaction, I would have separated this code to their respective classes.

There is again a spaghetti pattern which I recently encountered:

business layer method1 calls datalayer method to update column1 
business layer method2 calls datalayer method to update column2
businees layer method3 calls datalayer method to save the entire result in table by updating it.

This pattern emerged when I was trying to reap the benefits of reusability, these methods are called from different locations so they were reused. However, if were to write simple sql query not keeping in mind of reusability there would have been a single call to database.

So, is there any pattern or technique by which reusability can be achieved in data layer?

Note:

1. I don't want to use any stored procedures despite the fact that they offer precompilation benefits and etc. , as they tend to tie datalayer more specific to a particular database.
2. I am also currently not considering any ORM solutions here only plain ADO.net.

---

Excuses for not considering any ORMs.

1. Learning curve
2. Avoiding tight coupling to a specific ORM which I think can be removed by restricting the ORM code in datalayer itself.
3. I checked the internet some time 6 months ago and there were only two popular or widely used ORM solutions available then. Entity Framework and NHibernate. I choose Entity Framework (for some reasons I will link later link1, link2 besides that I had a feeling that working with EF would be easy as it is provided by Microsoft) to start learning.
4. I used this Microsoft recommended book in this book there were three techniques as I understood TPT, TPH and TPC; TPC I never tried.
5. When I checked the SQL generated from the Entity Framework, it was very ugly and was creating some extra columns: Ids, some ugly Case statements etc., it seemed that for a highly transactional system the ORM solution cannot be applied.By highly transactional system I mean 1000 of insertions happening every single minute. The database continues to swell in size and reaches somewhere near 500 to 600 GBs in some distant future.

Answer 1

What I'm not seeing is your model layer.

You have a business layer, and a DAO layer, but no model.

business layer method1 calls datalayer method to update column1 
business layer method2 calls datalayer method to update column2
businees layer method3 calls datalayer method to save the entire result in table by updating it.

Why isn't this:

business layer updates model/domain object A
business layer updates model/domain object A in a different way
business layer persists model/domain to database through data layer.

This way you get re-use, and avoid repeated loops back and forth to database.

Ultimately it sounds like your business layer knows FAR too much of the database data model. You need business objects, not just business methods.

Answer 2

I agree with the comments to your question; you should really avoid re-inventing the wheel here and go with an ORM, if at all possible. Speaking from experience, you're going to end up writing code and solving problems that have long ago been solved and it will probably take you more time in the long run. However, I understand that sometimes there are constraints that don't permit the use of an ORM.

Here are some articles that I have found helpful:

This first article is an old one but it explains the different options that you have for data access design patterns. It has a few different patterns and only you can really decide which one will be best for you but it sounds like you might want to look at the Repository Pattern:

http://msdn.microsoft.com/en-us/magazine/dd569757.aspx

This next article is the first in a series that talks about how to implement a repository pattern with a data mapper which, based on your example above, will probably help to reduce some of your redundant code.

http://blogsprajeesh.blogspot.com/2010/02/data-access-layer-in-c-using-repository.html

Finally, depending on how you implement your data access pattern, you may find the template pattern and generics helpful. The following article talks about that a little bit and you can glean some helpful information from it:

http://www.c-sharpcorner.com/UploadFile/rmcochran/elegant_dal05212006130957PM/elegant_dal.aspx

Without knowing more about your project, it's hard to say, exactly, which pattern will best suit your needs. However, using a combination of the Unit of Work pattern with repositories and data mappers will probably help you to reuse some code and manage your data access.