Jeff and .NET

The .NET musings of Jeff Putz

Sponsors

News

My Sites

Archives

Not every website is Match.com

In my post yesterday there were some interesting comments about the code sample I posted regarding the use of StringBuilder vs. straight concatenation. Yes, I created a ridiculous scenario that would probably not ever occur in real life, but the intention was to illustrate a point.

David Hayden made the comment that, "Not every website is Match.com." I totally agree with that, and I especially take issue with the thing about stored procedures always being "right." The code sample is for my book, and it comes with a liberal disclaimer that such a situation is fairly extreme. (And oh yeah, I argue against the use of sprocs too as the "right" thing.)

That's one of the hardest things to confront when you're trying to teach. Do you go with the ultimate best practice when there's an easier way that doesn't perform as well but is still adequate? I've never used any kind of drag-and-drop data access because I fear for its performance, but clearly it's adequate for someone out there or Microsoft wouldn't keep including it.

Posted: Aug 16 2004, 03:31 PM by Jeff | with 10 comment(s)
Filed under:

Comments

Brian said:

What would you say is a situation where sprocs would be the "wrong" thing, and what would the alternative be (or have you covered this on your blog before?)?

# August 16, 2004 3:48 PM

Richard Dudley said:

I can think of one instance. if I were writing an assembly--say a DNN module--that would be used with one of several databases, I would be inclined to write a single data provider that used parameterized queries. This way, one DP could be used with MySQL, Access and SQL Server. No changes would be necessary other than a connection string in the .config file.

There are certainly efficiencies to be gained by using the special classes for SQL Server and sprocs. But, parameterized queries would provice much of the defense against SQL injection that sprocs do, and I could spend the time I save writing another assembly.
# August 16, 2004 4:20 PM

Brian said:

Thats a good point. Are there any reasons not to use sprocs in a SQL Server-only environment?
# August 16, 2004 4:24 PM

Jeff said:

I think you ask the wrong question. What's a good reason, other than preference? If I have an app that queries "SELECT Name FROM People WHERE ID = @ID" all day long, there is no measurable benefit to using a stored procedure.
# August 16, 2004 4:35 PM

rick said:

There's no hard rule for anything. Stored procedures are great unless you want highly dynamic queries or database independance. But, if you have an operation that includes multiple queries, I would much rather put that in a procedure.

Personally, I don't go for the database independant thing, I'd rather code my app to use the provider to its best abilities. I rather like .Net 2.0's DataSource controls.

The best thing is to evaluate your options and pick what's best for what you're doing.
# August 16, 2004 6:14 PM

Jon said:

One reason to use stored procs is to lock down the talbes in the database. You could deny INSERT, UPDATE, DELETE for a table and only grant EXEC to a few stored procs to handle inserting, updating, or deleting. This may seem paranoid, but in a production environment it would not be unreasonable.
# August 16, 2004 7:30 PM

Jeff said:

Paranoid is right. Seriously, if someone is clever enough to break into your system (particularly in a Web environment) to start executing whatever code, SQL security is the least of your worries.
# August 16, 2004 7:39 PM

Jon said:

It is called defense in depth and it can buy you some time if your system is ever compromised. In some cases I would rather err on the side of caution - particularly if the data is sensitive.
# August 16, 2004 7:54 PM

Eric Newton said:

Jon: its paranoid to lock down every single table that way, although I'm not saying you specifically said that. Granted, there might be a "CreditCardTransactions" table that is highly sensitive, and in that case, you should only need an "INSERT" procedure... but all in all, you probably should be encrypting the data, storing it as varbinary so its also not recognizable on the wire, etc etc etc.

I believe in creating a sproc for a complex query, or one with a lot of parameters. Then the additional pain of versioning the sproc's interface is outweighed by the potential benefit of efficiency and speed.

Anybody who tells me you should only use sprocs for data access is gonna get a one finger salute from me.
# August 18, 2004 12:49 PM

Jon said:

Nice to know you're a flexible person. Anyway, I was not advocating the use of stored procs for data access to every table. Just because you have a hammer shouldn't make everything look like a nail. In design there are always trade-offs and I just wanted to point out that *sometimes* using stored procs for data access is the way to go.
# August 19, 2004 12:35 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)