i'm using three tier architecture with c# and sql server database as the data source. according to DRY principal the validation should be done in one place only which in my case is either the front end data access layer or the database stored procedures.
so i was wondering whether to validate the stored procedure parameters in data access layer or leave it to stored procedure itself??
DRY is an important principle, but so is defence in depth.
When it comes to validating input, you must ensure it is safe - this should be done on each and every level (so both in DAL and stored procedure).
As for validating data for business logic, this should be in your business logic layer (BLL).
If you are using a three tier architecture, I would recommend you investigate using an ORM instead such as Nhibernate, or Linq to Entites. An ORM will provide you with better refactor-ability and hence maintainability (Maintainability to me is the most important thing, as it leads to quality in the longer run, based on my experience).
It is not wise to put your validation in to the UI, as it is safer to have your secuirty down in your DAL (data access layer) than in your UI where it can more easily be bypassed (accidentially or on purpose). Think about SQL injection. You should validate on your data access agasint this as opposed to only your UI as it is easy to miss on your UI, and easy to bypass as a malicious user trying to gain access to other data they are not allowed to access.
I think that it might make sense to have validation potentially on the UI for usability, and in the data access layer for safety. I do like the DRY principal of doing validation in one place, and you can still do that. If you make a common set of rules which are propogated through to both the data access layer and the UI then you will have a safe and usable system (through immediate feedback on data entry). ANother way could be to have different rules for different layers. For example field length rules and data entry patterns could be UI specific. The DAL can enforce the data is valid for example. THat is doing validation in multiple places, but as long as they are not independently doing the same thing, I think you will be ok. This is one of the hardest areas of consideration when designing an application as validation is a cross cutting concern and how you do it depends alot on how you structure the rest of your application design.
