Author: Mark

I finally had some time to address some of the points in Frans post.

> Ad-hoc SQL Script is brittle

‘For Ad-hoc queries, you change the query’

Yes, and you’ve also got to recompile the application don’t forget to mention that

‘A component which generates the SQL on the fly at runtime’

Well this doesn’t really solve the problem does it – it just pushed problem to another layer. Now instead of updating my stored procedures I need to count on returning or executing additional code to dynamically generate the SQL. I’ve also now got more code on the stack that I have to call through to get my data vs. updating a sproc.

‘With a stored procedure this wouldn’t be possible.’

Why not? For sprocs I can control what fields are returned and I can also control new inputs by defining new parameters that can set default values.

> Security

‘Roles’

Frans is making very good points here and we are in agreement. Roles can be used to restrict users to certain tasks, inclusive of SELECT, INSERT, etc. permissions on the database. I tend to think in my limited view of web based applications. In many cases for web based applications you may only need a single user. But, yes, it would be better to define and apply a role for that user – incidentally something we do for managability on www.asp.net.

‘in most applications you have 2 roles’

Ever heard of role based security? Frans hasn’t (touché — sorry couldn’t resist 🙂 ). I think most applications tend to have more than 2 roles, I know mine do 🙂

‘SQL injection attacks due to the parameters’

Totally agree, you can use parameterized ad-hoc SQL to solve this problem. Never said you couldn’t in fact what I said was that stored procedures provide an effective counter-measure, not the only counter measure.

> Performance

‘Compiled’

So, I actually did some fact checking and talked to some people on the SQL Server team. Frans’ you may be interpreting the documentation incorrectly (or the documentation is wrong, I haven’t had a chance to dig through it yet). From what I’ve been told – again coming from the team that builds SQL Server – is that stored procedures are compiled if the plan is not in the cache. You negelet to mention is that subsequent uses of the stored procedure will use that plan and not compile. My data comes from someone who helped design several stored procedure for a large trading company (called millions of times/day) and the stored procedure was able to stay in the cache and was never re-compiled, quote: ‘performance that could never be duplicated with dynamic SQL’.

I was also enlighted that the view of the ‘data’ world affects the design. In a data warehouse/data mart view of the world I was told that dynamic SQL is probably the way to go where a bunch of unrelated sql statements need to be used. In OLTP systems or large data warehouse systems dyanmic SQL is ‘not even close’ and SPs are the way to go. For example, ad-hoc SQL for complex behaviors will be more resource intensive than stored procedures – take adding a post in the forums (all within a transaction):

1. SELECT – Determine if the forum is moderated?

2. SELECT – If the forum is moderated does the user’s post require moderation?

3. INSERT – Insert post

4. UPDATE – Update related posts with the new child post id

5. UPDATE – Threads table with information about new post

6. DELETE – Clean the table that tracks what posts a user has read

7. UPDATE – If the user is tracking the post, update that a new post has been added

8. Call another procedure to update statistics on the forums

9. UPDATE – Threads statistics

I also asked a friend of mine who had a lot of input on a fairly well known and large enterprise resource and planning software package when it was ported to SQL server. His opinion:

‘If the ad-hoc sql is a parameterized statement, then the perf will generally be similar. Otherwise the folks are incorrect – especially if the statement does anything complex such as a join, a sub-query, or anything interesting in the where clause AND the statement happens more than once.  This is to say nothing about other DBA issues such as securing objects, maintenance, abstraction, and reducing network traffic, troubleshooting, etc.’

My own conclusions:

• Both stored procedures and ad-hoc SQL have application maintainability issues. The maintainability is done either in the database or the data abstraction layer, i.e. update a stored procedure, recompile the application, or use an OR mapper. You need to determine where you want to solve this problem, with dynamic SQL it can mean recomilation of the application. With a stored procedure it means an update to the stored procedure.

• Both stored procedures and ad-hoc SQL can address security issues in a similar manner. If you are using ad-hoc SQL, use parameterized statements. Neither has specifc security advantages, although setting permissions only on stored procedures can be easier than individually setting permissions on a resource-by-resource basis.

• Both stored procedures and ad-hoc SQL can be used to solve the same problems and in some cases have equivalent performance. However, stored procedures are better for complex database behaviors such as paging or performing multiple actions either within a transaction or outside a transaction.

Thanks Frans, this has definitely proven to be a very valuable discussion! I think the ultimate conclusion is that there are several ways to solve data access problem and everyone should come to his or her own conclusions on what is right for the application. In some cases this is dynamic parameterized SQL (not string concatenation) in others it is stored procedures. However, my personal preference will be to use stored procedures for the majority of data access code, as I still believe the benefits still strongly outweight the costs.

I got a couple of emails WRT my comments on NIHS (from another fun post), and thought I’d share a few others I’ve heard of.

Ok, keep in mind this is supposed to be developer humor – hopefully no one will take it any other way 😉

I.A.R. (I am Always Right) – This malady usually turns up with developers that are defending a practice they believe to be correct but refuse to discuss other arguments. While curable, more severe contractions also lead to YAW.

Y.A.W. (You are Always Wrong) – A common affliction that forces the person to believe that anyone else’s opinion is always wrong. The patient will likely also be suffering from IAR. Contractions of both IAR and YAW almost invariably lead to AAA.

A.A.A. (Argue About Anything) – Common symptoms: never completely agree with anything anyone else says. Example, “The sky is blue” reply: “the sky only appears blue due to the way the sun is refracted through the molecules in the air”. While technically correct, symptoms of IAR and YAW are quite apparent.

N.I.H.S. (Not Invented Here Syndrome) – A terminal disease for software development projects. The inability to use software developed by anyone else is the most common symptom. Unqualified statements such as, “I’ll write my own because their solution sucks.” are usually the first tell-tale signs. It is important to note that there can be false positives, occasionaly there are situations where business critical software must be re-invented and re-written.

P.I.E. (Platform Independence Everywhere) – Although curable, this infection tends to have the developer write code that can be ported to anything/anywhere even though the primary system is already selected. Symptoms include the inability to use features that are specific to any particular platform even though those features can lead to better application performance. This is infection is usually accompanied by NIHS, example unqualified statement: “I’ll write my own platform independent version because their solution sucks, and who knows we may eventually port it back to an Atari 2600 — and we’ll be ready!”. False positives can be found, there are occasionally cases where it makes sense to plan for other system architectures.

C.A.N.A.L. (Code ANAL) – More like a flue; this malady can rapidly infect entire development teams. Statements such as, “Curly braces should go …” with check-in mail are clear clues to an infection. Persons infected will check-out entire source libraries to perform janitorial services on the code base forcing full unit retests and full recompilation of all sources for the sole purpose of ‘readability’.

A.B.M. (Anything But Microsoft) – Curable, but only with large doses of reality. Most obvious indicator: documents written by persons infected will always refer to Microsoft as ‘M$’. System administrators can check proxy servers for requests to a site known as Slash Dot topic 109 (M$) where infected persons can get their daily ‘fix’. Severe cases will lead to complete BS (see below).

B.S. (Bill Syndrome) also known as W.N.B.B – Pronounced ‘Wanna Be’ -(WaNt To Be Bill) – Terminal, no known cure. An advanced form of ABM. Primarily affects executives at software companies to constantly expouse F.U.D. about Microsoft.

Heard of others? Let me know and I’ll add to the list 🙂

Wow! It’s great to see discussion, that’s the best way to learn new things.

Frans makes some very valid counter-points. I’ll eat crow over my original posting for stating:

‘When stored procedures are used SQL Server can cache or pre-compile the ‘execution plan’ that it uses to execute the SQL vs. having to recalculate the execution plan on each request.‘

I’ve edited the original post to reflect the fact that SQL Server does not pre-compile the stored procedure. I was incorrectly using cache/pre-compile interchangeably and semantically this was incorrect, mea culpa.

I also edited the post to add a note on using parameterized ad-hoc SQL which is also an effective counter-measure to SQL Script injection attacks – as many people pointed out, and which I should have included originally.

At the end of the day it’s important to make your own decisions. However, I do find it somewhat interesting that the two biggest critics of the stored procedure approach build O/R mappers; I expected to get some feedback from Thona – who I am looking forward to eventually meeting some day! I do want to call out that O/R mappers are incredibly valuable technology solutions that can save a ton of time. I don’t have any personal experience with either Thona’s or Frans’ solutions, but I’m sure both can save a ton of dev-time – personally looking forward to replacing my stored procedures with CLR code in SQL Server ‘Yukon’. 

I did stumble across this while catching up this morning:

http://radio.weblogs.com/0101986/stories/2003/11/12/whenDidDynamicSqlGetSuchABadName.html

This benchmark is both invalid and valid – all depending on how you write your code. The stored procedure examples used both encapsulate checks on the parameters, while the ad-hoc SQL approach does not. A more valid approach (IMHO) would be to encapsulate either the ad-hoc SQL or the stored-proc call in a data access layer class. The data access layer (or a higher level business logic class) should validate the inputs – the validation should not occur in the stored procedure. In this case the sproc should look something like:

CREATE PROCEDURE GetOrders
 @sCustomerID nchar(5),
 @iEmployeeID int,
 @iShipperID int
AS
SELECT  *
FROM Orders
WHERE
 CustomerID= @sCustomerID AND
 EmployeeID= @iEmployeeID AND
 ShipVia= @iShipperID

In this case it’s pretty obvious that a sproc is now overkill. I’ve re-posted my original side-note from the first article:

Side-note: if you jumped to the conclusion that the point of all this is to advocate all business logic residing in stored procedures, you missed the point. There needs to be balance. My general rule of thumb is: return only the necessary data, no-more no less. The more efficient you can make your data access code by rapidly opening and closing the connection to the database the better; stored procedures provide you with an effective tool for accomplishing this. Performing business logic is frowned upon, i.e. transforming the data or performing calculations on the data. Use the power of the database to thresh the wheat from the chaff. Use your business logic to turn the wheat into dough 😉

Several people have missed the point . Stored procedures provide an effective way for accessing the data, but they are not the only way. But no matter which you choose neither should enforce business logic rules on the data.

In cases such as the above sproc it doesn’t take a rocket scientist to tell you that ad-hoc SQL is a better/simpler solution. However, take a more complex application such as the ASP.NET Forums used for solutions such as the Xbox forums. There are 472,000 posts in the Xbox forums Game Discussions forum. When viewing posts does it make sense to retrieve all 472K posts and then throw away all but the current 25 that you are viewing? Most likely not (if you didn’t guess, the correct answer is no). In this case a stored procedure is used to page the data in the database and only return the set of data that is applicable. If you think ad-hoc SQL is a better solution for this and to then filter the data in your DAL/BAL … please email me I’ve got some spectacular beach property in Montana that I’d like to sell you 😉

To state it one more time: there needs to be balance. Choose the solution that best solves the problem. If you take the attitude that one approach is always right one is always wrong, the only guarantee is that you’ll always be wrong.

Edit:
In the forums example, when stating the filtering of data, I don’t simply mean using a WHERE clause to reduce the set. Instead, the ability to selectivly choose a smaller subset from the set that SQL would create to satisify the original WHERE clause, i.e. when there are 1000 results returned but you now want to selectivly page through that set.

Found some new books awaiting me in my office that I can’t wait to dig into:

The Common Language Infastructure Annotated Standard, by Jim Miller and Susann Ragsdale. – In hard cover no less! I can’t wait to dig into this book, Nikhil even dropped by my office and said the same thing. Jim (one of the authors) is a software architect on the CLR team and just an all around nice (not to mention smart) guy. Should be an awesome book.

There was also a copy of Anders Hejlsberg’s, et al. new C# Programming Language book (inclusive of new “Whidbey” features).

Where did I put my highlighter…

At just about every talk I give I always try to make several consistent statements. One of which is: ‘Whenever possible use stored procedures to access your data’.

The following day I usually receive some email from people that attended the talk – it’s always great to hear from people and how they build their applications. Every once in a while I’ll get a comment such as: ‘I don’t use stored procedures for several reasons, one of which is that it ties me to a particular database’.  I just finished answering several such emails after our last couple of roadshows events and thought – great blog topic!

As soon as you put data in a database, whether its SQL Server, Oracle, or DB2, you just became tied to that particular database — surprised??? Sure, it’s just your data that is in there and you can use ANSI SQL for ‘generic/independent’ access to the data from your application. But claiming that ad-hoc SQL (SQL script embedded directly in your code) is the only way to accomplish database independence is simply incorrect. In fact there are many ways to efficiently solve this problem without creating such a brittle link between the application and the database.

One of the ways we solve this problem in ASP.NET “Whidbey” and the ASP.NET Forums (and now .Text and DotNetNuke too) is through the provider model pattern: a pluggable data access layer component that can easily be swapped out for other data access layer components. The difference is we do everything possible to take advantage of the database we’re running on. This means ensuring that all data access is through stored procedures and that the stored procedures return data to the business logic layer with no wasted cycles and no wasted data, i.e. sort the data in the database and only return records which will be used, no-more no-less.

There are several reasons for using stored procedures to access data, below I’ve attempted to capture the 3 that I always reference:

1. Ad-hoc SQL Script is brittle

You would never put business logic code in your presentation tier, right? Why put ad-hoc SQL in your business logic layer? Embedded SQL script is very brittle; small changes to the database can have severe impacts on the application. Whereas stored procedures afford abstraction between the data and the business logic layer. The data model can be dramatically changed, but the stored procedures can still return identical data.

2. Security

What permissions are required for working with your data? If embedded SQL is used it likely means that the application can execute any INSERT, UPDATE, DELETE, SELECT script it desires. You wouldn’t – hopefully you don’t – run as the administrator (sa) account on your server, so why allow full access to the database itself?

Stored procedures allow for better data protection by controlling how the data is accessed. By granting a database login EXECUTE permissions on stored procedures you can specify limited actions to the application. Additionally, stored procedures are a counter-measure to dangerous SQL Script injection attacks, a susceptibility that applications using embedded SQL are more vulnerable to.

3. Performance

A counter argument for performing data manipulation in a stored procedure, vs. simply retrieving the desired records in a SELECT statement, is it slows SQL Server down. That’s not exactly correct. In many cases you can get better performance by looping and filtering data in SQL Server than you could performing the same loops and filters in the Data Access Layer – databases are intrinsically designed to do this, while you and I have to write our own code (which do you think is going to be faster?). It is, however, important to understand how SQL Server uses indexes and clustered indexes. I’d recommend SQL Server 2000 Performance and Optimization Tuning to learn more about this and other SQL Server performance guidelines.

Beyond sorting and filtering data in the stored procedures you can also batch common work together or retrieve multiple sets of data. For example, retrieve some data, update a datetime stamp, and then insert a new record. If you were to execute these 3 tasks once a second as ad-hoc SQL this would result in 259,200/day independent database request vs. 86,400/day if all were encapsulated in a stored procedure. That’s 172,800 database connections and network IO usages that you no longer require! Consolidating work through stored procedures makes more effective use of a connection (and your system).

There are also internal performance benefits to SQL Server for using stored procedures vs. ad-hoc SQL script. When stored procedures are used SQL Server can cache the ‘execution plan’ that it uses to execute the SQL vs. having to recalculate the execution plan on each request – would you recompile your business logic class on each request? …probably not. While small details such as this appear to be trivial, when you start considering total requests/second that your SQL Server is serving, small performance improvements to repetitive code paths add up very quickly and make your server more efficient and scalable.

It’s also worth mentioning that the easiest way to get performance out of your database is to do everything you can to take advantage of the platform you are running on. For example, if I were to write an application that used a DB2 database, I would do everything I could to get the maximum performance out of that system vs. trying to write generic data access code. Why? Well the SQL Server team here at Microsoft has some of the best minds in the industry making sure SQL Server has phenomenal performance and scalability – it would be a shame not to take advantage of all that effort, right? We can assume that other database companies hire smart people for their products too — don’t get striken by N.I.H.S. (Not Invented Here S< /STRONG>yndrome) a dreaded&n
bsp;software developer disease where nothing is good unless you wrote every line of code!

Side-note: if you jumped to the conclusion that the point of all this is to advocate all business logic residing in stored procedures, you missed the point. There needs to be balance. My general rule of thumb is: return only the necessary data, no-more no less. The more efficient you can make your data access code by rapidly opening and closing the connection to the database the better; stored procedures provide you with an effective tool for accomplishing this. Performing business logic is frowned upon, i.e. transforming the data or performing calculations on the data. Use the power of the database to thresh the wheat from the chaff. Use your business logic to turn the wheat into dough 😉

To follow this discussion in whole:

Read: this, this, and this.

[listening to: John Mayer, Comfortable]

Edit: 11/18

A couple of people have very correctly pointed out two things:

1. I stated that SQL can ‘pre-compile’ the execution plan. I was using ‘pre-compile’ interchangably with ‘cache’ (the original statement said ‘pre-compile or cache’. SQL Server does not have the ability to pre-compile a stored procedure – I edited the article to remove the work ‘pre-compile’ as it can be misleading.

2. Stored procedures are not the only defense against SQL Script injection attacks. Parameterized ad-hoc SQL works just as well, although stated in the original post.

Edit 11/19

Added details about entire conversation

I just read an interesting critisim of some of the feature in ASP.NET “Whidbey“.  I posted a reply that I wanted to capture in my own blog with some additional thoughts:

There were really two parts to the critisim: The first had to do with class naming and the author’s dislike of the class names we chose (such as Roles and MembershipUser). The author makes some good points about naming choices, i.e. why we chose Roles instead of groups and why we have a MembershipUser class instead of a User class. The roles vs. groups argument can fall either way, we chose to go with roles since the ASP.NET V1.X feature was called ‘Role based Security’. It would be a little confusing if we had instead called it ‘Group based security’. Secondly, why not call the MembershipUser object User. Initially we did, but there also happens to be another class ‘User’ that already exists. Had we called our Membership User ‘User’ a fully qualified type name would have been needed for any references to it — yuck!

The critisim, however, is interesting (and valid) because we had all the exact same discussions on the feature team <g> – we obviously reached different conclusions though. Someday maybe we can start publishing our design documents (Membership is about 70pp and Roles is about 60pp) where we capture some of these issues and in some cases, reasons for design changes.

There was also some feedback within the post about the overall object oriented design of the system. For example, why not cut features such as Membership into many sub-interfaces instead of having one large interface; or why have methods that all data stores can’t support, such as GetPassword() — side note, we’re moving to an abstract base class model in the beta.

In any implementation there is always a trade-off between functionality/usability and pure object-oriented design. This is a philisophical debate for how software should be built. There are two sides: 

1. Pure object-oriented design – the design should be sufficiently complex as to allow/control for any changes to keep the design ‘pure’. For example, dividing the Membership functionality of ASP.NET into many smaller interfaces where each interface could control a specific set of behaviors, e.g. IValidateUser, IPasswordManagement, IUser, IEncryptRoutines, etc. The developer then chooses which interfaces are most appropriate for the task and only implements those. Positives: the software can be infinitly customized. Negatives: sheer complexity. The emphasis in this philosophy is on the developer writing the system, not the developer using the system.

2. Designing for usability – the design should focus on simplicity and usability. For example, in Membership rather than having many interfaces for expressing behaviors there is one interface. Rather than having complete control over all the APIs, APIs are added that all data providers may not support. Positives: the software can be customized with less code. Negatives: behaviors are not segmented in an oo manner. The emphasis in this philosophy is on the end-user, i.e. who is using it and what are their common tasks.

The obvious trade-offs here are complexitity vs. simplicity. I’ve personally maintained that the simpler and more understandable the software is the less bugs and support problems you will have and more people will use it because they can actually understand it! Furthermore, the pure object-oriented philosophy is somewhat elitist — it places emphasis on who builds the software not on who uses the software, i.e. only the person who built it trully understands how it works due to the complexity. Our goal is to provide a model to address the problems of the majority – who will never extend our base functionality while still allowig the more advanced developers to extend our design, hence the ‘provider design pattern’.

What are your thoughts and what philosophy do you apply to building software?

P.S., drop by and say hi if you are attending the Bay Area .NET User’s group tonight  — I’ll be presenting on ASP.NET “Whidbey”.

ASP.NET already has many great extensibility points: you can add new HttpModules to interact with an incoming or outgoing request, you can add your own HttpHandler to take over the processing of individual requests, you can extend the configuration system, and finally you can derive from many of our server controls to add your own specific behaviors. In ASP.NET “Whidbey” we’re introducing a new extensiblity point: Providers.

The concept of a provider is something we initially started experimenting with in the ASP.NET Forums. The idea that the data layer of the application, for example all the code specific to SQL Server, would exist in a separate assembly that could be loaded by the running application. This allowed for developers to write their own data layers and simply plug it in (note, this first data layer abstraction concept was from Scott Mitchell). With ASP.NET “Whidbey” we’re taking it to the next level. Many of the new features — Membership, Personalization, Role Manager, Site Navigation, Build Providers, Health Monitoring, etc. — now support a provider model. However, rather than being only a data layer abstraction the provider model in ASP.NET “Whidbey” is both a data and business logic layer abstraction.

What this means to ASP.NET developers is that you can completely unplug the logic/behavior/data interaction of a particular feature of ASP.NET and replace it with your own logic/data layer. A great example is the new Membership system for managing user credentials. Membership provides an easy way for you to validate a user’s credentials:

bool Membership.ValidateUser(username, password);

Internally this call to ValidateUser is being forwarded to a configured provider. For example, the SQL Server Membership provider:

public class MembershipSqlProvider : MembershipProviderBase {

  override public bool ValidateUser (string username, string password) { … }

}

The provider selection is determined by settings in the web.config (or defaults in the machine.config) file. Both the ASP.NET Forums and Dot Net Nuke projects already implement this new provider model — in case you’d like to see examples of how this system works. Below is sample configuration data from the web.config of the ASP.NET Forums to configure a provider:

<forums defaultProvider=”SqlForumsProvider” defaultLanguage=”en-en” >

  <providers>

      <clear/>

      <add name = “SqlForumsProvider” 
           type = “AspNetForums.Data.SqlDataProvider, AspNetForums.SqlDataProvider” 
           connectionString = “[hidden]”
           databaseOwner = “dbo” />

      <add name = “AccessForumsProvider” 
           type = “AspNetForums.Data.AccessDataProvider, AspNetForums.AccessDataProvider” 
           fileLocation = “~\data\AccessDataProvider\AspNetForums.mdb” />

    </providers>

</forums>

The defaultProvider attribute in the <forums /> element instructs the forums application as to which provider (in the <providers /> section) is the default of the application. The <providers /> section is then used to add (or remove) providers that are available to the application. In the above example, the provider is ‘SqlForumsProvider’ (since we haven’t completed the Access provider yet) and the application then loads and uses this provider for any data interactions.

As you can see, providers are very powerful new feature of ASP.NET. One of the huge wins for everyone is that there is no data model your are locked into. Yes, we’ll provide a great data model for many of our features, but if you’ve already got a data model or use data from other sources — such as an Oracle or DB2 database — you can still easily integrate it with ASP.NET and take advantage of all of the API education we’re putting in place for these features.

In fact, we’re planning on writing some sample providers for both Oracle and DB2 that we’ll post the source code for. If you’d like to learn more about the provider feature, check out the Membership and Role Management or Personalization talks from the PDC. You can access the powerpoints and demos at http://www.asp.net/whidbey/.

P.S., lots of people have been asking where they can get the ASP.NET “Whidbey” alpha from. Unfortunately the Alpha is limited to PDC attendees and MSDN Universal subscribers. If you’re an MSDN Universal Subscriber, you should be able to call an order a copy of the “Whidbey” alpha.

We kicked off the ASP.NET Roadshow last night in Dallas, Texas with a packed house (almost 300 people). We started with a brief introduction to ASP.NET, did some cool Tips/Tricks demos — like building a ‘Page Loading…’ example and a dynamic image generator. After a short-break we talked about about some security best practices, specifically around SQL script injection attacks and cross-site scripting attacks (XSS) — lot’s of people probably went home last night and tried some of these techniques on their own sites! Finally we wrapped up with about a 45 minute overview of ASP.NET “Whidbey”.

Check out www.asp.net/roadshow/ to see if we’re coming to your city — depending upon the success of the U.S. event we’ll also look into some European and Asia/Pacific events too (any excuse to finally go to Australia!!).

Special thanks to Brian Moore (*the* Microsoft Developer Evangelist) and the local user group leaders Stephen Swienton (Fort Worth .NET User’s Group) and Keven Markham (Dallas .NET User’s Group) — G. Andrew Duthie will be presenting at the Fort Worth .NET User’s Group next month on ASP.NET “Whidbey”. And of course INETA who has helped coordinate and organize all the events!

Today we had the opportunity to present at Borland’s Developer Conference in San Jose, CA.

David Treadwell gave the keynote for today’s general session, and I provided an ASP.NET “Whidbey” demo. In the keynote we build a demo from scratch that used the new SqlDataSource control, added paging/sorting, used a theme, used a masterpage, and finally supported database cache invalidation — all of this in less than 10 minutes with 0 lines of procedural code. This morning I had the chance to be introduced to Borland’s CEO, I wasn’t sure if he was in attendance at the general session – but it’s great to see the commitment that Borland has to the .NET Framework.

Tomorrow we kick off our ASP.NET RoadShow if you get a chance please sign-up. We’ll spend at least 1/3 of the time talking about ASP.NET “Whidbey”.

Here’s a common scenario:

You’ve built a dynamic web site with ASP.NET; for example: the Commerce Stater Kit. You have a products page that displays detail (from the database) about a requested product specified by a query string parameter, e.g. /products/default.aspx?ProdID=5. A page such as this is usually a great candidate for the ASP.NET Output Cache using the declarative OutputCache directive:

<%@ outputcache duration=”3600″ varybyparam=”ProdID” %>

If you’ve used ASP.NET, you’re likely very familar with the output cache and the concept of dependencies. In the code sample above, a time based dependency is used to control how long a particular page can remain in the cache before being removed from the cache. Specifically, the above directive states: cache the output generated by the page in memory for 1 hour varying the entries by the ProdID query string or post-parameter.

There is a common problem here … what if a manager wanted to change the price of a product from $10 to $8. In the current model you would either have to manually evict the page from cache using the Response.RemoveOutputCacheItem or wait until the hour expired before the update would occur automatically.

To address this problem we’ve added support for database cache dependencies in ASP.NET “Whidbey”, i.e. when the data changes in the database remove the item from the ASP.NET cache. We support this for SQL Server 7, 2000, and “Yukon”. The support for SQL 7 and 2000 is a table level change notification, i.e. anything in the table changes and we notify you. The SQL “Yukon” model is granular enough to alert us when the results from a stored procedure change.

For SQL 7 and 2000 it’s as simple as:

<%@ outputcache duration=”3600″ varybyparam=”none” sqldependency=”MyDatabase:Authors” %>

This new sqldependency allows you to name a database entry from web.config (that’s the ‘MyDatabase’ part) as well as the table to watch for changes: Authors. For SQL Server “Yukon“ it’s even easier — in fact support is built directly into the SqlCommand class.

Below is a sample using the OutputCache API with SQL Server “Yukon“:

Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Response.Write(“Page generated: ” + DateTime.Now.ToString(“r”))
Dim connection As New SqlConnection(ConfigurationSettings.ConnectionStrings(“MyConnectionString”))
Dim command As New SqlCommand(“SELECT * FROM Products”, connection)
Dim sqlDependency As New SqlCacheDependency(command)

connection.Open()

GridView1.DataSource = command.ExecuteReader()
GridView1.DataBind()

connection.Close()

Response.AddCacheDependency(sqlDependency)
Response.Cache.SetValidUntilExpires(True)
Response.Cache.SetExpires(DateTime.Now.AddMinutes(60))
Response.Cache.SetCacheability(HttpCacheability.Public)

End Sub

In the above code snip you can see the SqlCommand object being passed to the SqlCacheDependency constructor.

The other really cool part is that we’ve also added the ability for you to write your own ‘custom’ cache dependencies. So if you want, for example, to support row level change notification for SQL 7 or 2000 — you can now do it by deriving from CacheDependency. For example:

public class StockPriceCacheDependency: CacheDependency { … }

… and then in your page code:

Dim dep As New StockPriceCacheDependency(“MSFT“, 5)

Cache.Insert(“MSFT“, dep.StockPrice, dep)< /P>

In the above code, whenever the stock price of Microsoft — or other stock — changes the cache dependency would force the evicition of the data from the application using the custom ‘StockPriceCacheDependency’ (I’ll do another blog entry about this in more detail).

So armed with the ability to create custom cache dependencies you could built depdencies for the AS/400 server, an Oracle database or any other type of dependency you can think of.

If you would like to learn more, come to the PDC WS300 “Advanced Caching Techniques” on either Tuesday or Wednesday. You can also download the slides and demos from www.asp.net/whidbey/.  I’ll also blog about this as well as other new ASP.NET “Whidbey” features more over the coming weeks.