Persistent level checks
When saving data using a stored procedure, we often come across foreign key constraints, unique key constraints, etc. All such errors can be corrected by passing the correct data.
Validation can be performed at the business logic level and should only be submitted to the persistence level if it is successful. But in some cases it is convenient to save the data when saving. Like a stored procedure.
Say, for example, if a user enters some values for a date range, it should be validated so that the range does not overlap existing ranges. In such a situation, it is better to return some error code that can tell us if the range overlaps and cannot be saved.
In SQL Server, we can just raise custom exceptions, but I want to do this without using exceptions. Are there already existing validation frameworks that I can use.
I am looking for a solution for SQL Server 2005 and .net.
PS: I usually return custom error codes from Stored Procs and then parse them by looking at the xml file and then use it in my business layer rules engine.
a source to share
Embedding business logic in SQL Server can improve performance, but it complicates the design by breaking the separation of concerns. In order to have portable business logic, it must be in the business layer. I would remove the validation logic from the stored procedures and only use them to simplify CRUD operations. You never know when project stakeholders will say "do it on database X!" Do your best to maintain an independent database of validation logic.
a source to share