.NET data-based application architecture suggestions, opinions, good practices

Question

Hello, I'd like ask a question about building applications in .NET that use data from a database. There are many technologies and patterns and I'm trying to connect it all.

I'm building a desktop application with local database, so I choose SQLServer CE + WinForms, but I'd like to keep this as general as possible. I'm not into other technologies (Java, etc.), but if there are some good solutions there, then you're welcome to write about them.

I'd like to ask for your suggestions, opinions and good practices that you use for building applications. Would you add or remove any of the listed layers? What technologies do you prefer?

So here are the basic layers of the application:

1) SQLServer CE/SQLServer/Oracle/IBM DB2
--------------------------------------------------
2) LINQ to SQL/Entity Framework/NHibernate/ADO.NET
--------------------------------------------------
3) Data Transfer Objects
--------------------------------------------------
4) DTO-to-BM/Data Access Objects
--------------------------------------------------
5) Business Model
--------------------------------------------------
6) MVP/MVC/MVVM/PM
--------------------------------------------------
7) WinForms/WPF/ASP.NET

1) This is the classic relational database. So the first question is whether to use stored procedures and triggers or not? I'll be using SQLServer CE, so no SPs for me, but I was wondering if peaple are pro or against them. It seems easier to me (more testable, more verifiable) NOT to put logic into the DB. The business rules would be placed in the business layer - maybe in some sort of "framework".

2) This is the DB access layer.

3) DTOs - simple POCOs. Are they needed?

4) Mapping between DTOs and BM. Hand coded, AutoMapper or maybe inheritance? Or maybe DAO - abstract objects for accessing the database that return objects from BM?

5) Business objects that build the business model. I'd put all the business rules and validation here. But is this layer needed? Or would you put all the business stuff into layer 3?

6) and 7) Building UI using BM.

Answer 1

If you want to use some embedded sql database you should have a look at SQLite. It has much more useful features than SQL CE and works faster and without problems. Or even more, I recommend you to use some document or object database (db4o for example). IMO, it'll simplify your DAOs and code a lot.

1, I prefer to use ORMs and I use stored procedures and triggers very rarely and only in legacy applications. It's much more easy to deal with c# code and to have all in one place. And you are right with logic and business rules.

2, I prefer to use NHibernate as the most mature, powerful and customizable framework. And I also recommend you to look at NhProf (by the word, the same profilers exists for L2S and EF).

3&4, Just remove it, unnecessary noise and monkey work.

2&5, Use your database model as BusinessModel (it's very easy with NHibernate). Check this and this articles.

6&7, For web I choose MVC with asp.net mvc, for WPF - MVVM. From my experience, don't use WinForms and Asp.Net when you don't have restrictions like legacy code. It's much more easy and fun to develop using Asp.Net Mvc and WPF. And there are a lot of cool and time saving frameworks for them - MvcTurbine, MVC Contrib, SharpArchitecture for asp.net mvc and Caliburn, WPF Toolkit, Cinch, MVVM Light toolkit and many others.

Also I recommend you to look at the following articles and samples that may help you to design your app better:
http://nhforge.org/blogs/nhibernate/archive/2009/08/27/nhibernate-and-wpf-validations.aspx
http://nhforge.org/blogs/nhibernate/archive/2009/11/07/nhibernate-and-wpf-the-guywire.aspx
http://msdn.microsoft.com/en-us/magazine/ee819139.aspx
http://code.google.com/p/unhaddins/source/browse/#svn/trunk/Examples/uNHAddIns.Examples.WPF

Answer 2

I'd like to ask for your suggestions, opinions and good practices that you use for building applications.

Always abstract out the data access implementation from the business layer. Specify the contract using good old C# interface's; you can then have multiple implementations - any bad technology choices you make are minimized as you can implement using something else. Others can also develop their own implementation if they really want to (the solution is more extensible), and it's easier to test.

I like they layering you have - as Zihotki points out you could loose a couple; as long as you have seperate Ui business logic tiers and an abstracted data access I'd be happy.

Would you add or remove any of the listed layers? What technologies do you prefer?

I can't speak for specifics but it does depend on what you want to do and who's going to be working on the system, for example: is this going to be open source? If your client base includes developers then you need to evaluate what they might want, and balance that with your other needs. I think your list is fine. I wouldn't use NHibernate as I haven'ty had any experience with it - but that's more based on personal history than evidence.

If you're considering ORM tools (like NHibernate) I suggest you consider LightSpeed, friends of mine (whom I respect) speak VERY highly of it.

So the first question is whether to use stored procedures and triggers or not?

I'd very strongly advocate using SP's, particularly in web environments as (done correctly) they offer a better level of protection aganist SQL Injection attacks.

Also, just because you're using SP's doesn't mean you have business logic in them.

Triggers - what are they doing? Nothing 'business logic' related I hope :)

Business objects that build the business model. I'd put all the business rules and validation here. But is this layer needed? Or would you put all the business stuff into layer 3?

As you've probably worked out already, I'm a firm believer of putting all the business logic into the business layer, it'll be more readily reusable, and the advantages of consistency should be obvious :)