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

Answer 2

Well, you're going to end up using ADO.NET either way... it sounds like you mean stored procedures vs command-text. There are advantages of both, unfortunately, so it isn't a simple question, however, if you are looking at tools like LINQ, there are more advantages to command-text, since it can be more "composable" (i.e. the caller can add Skip/Take/Where/OrderBy and have it impact the overall query).

Another option that mixes the benefits of both is table-valued-functions (via UDFs) - they also have the advantage of stronger metadata (it is hard to accurately query the schema of a stored-procedure).

Any approach should be injection-safe as long as you correctly use parameters.

Answer 3

Allow me to rephrase that: should you be pulling down the data, modifying it in code, and then having an SP update the DB with the new values, or should you just call the SP as a "function".
My point of emphasis is, even if you do the data manipulations in code, you should still only be accessing SPs on the db.

Whether you do that, or have more complex SPs do the data manipulation, would depend on several factors:

• your level of comfort with complex SQL (as opposed to C#)
• how much data you need to pull down and modify - there may be performance aspects (it costs to pull all that data to the code)
• connected to the previous one, your architecture may come into play - e.g. are you accessing a local db server, or remote
• What kind of manipulations you need to do, it may be so complex you dont want to muddy your sql
• What kind of business logic you need to enforce, what is the data dependent on, etc.

It really comes down to a judgment call, there is no right way (though there are many wrong ways...). Though it is important to remain consistent, it is perfectly acceptable to have different types of db access in your system, as long as the context/need justifies it.
I would say choose whichever you're most comfortable with, and stick to that - until you have a reason to do it differently. No reason to be dogmatic about it...

Answer 4

I would recommend following one convention or the other (either have your SQL code as strings in your classes or use stored procedures). There are countless debates about this, but there really isn't any important reasons to choose one or the other.

Answer 5

Two points that haven't been covered yet:

SProcs can be placed in a schema, and impersonate a different schema. This means that you can lock down the database access and give users permissions ONLY to execute your SProcs, not to select update etc, which is a very nice security bonus.

Secondly, the sproc can be SIGNIFICANTLY faster depending on what operations your doing. If your constrained by network IO and your able to perform set operations on your data, doing so in a single stored procedure instead of doing a fetch of the ENTIRE dataset, processing it, then transmitting the change will give you a nice speed increase.

Same goes for transactions, if you need to perform operations serially (i.e change a value in two tables at once which involves some logic) then you will need to hold a transaction for the time it takes for you to compute the value and transmit back in two SQL queries, whereas the stored procedure can do it in a single procedure.