Best practice for handling record validation in database RRS feed

  • Question





    Im looking for tips on how to implement a solution where the database will handle most (or preferably) all record validation when editing records in our web application.


    The idea is that wheter the user wants to do an insert, update or delete we wouldnt have to do a lot of testing and searching in the database separatly first to see if what the user wants to do is allowed, but instead just run the stored procedure and then translate all not null -, constraint -, unique index -, foregin key - violations and trigger erros into proper errormessages to the user.


    We have a lot of dependencies in the database where items are not allowed to be updated, deleted or even inserted under certain conditions. And the proper way would of course be to implement all of these rules into the database. And when they are in place it feels like a lot of extra unnessecary work to run separate checks in the application to see if what the user wants to do is allowed. But of course we want to display nice specific error-messages to the user.


    Is there anybody else that have implemented a solution like this?

    Or have you thought about it and abandoned it for some reasons.

    Or maybe you just have some input for us :-)


    The application is a Asp.net application using C# and the databse is Sql Server 2005.





    (I dont know where this issue belongs so I have posted both here and in the Sql Server forum. But actually I think its more of a C#-issue since most of the implementation will be realized in the c#-code.)
    Tuesday, November 27, 2007 10:24 AM

All replies

  • If you ask about does SQL Server is the place to put validation logic, why not, by the way SQL Server have infrastructure for storring configured messages (mulitilanguage support also) that can be used. You will raise preconfigured messages from stored procedures and triggers for which you need to create error handling mechanizm in your application source.
    Check the help for stored procedure "sp_addmessage" and you will know pretty much all about.

    Tuesday, November 27, 2007 4:06 PM