Archives / 2006 / May
  • The Truth about Stored Procs and O/R Mapping

    There's been yet another series of posts recently that spread some FUD about stored procs and O/R Mapping.  I could try to refute the various points one by one, but that's been done before for those willing to look.  So instead I decided to write a little about how O/R Mapping actually changes very few things we know well.

    First, do you give each user a separate db login, with varying capabilities, or does your app use just one?  This seems to be the central point of contention in some of these discussions about stored procs and O/RM, but this is actually a very independent design decision that has nothing to do with whether you use an O/RM.  That's right, you can use separate db logins with O/RM, and you can use a single db login without an O/RM.  So how do you determine which approach to use?  You must consider the various tradeoffs that are involved.  It may seem obvious that every user must have their own separate db login, but what are the implications?  The most serious tradeoff is that your app will never be able to take advantage of db connection pooling.  Because of that fact, most all web apps, as well as those enterprise apps that have a distributed design, make the decision to have just a single db login that the application uses for all users and db interaction.  This approach is used almost universally, including by those that use stored procs and do not use any O/RM, so I really find it troubling when some people ignore these facts to imply that this makes O/RM insecure.  But what if you do have a situation where you have determined that you do need separate db logins instead?  Then there is absolutely nothing about O/RM that prevents you from using that approach if that is required.  Some O/RMs won't allow you to use stored procs, but others do allow stored procs -- its not all or nothing.  The point is that arguing that using stored procs with separate db logins is more secure than a single login has absolutely nothing to do with whether or not you use O/RM -- any such comments otherwise are pure FUD.

    Next, if like most projects, you have chosen to use the single db login approach to get connection pooling, then how do you end up securing your application and giving varying capabilities to your different users?  You of course end up checking user authentication and feature authorization by querying a membership store, and you probably also have a system in place so that users won't even see the capabilities they do not have.  So how do you actually prevent users from doing things that they are not allowed if your db login doesn't itself prevent those capabilities -- this is the issue raised when someone asks what if they get your dll.  The answer is that your application must manage security correctly, but this has nothing to do with O/RM, since this is true as soon as you go down the path of having a single db login for the connection pooling.  And just getting your dll doesn't give anyone any capabilities unless they also have your single db login, which could easily be the process account itself, instead of a sql login, to make that feat impossible.  The point is that security is protecting that single db login, which is trivial if its the process account, and if you don't protect that asset then you have a big problem irregardless of stored procs and/or O/RM.  So lets be very clear that security comes down to your application design and your protection of the login, and both of those key issues remain exactly the same regardless of whether or not you choose to use an O/RM.  As a side note, even if you do use separate db logins, you probably still only show users what they can do, which again makes the argument that a single login must provide additional security logic to be pure FUD.

    So now, assuming we can agree that the important security questions are not at all specific to using O/RM, then what other issues are brought up in regards to whether or not one should use or avoid stored procs?  Some people continue to raise the same old tired and flawed discussions about sql injection and performance, but the facts are out there for those really interested in the truth instead of simply spreading more FUD.  I think the most interesting observation is that I know of many, myself included, that switched from these stored proc based systems to using O/RM without stored procs, but I know no one that switched the other way!  Why is that?  Could it possibly be that we know based on our experience, instead of idle academic guessing, that our apps are just as secure and performant as they were before O/RM, but far easier to build/maintain?  And why is it that I still use stored procs when they make sense, especially for batch jobs and reports, as well as for the occasional situation where there are some serious performance optimizations to be made?  Could it be that O/RM users are not anti- stored proc, but instead simply use the best tool for the job?  And why do they assume we can't possibly use triggers for maintaining history -- I've recommended triggers.  And if they really insist on stored procs, then why do they ignore O/RMs that do support those stored procs?  It seems instead that some people must defend their decision to continue doing what they have always done, in spite of the many facts and success stories that speak differently, thus the continuing parade of FUD.  Can you do things poorly with O/RM?  Absolutely -- just like you can make poor choices without an O/RM!

    Finally, I saw a few people ask for examples that prove O/RM can be secure and performant and scalable.  If you are truly wanting such an example, then I point you to my WilsonWebPortal which uses my own O/RM.  All of my sites run exceptionally well on it -- better than many other popular portal based sites out there.

  • Introducing the WilsonORMapper DataSource Control

    It seems that the ObjectDataSource control is not as easy to work with as most people had hoped.  It also requires far more than is necessary if you are already using an O/R Mapper with your objects.  So I decided to spend a few spare hours and throw together a DataSource control for my WilsonORMapper.  The download includes the full source code for the DataSource control, as well as a simple web demo.  So download it and let me know what you think -- and maybe the source code can be modified for NHibernate and others.  Please let me know how it works for you, since I honestly haven't spent much time on it, and let me know if you found the source useful too.

  • ASP.NET Custom Providers Presentation and Sample Code

    My ASP.NET Custom Providers presentation and sample code can be downloaded from  The sample code includes very simple custom providers for Membership, Roles, and SiteMap, along with an overly simplistic web app to demo them.  They all use my ORMapper for database access, which means you can target most any database, but you can easily substitute your own data access pretty easily.  The SiteMap provider is intentionally not very optimized since the intent is to only show a very simple example, although there is one tiny optimization included.  That said, its not very hard to add some smarter caching, as I have done in my WebPortal, although you'll also need some type of cache invalidation when you add, modify, or remove pages.

  • Speaking at the Atlanta Code Camp on ASP.NET Custom Providers

    The Atlanta Code Camp (2006) is scheduled for tomorrow (May 13, 2006) at DeVry University in Decatur (not the MS Offices in Alpharetta).  I'm currently scheduled to give my presentation on ASP.NET Custom Providers at 8:45 am, although the schedule may change.  The new services exposed by the new provider model in ASP.NET v2.0 are some of the best new things, so this should be a great topic.  My goals for this presentation are to introduce these new services and the provider model, and to convince everyone to build their own custom providers.  Well maybe that's a little strong, but I do think that most enterprise web apps should consider this, and I'll show some simple sample custom providers.  In particular, I'll give you the code for some very simple Membership, RoleManger, and SiteMap providers that work on top of O/R Mapping.  We won't be focusing on the O/R Mapping at all, so that's not a prerequisite, since you can always add your own database logic if you desire.  But O/R Mapping does make working with custom providers far easier, and it automatically works with MS Sql, Access, Oracle, MySql, and most other databases as a bonus.  And if you need more, my WilsonWebPortal has better optimized versions of these same providers, as well as adding custom Profile and WebEvents providers.

    I've also got a lot of very good books to give away, in addition to the ones that will be given away all day long in all the sessions.  They are (1) Apress' Beginning ASP.NET 2.0 in VB 2005 by Matthew MacDonald, (2) Apress' Pro VB 2005 and the .NET 2.0 Platform by Andrew Troelsen, (3&4) two copies of Addison Wesley's Visual Studio Tools for Office Using Visual Basic 2005 by Eric Carter and Eric Lippert, (5) Addison Wesley's SQL Server 2005 Distilled by Eric Brown, (6) O'Reilly's Programming SQL Server 2005 by Bill Hamilton, (7) Microsoft Press' Programming ASP.NET 2.0 Core Reference by Dino Esposito, and (8) Microsoft Press' Programming ASP.NET 2.0 Applications Advanced Topics by Dino Esposito.  And I'll give you the book of your choice (that's still left) if you attend my session and come up before it starts to introduce yourself and tell me you read about this offer on my blog.  I've also got a few 32MB USB Keys if you prefer that instead of a book, so even if you don't want a book you can still come up and introduce yourself and tell me you read about this on my blog.  Any books or USB keys left after that will be given away during the session however I see fit, so come early -- but please form a line to keep it nice and professional.  :)

    There's a lot of other sessions that look good too of course, and most of them are probably better speakers than I, so come out -- assuming you did register.  I'm not even sure how I'll pick between some of these sessions, since there are several ones in each time slot that I'm interested in attending.  So don't be offended if I attend a different session Wally -- I may still make it to your Atlas session, as I really am interested, but there are other good ones too, so its hard to decide.  So see you at the Atlanta Code Camp tomorrow -- and don't forget the location is DeVry and not the MS Offices since we needed more space.

  • Tim Doesn't Like -- And I'm Not Sure About Atlas

    Tim says he doesn't like -- its slow and its ugly -- and there's just no point in it.  I have to say that I agree, but I also think there's an even bigger problem -- Atlas !  Now I'll be the first to admit that I still haven't spent much time with Atlas, so maybe I'm missing something, but it just feels all wrong to me.  Don't get me wrong, I like Ajax, when its used correctly and within reason -- of course I also liked it back in 1999 before it was called Ajax.  I also like some of the Ajax implementations from what I've seen of them, but that's where Atlas goes wrong.  What do I mean?  Quite simply it doesn't try to be an Ajax implementation -- instead it tries to be a client-side framework.  And just why do we need a client-side framework?  Oh yea, to build sites like -- it all makes perfect sense now.

  • Interested in Astronomy -- Try's Twin 14" Scopes

    I don't usually make many recommendations, since I don't want to sound like a commercial (unless its about me of course), but you've got to try !  I've always loved astronomy (and most every other science for that matter), but I've never had the opportunity to really see things myself.  I actually even bought what I thought was a good backyard telescope a few years ago, but I returned it when the Moon was just bright and Saturn was just a blur.  But then I read about in my Discover magazine the other day (another highly recommend resource -- every article is a must read for me every month), and I thought this was too good to pass up.

    So what is  Basically someone has setup 2 14" telescopes on a mountain in the Canary Islands and hooked them up to the net -- and you get unlimited viewing for $99 a year (they have smaller per-minute deals also).  One scope is controlled by the professionals, with paid commentators, but the other scope is controlled by members -- you can pick what you want to see in an available time slot, and those are actually easy to get, and you can always watch what others have selected too.  Each viewing is only 5 minutes, but that's more than long enough for even objects far away to resolve in decent detail and color -- and the timezone difference is perfect.

    Now let me share my family's observations from last night.  We saw the Moon and all its craters crystal clear as I expected, and we saw Saturn pretty well -- I had hoped Saturn would be better but I do understand this is only a 14" telescope (better than anything we could personally own, but not as good as the biggest either).  But then we looked at Pluto -- and you can't even tell which bright dot out of many others in the background it really is -- and that was a perfect educational opportunity.  Why?  Because the only way astronomers actually found Pluto (and others farther now) is by looking for small movement over several hours or even days !

    We also have observed lots of galaxies, nebulas, and other objects too, but it was definitely the planets that I wanted to look at with my son first.  Just make sure you realize that far away objects actually take several minutes to appear in full color and clarity since it has to collect enough light.  Its really fascinating and I highly recommend it to anyone wanting to observe the planets and stars and more.  And you certainly can't beat it for the price and the educational value either -- by the way, "slooh" is a play on "slew", which is how you turn a telescope, so I suppose you're supposed to pronounce it "slu" and not "slo".