What design pattern works for you? ASP.NET with MySQL

Hi this is a question to all experienced developers who create their web applications using ASP.NET(C#) with MySQL. I am currently using the Microsoft Enterprise Library to implement a database factory design pattern.

I have a DAL which returns a DataTable. I have a BLL that executes that DAL which returns a List<> of my DataObjects. The BLL has parameters for sorting, limit records, and filters.

What do you think is by far a good design pattern that wi开发者_开发知识库ll work for transactional queries and queries containing a number of joins (4 to 10 tables) and conditions.

I am not satisfied with the current implementation and I don't know if it'll work for me in the long run. I keep on creating DataObjects -> BLL -> DAL classes every time.

---

For transactional queries, I'd look into the UnitOfWork pattern, which can help manage the complexities of updating several objects that are modified during a process that can be considered a single unit of work (often associated directly with a single transaction).

For queries with many joins, look into Command-Query Responsibility Segregation. The general idea behind CQRS is that our data-access patterns look very different when we are querying data (e.g., for reporting) than when we are requesting data to modify it. CQRS tells us to embrace this difference and code our access differently because the technical requirements of each type of access are truly so different.