C# Application - Should I Use Stored Procedures or ADO.NET with C# Programming Techniques?

Question

I have a C# Application I am creating that stores all data in SQL Server.

Sometimes it's easier for me to make data changes programmatically and sometimes it's easier to have stored procedures and functions in the SQL Server database and call them.

I am rather new to programming and I don't know what the subtle advantages and/or disadvantages of each method are.

I figure I should probably be doing things one way or the other instead of using several methods.

My code is starting to look like a random collection of thoughts all just shoved into one spot. No offense to Joel Spolsky. I love his books.

This is a solo project, so I can refactor any amount of code I want to make it better. All options are on the table.

Thanks,

J3r3myK

Answer 1

Well, Stored Procs can be an additional level of abstraction or a set of functions/methods if you look at the database like an object or service. This can be beneficial, since you can hide underlying implementation details and change it when need be without breaking the app (as long as you leave the interface, e.g. the stored proc parameters, alone). Also, if you can hide your table details behind a set of stored procs, no matter how someone gets access to your database, they'll only be able to interact with it using the methods you designed and wrote for it --> there's less risk of someone firing up Excel and hacking into your database.

On the other hand, Stored Proc require extensive T-SQL knowledge and you'll be spreading your business and app logic across two sets of code bases, which can be a downside. Anything you can do in a stored proc can also be done in ADO.NET with straight SQL statements, and it's not even slower anymore.

A further plus for Stored Procs is the fact that if something is wrong in your proc, you can fix it merely by deploying the proc with the fix - you don't need to touch your C# app. This can be a great plus in a "hosted" environment if you only get 3 releases per year - applying a hotfix by means of fixing an Stored Proc can be your live-saver then! :-)

All in all: there are lots of pros and cons for or against stored procs; I'd suggest, if you feel comfortable with writing T-SQL, why not give it a try and see how it works for you. If it feels to cumbersome or too inflexible or like too much work in the long run, you can always switch back to using straight SQL in your C# app.

Just my $0.02. Marc