Error handling through SQL or C#.
How common is it to handle errors and enforce constraints through SQL, with transactions and stored procedures, instead of with C# code?
What is the best practice?
Is there any performance gain or loss by choosing one over the other?
6 Replies
I would say it's more common to enforce constraints at the DB layer through things like foreign key constraints, not stored procedures, but DB-level protections are very common in my experience. EF Core, the most popular ORM, implicitly uses a DB transaction for all its CRUD operations.
The best practice is to maintain constraints at both the database and the app (imo), since your app should be somewhat agnostic about the specifics of what database it's talking to. DB-level security is a final defense against bad data. If you can filter out bad requests in your app, you can also short-circuit database calls before they even happen which is good for performance.
Foreign key constraints and other DB-level protections do have a performance cost. I know that at very high scales, some companies drop all constraints and entirely rely on the app for data correctness. But I don't think that's relevant for the majority of projects.
@Becquerel Thanks for the amazing answer!
no problem 🙂
@becquerel I started thinking about this a couple days ago while building a feature for a school project. The idea was simple. I have a table of employees, a table of course assignments (shows what courses an employee teaches) and a course enrolment table (shows what courses a student has been enrolled to, this table contains information like course grade and which teacher set the grade etc etc). In the course enrolment table I wanted to make sure that only teachers that has been assigned to that particular course can set the grades for it. I solved the problem by creating a trigger that checks if the grade setter ID in the enrolment table matches the ID in the course assignment table. It works fine. The reason I choose to do it this way is because I felt like it would be extra steps to first get the data from the database, do the check through C# and then update the table if everything is correct.
that seems like a perfectly valid way to do it
though the extra work required to do that check in c# would not be significant, imo, and it might be nice to have that business requirement present in your code -- something you can check into source control, anyway
databases are often where documentation goes to die, in my experience
and five years later you end up with a lot of 'why was this even done?' questions
That is a valid point!