I Still Say Use Stored Procedures!
First, wow, I never ever dreamed pushing that publish button on something I wrote in about 20 minutes would get so much play! Thanks for all who shared their opinions, I enjoyed them all. Please feel free to share more, I think this is such a good discussion to have.
But seriously you will not find me breaking from my stances. I will write more detailed Blogs over the next month or so on my other points. I really want to collect some really good content to demonstrate what I am talking about. Once I do, I think many of you will understand better.
I want to go over the Ad-Hoc SQL (Yuck) versus Stored Procedures argument. First I guess things may be worse than I ever thought. I used to use ad-hoc queries, I really did. Then I stopped supporting MS Access for my back-end and just focused on SQL Server for my data store.
I know, I know, I could use ad-hoc queries for about 95% of my SQL. And no I most likely would not notice any sort of performance gains or loss either way. I tend to keep my sprocs to basic CRUD, but do find situations where more complicated queries need to be in the database, such as returning all locations within 25 miles of a point. But I do put much more of the real business logic in my application code, but frown on hold large sets of data in my applications to filter and sort.
I do however feel I would create a much harder to maintain application. I try so hard not to hard code strings in my application and an ad-hoc query is just that. From a maintenance point of view I can change a stored procedure real quick if I need to, without building my app and deploying it all over again. In fact I most likely would never have to touch the application code for many of these changes.
So where does my frustration come from, well it is the way things are taught. We have way too many examples handed to us as new .NET programmers with ad-hoc queries. My peer Craig Utley shared this thought with me on the topic:
First, when learning .NET, all the examples show in-line SQL in the application. So it's a learned behavior from the start, which is of course the hardest thing to unlearn.
I see a lot of development environments these days. And I see a lot of good things, but way too many bad things that make .NET applications get a poor reputation. Most of this comes from a poor introduction in the initial tutorials we are offered. I admit it hurt me for the first few years with ASP.NET as my framework of choice. I sort of resent it. I wish I would have been challenged more to get rid of the tee and learn to hit a real curve ball. Don't candy coat things for me, when I need to really know how to accomplish something.
Another peer Itzik Ben-Gan shared his opinion on why developers fall on one side of the fence or the other:
People like to use tools they have more control over. DBAs and Database Developers have control over the database side and are comfortable with stored procedures. Developers don’t want to be at the mercy of the database folks, and require confirmation for every move, which is more of a case when using stored procedures.
I think this has a lot to do with it as well. I have been places where it takes an act of congress to get a DBA to execute a simple script update for you on the production database server. But I have also been in places where the DBA will under no circumstances allow you to have direct permissions to any of their tables. It is a security thing and we all know how DBAs guard their databases. So sometimes you will not even have the permissions to use ad-hoc queries. Sometimes you will and the ability to update stored procedures is just not going to be an option, so I can see running with ad-hoc queries.
Now if I were to use ad-hoc queries how would I do it? Well I would abstract it down to a single point of failure and maintenance, as close to the database as I could get it. Andrew was good enough to point us in the right direction with suggesting an ORM, and not to mention he is onto how to page a repeater. You should have a good n-tier architecture in place for your applications.
How do I do it, well I just use the database, with stored procedures with a data access layer above it. My stored procedures generally are not terribly complicated, 95% do not have to be more than just simple basic CRUD, let's face that fact. So having a good data access layer is key.
In this layer I have an abstracted class to manage interactions with the database. I seriously have had this written and baked for years and it abstracts me from really caring about how to get a dataset or a repeater and I am fine with that. I have my code written and it works great, so no if you asked me to write how to do that boring mundane code at this point I would not know where to start. I would write something like this (and this is some real code from one of my sites):
Public Function GetBonusTypes(ByVal BonusType As Integer) As BonusTypesInfo
Dim dr As New SafeDataReader(ExecuteReader("spGetBonusTypes", _
CommandType.StoredProcedure, "@BonusType", BonusType))
Dim si As BonusTypesInfo = Nothing
If dr.Read Then
si = BindBonusTypesInfo(dr)
End If
dr.Close()
Return si
End Function
Oh and I use the CSLA SafeDataReader class for a datareader and I highly recommend using it instead of the provided SQLDataReader. But that is another story for another day!
The next layer is the business layer and here I do perform many of my data manipulations, but again for the most part they are not typically complex. I have found that if I have to make them fairly complex a stored procedure is a much better way to go. Let's face it the database is optimized to manipulate large sets of data fast. Therefor enter the stored procedure for sure.
The next layer is the user interface, which is where we get into the argument against the Views, and like I stated that will be covered in the near future.
I am not going to get into doing some sort of performance tests or things like that. What gripes me is more of a style of programming. I like having parts that perform specific tasks, making it, in my opinion, easier to maintain once the application is alive and during the initial run-up to production.
I wanted to glean some good quote to Adam Machanic's article, but there are a lot of great things in it about stored procedures and the API approach I think you should just check it out.
Now onto things that I honestly have not really had a good opportunity to start playing with LINQ and Data Entities. I know all about LINQ to SQL, and I really like all the LINQ extensions we are seeing. But the more I examine things I really think the strength will be applying LINQ to Objects, which is where I see myself going pretty quick. I just need some more time (if you can tell me where to buy some send me the link). Data Entities has not officially been released and I am still reading about it, so I will leave that for later too.
Finally, SQL-Injection. Many of you pointed out you should use parameterized queries. Absolutely you should, but face it many programmers and you know who you are, do not. There are way too many examples of this type of bad programming:
"Select * from Customers where CustomerId = " & CustId
Oh so bad and I see so much of it in person and posted on the ASP.NET forums. Right now I think I am too tired to cover parameterized queries. So another Blog is in the works! I am sure there are plenty out there already.
So to leave on a funny note, some of my peers offered up the ultimate stored procedure (take it in good humor!):
CREATE PROC p_FireThisDeveloper @IamStupid varchar(8000)
AS
exec (@IamStupid)
Again thanks for all who commented and sharing your opinions. Keep it up, I really enjoyed each of the comments. This was a good banter and I hope we all learn and grow from it.