Persistent Layer Validations

Question

While saving data using a stored procedure often we encounter foreign key constraints, unique key constraints, etc. All such errors can be corrected by passing in the right data.

Validation can be done in the business logic layer and should be sent to the persistence layer only if it succeeds. But in some cases it is convenient to validate data while saving. Like in a stored procedure.

Say for example if the user enters some date range values it should be validated that the range do not overlap any existing ranges. In such situation it is better to return some error code which can tell us if the range is overlapping and cannot be saved.

In SQL Server we can simply raise custom exceptions but I want to do it without using exceptions. Is there any validation frameworks already available which I can use.

I am looking for a SQL Server 2005 and .net specific solution.

---

P.S.: I generally return custom error codes from the Stored Procs and then parse them by looking up in an xml file and then use it in my Business Layer rules engine.

Answer 1

Embedding business logic in the SQL Server might improve the performance but it's going to complicate the design by violating seperation of concerns. In order for me to have portable business logic, it should be in the business layer. I would remove the validation logic from the stored procs and only use them to make CRUD operations easier. You never know when the project stakeholders are going to say "make it run on Database X!". Do your best to keep the validation logic database independent.