Are we still talking about Stored Procedures vs. Dynamic SQL?

Rob Howard and Frans Bouma still are. And I guess, I am now too. Let's summarize a few of the facts from these counter points:

  • Any form of pre-compilation or cached query plan arguments are moot betweem SQL and Procs. Rob has some outdated information and Frans corrects that in his post.
  • Stored Procedures can offer the perf benefits if they are designed properly that Rob claims by avoiding round trips and unncessarily data transfer when trying to get computed or aggregated data out of the database.
  • Both are susceptible to SQL Injection attacks if the SQL is concatenated with parm values.

Let's talk about security. Frans thinks that Role Based security is the way to get fine grained security in your database while using embedded or dynamic SQL. Frans's solution of adding users and roles in the database is a dated technique back to client server 2 tier systems. Web-based or other wise distributed applications typically have a connection pool - and unless you are going to have a connection pool for each role, then you can't rely on SQL Server based role based security to be your cop. Frans goes on to talk about how views can be used to encapsulate security rules just like a stored procedure.

Both Frans and Rob talk about the brittleness of SQL with regards to schema changes. Rob thinks your SQL centralization/encapsulation  should occurr inside of stored procedures. Frans think you should do this in a data access component that is part of your application. Frans hasn't really explained what his application's component does specifically but it sounds like he prefers to dynamically create the SQL on the fly by reflecting on schema of entities in his application.

What both of them has avoided is any realization that talking to a SQL Server database is the same problem as talking to any external service. Whose responsibility is it to provide the encapsulation and deep understanding of the underlying database schema. The answer to that question can't be answer universally. Back in May 2005, I blogged about the notion of DatabaseAsService.

Is your database a shared service between several applications? Some folks might even go as far as to say that their database is an enterprise service. Especially in this case it makes perfect sense to encapsulate complex internal schematics inside of the single shared resource the database. This can be done with Stored Procedures or Views, but do you really want each application to have intimate knowledge of deep schema details? That's brittle way beyond the scope of a single application.

In other cases, your database is more like a file that your application persists its data and it is not a shared resource. In these cases, the database is not really a service in terms of Service Oriented Architecture principles. In fact, I'd go as far to argue in these cases that the db is such an intimate part of your application's design that there should be no “mapping“ of schema inside/outside of the database and that they could/should be the same. Go ahead and make the full set of tables/schema public to your application logic.


Class design considerations for extension methods and anonymous types

One of my readers was watching the DNRTV episode I did on LINQ recently and had this question: 

At some point, when you're explaining object initializers and anonymous types, you say something regarding extension methods, like how they could be used with the anonymous types. I'm not sure how that'd work: if the anonymous type gets named dynamically as something like "<Projection>F__4", and if the extension is declared during compile time as something like "method (this type)", how can we extend the dynamic class?

He is correct in pointing out the difficulty. These projection or anonymous types have dynamically declared names and when you declare an extension method, you must specify the type after "this" in the argument clause.

One thing that is not obvious is that the type specified after “this” in an extension method doesn't have to be the exact type, but can be an ancestor or interface implemented by the type you are ultimately wishing to extend.

public static float GetArea(this IPolygon shape){...}

As an example, the above extension method could be used as an extension method over anything that implements IPolygon.

The downside is that anonymous types (and linq projections) inherit directly from object and I don't expect that they will implement any special interfaces (the goal is too keep them simple for C# 3.0). What are you left to do? Create extension methods on "object". That is certainly a theoretical option I suppose, but that seems a little bit extreme.

To be honest though, try to think of real cases when you'd want to extend an anonymous type. If the type is truly anonymous, you know absolutely nothing about it, and what assumptions can you really make about it in an extension method? Truly, in some cases, you are going to choose to implement a named type instead of an anonymous type, and it appears that we'll see refactoring support in the tools to promote an anonymous type to a real type. This is a very likely scenario.

The reasonable cases that I can think of where anonymous types are the preference (i.e. I have no burning need to have a named type) but still need (and can) extend them, is when they are used in the context of a collection or another generic type.

For a good example of that, let's take a look at some of the extension methods provided by Linq itself.

  public static class Sequence {

    public static IEnumerable<T> Where<T>(

             this IEnumerable<T> source,

                  Func<T, bool> predicate) {

      foreach (T item in source)

        if (predicate(item))

          yield return item;



Consider that this is an Extension method for anything IEnumerable<T>. In this case, we're using this extension method against an IEnumerable collection of type <T> - a generic. That generic type could be an anonymous type. But the important information here is that we know something more about the anonymous type here and that is that it's used inside of an Enumerable collection, and hence we can provide the value of iterating through it in the foreach, evaluating some criteria, and yielding the items that pass the criteria into another collection.

Speaking at Chicago .NET Users Group in Downers Grove on March 15th

I'll be speaking at the Chicago .NET Users Group in Downers Grove on March 15th

Stay tuned for details....


The Code Room: Episode 3 - Breaking into Las Vegas

It's the good guys vs. the bad guys, fighting over millions of dollars. Could this happen to you? Maybe it already has.

Visual Studio Team System and Team Foundation Server Seminar across Canada

In November, 2005, Microsoft launched Visual Studio 2005, a major new release of its developer tools environment, and the foundation of Microsoft platform development for the next few years. In addition, this marked our entry into the lifecycle tools market, with the addition of Visual Studio Team System and the Team Foundation Server, to support end-to-end system development scenarios. We are bringing the tools we have been using for years internally to market to help address customers need to more rapidly develop and deliver solutions that meet the business performance, scalability and end user requirements.

Visual Studio Team System adds significant new capabilities and value to the suite of Microsoft developer tools, with advanced end-to-end role collaboration, real-time reporting and analysis, and a host of new tools covering architecture, development, and testing.

Many of you may have already transition to one of the individual role based offerings in Visual Studio Team System, but are still learning all the new tools and benefits of the offering. This session is intended to provide an introduction to Visual Studio Team System, highlight the new functionality and business value in each offering, and outline the transition steps for existing Visual Studio and MSDN customers. We will also demonstrate Visual Studio Team System in action.

This is your opportunity to attend a free education session and fast track your knowledge and use of Visual Studio Team System.


On-Site VSTS Event Details & Registration:

City: Mississauga
Date/Time: February 22nd 2006 -- 3:00 to 5:00pm
Location: MS Mississauga Office MPR Room
Conference ID: 1032290174
TO REGISTER for this event click here

City: Vancouver:
Date/Time: March 2nd 2006 -- 9:00 to 11:00am
Location: MS Vancouver OFC
Conference ID: 1032290176
TO REGISTER for this event click here

City: Mississauga
Date/Time: March 21st 2006 -- 1:00 to 3:00pm
Location: MS Mississauga Office MPR Room
Conference ID: 1032290177
TO REGISTER for this event click here

City: Ottawa
Date/Time: Apr 19th 2006 -- 1:00 to 3:00pm
Location: MS Ottawa Office Glacier Room
Conference ID: 1032290179
TO REGISTER for this event click here

City: Calgary
Date/Time: May 17th 2006 -- 9:00 to 11:00am
Location: MS Calgary Office Bldg (*TBC) - registrants will be notified of location
Conference ID: 1032290181
TO REGISTER for this event click here
City: Toronto

Date/Time: June 21st 2006 -- 1:00 to 3:00pm
Location: Intercontinental Hotel on Front Street (*TBC) - downtown Toronto
Conference ID: 1032290183
TO REGISTER for this event click here
*TBC – To be confirmed

Alternative Registration Options:

  • By calling 1-877-673-8368 to reserve your place, 24 hours a day, 7 days a week, and quoting the event ID.
  • Register online at by clicking on Search Event or Event ID in the left hand column and typing in the event ID.

VSLive 2006 Toronto Dates announced

Not sure this is news to most of you but VSLive is making another trip to Toronto again this year April 24-27th, 2006. It's back at the Toronto Congress Centre out by the airport again (I have mixed emotions about that one). Anyway, make sure to check it out:

How Do I Revert a Changest in Team Foundation Source Control?

This question came up in our VSTS for Developers Training Course in Toronto this week. One of my students wanted to know how to undo or rollback a checkin, something you could do in Visual Source Safe.

Unfortunately this feature was cut for V1 of Team Foundation Server. Buck Hodges and Brian Harry from Microsoft share how this was a painful cut to help get V1 out the door in this forum post. 

Update: James Manning (via the comments) points out that the Team Foundation PowerToy will do this for you. Great tool btw - check it out. Includes the following tools: Unshelve & Merge local changes, Rollback, Online (syncs offline changes to source control), Get Changset, and Undo Unchanged.

Vancouver Launch of Visual Studio and SQL Server 2005

I'm in British Columbia for a few days for the Vancouver stop of the Canadian Launch of Visual Studio and SQL Server 2005. They are expecting a great turnout - should be one of the largest MS events in town in recent memory. Last night we had a User Group reception and I got a chance to meet some of the local community leaders and technorati.

  • Rob Chartier is a smart guy who is going to be working at the Ask the Experts Cabana area as well. He is also working on a Code Camp out here in Vancouver on March 18th. Registration is now open.
  • Shaun Walker of DotNetNuke open source portal fame was also there. We had some interesting conversations around the challenges of managing an open source project. I was happy to hear how commercially successfully they were as well.
  • My friend Mike Flasko was also up from Redmond. He is now the Program Manager for the System.Net team and is doing well in his new role at Microsoft. They have some pretty exciting stuff in the works for Orcas and beyond. He also has an open call on his blog for feedback on what you want in Orcas for System.NET.
  • I also had a chance to meet Graham Jones who runs Vantug out here.

All in all in was a fun evening. Ilya Bukshteyn is up from Redmond to do the Keynote presentation which I'm looking forward too. John Bristowe and Ilya are sure to have some lively banter during the demos.

Migrating ASP.NET 1.x sites to ASP.NET 2.0

One of the questions we got during the Q&A of the Ottawa VS Launch yesterday was around problems in migrating ASP.NET applications from 2002/2003 to 2005.

The Web Platform Team has put together a nice step-by-step guide that covers some best practices to ensure a successful migration effort which should take you “the better part of a day” according to them.

Hello 2.0

This past week we saw the final bits of SQL Server 2005 and Visual Studio 2005 get shipped up to MSDN Subscriber Downloads. Next week we'll see the official launch of these same products to the rest of the world, ushered in with a rolling thunder of launch events and parties stretching into the rest of the month and beyond. Microsoft does a great job of fostering community with events like this.

Technically there is a lot to like about the updates to the platform and I share most of Joel's top picks. I've been building applications, consulting and teaching developers on this platform for 4 years now and it feels quite legacy, if not common place, to me now. However, in many peoples' eyes, this becomes a critical moment in time: .NET is no longer a 1.0 product. Of course I'm speaking about groups who are not developing anything significant in .NET today, and with this maturity milestone, allows them into this “new“ world.

We've been watching the adoption and market maturity of .NET closely for the past few years, and a bit to my surprise I'm starting to see a lot of groups come to .NET for the very first time with 2.0.

This coming Tuesday I have the great pleasure of being involved in the ushering in of this new era at the Toronto launch where we are expecting between 3,000 and 4,000 developers and IT professionals come together. Early statistics are showing that somewhere between 35-50% of these folks are new to .NET. Similar events are taking place all over the world during this week and stretching out into December and beyond. For Canada, Toronto is just the first stop in a long list of cities from coast to coast. Personally, I'll be presenting at Toronto, Ottawa, Vancouver, Montreal, Quebec City and Halifax.

The overwhelming registration statics tics in all cities tells me two things: Firstly that .NET 2.0 is going to be adopted very quickly. Secondly, and more importantly, is that the software development industry in Canada is vibrantly growing and that indeed....Software Matters!

Consider two things:

  • Software costs a lot of money to design, build, test and deploy. Much more than it should.
  • Software projects fail at an alarming rate. Failure can be defined as any of the following: Late, Over Budget, Under Functionality, Buggy, Doesn't meet requirements.

Yet despite these two glaring issues, the business value of software is so compelling, that people are willing to keep investing in building software at increasing rates.

And then there is Visual Studio and SQL Server 2005:

  • One of ASP.NET 2.0's design goals was to reduce the number of lines of code in a typical application by over 50%.
  • SQL Server 2005 has been enhanced to be more reliable and secure, while at the same time bringing the 4GL productivity associated with C#, VB.NET and the .NET Framework into the database engine itself.
  • Visual Studio Team System 2005 was built from the ground up to help project's stay on track by integrating developers, architects, testers, project managers and other stakeholders into a common extensible repository known as Team Foundation Server.

Coincidence? I hope not ;)