On this page.... RSS 2.0 | Atom 1.0 | CDF
# Tuesday, April 11, 2006

At the recent Orlando CodeCamp, I did a presentation called (roughly) "Introduction to Generic Database Programming with ADO.NET 2."  Phwew.. gotta take a deep breath after that one.  So, the premise of the talk was to introduce folks to the features in ADO.NET 2.0 that make provider-agnostic (a.k.a., generic) database programming possible.  And I think that was accomplished in that it covered the System.Data.Common namespace and had an example that illustrated how to create connections using the DbProviderFactories class and the provider invariant name as well as how to retrieve metadata information using the GetSchema methods on DbConnection.

In my chapter for Professional ADO.NET 2, I created a very basic custom ADO.NET provider for Active Directory, which required me to implement most of those classes and provide the basic info from GetSchema.  So I was familiar with it from the theory side of things and thought it was pretty neat as an idea.  But I personally have not had the opportunity to really dig into doing it much myself; I prefer using an object-relational mapper when I can, and I've been fortunate enough to not be in too many situations where doing "real" generic db programming was necessary.  I also knew, in theory, that there were complications in creating database-agnostic code, but I didn't know just how unfinished it is.

Consider, for example, that Oracle returns results from stored procedures using an output cursor parameter instead of a regular result set; this can make it less than generic when you want to use sprocs.  And even something as simple as dynamic SQL command parameters is not fully addressed with the new ADO.NET 2 stuff.  It is this fact that prompted this blog entry.  I was trying to do something super simple--create a very focused test data generation tool that could use Oracle or SQL Server as its persistent data store. 

In particular, for the commands (trying to follow best practices), I was going to use ADO.NET parameters instead of string concatenation or substitution, and I recalled that one of the new standard schemas is a DataSourceInformation schema that specifies, among other things, a format string for parameter names.  In other words, as my friend Plip demonstrates on his blog, I should be able to use that value with String.Format in order to get a properly-formatted parameter name for my commands.

It turns out, though, that it just don't work, at least not in the RTM version.  (In Plip's defense, that post was made when Beta 1 was current.)  In the RTM, the SQL Server provider returns simply "{0}" as the parameter marker format string, which is, I'm sad to say, utterly useless.  It should return (in my humble opinion) "@{0}" to prepend the @ in front of the name as that's what SQL Server expects.  Looking at the Oracle provider, it correctly prepends the : using ":{0}" as the format string.  So, something that should be as simple as getting an appropriate parameter name is goofed up. 

Now maybe I'm missing something, but I know that folks like Frans Bouma, who have far more experience than I writing code to interface with multiple providers, seem to indicate that my impressions are not far off.  My own object-relational mapper completely abstracts the SQL itself such that providers have to supply SQL templates, so I'm not faced with writing generic SQL and can still take advantage of the abstractions provided by ADO.NET.

So I guess it really depends on what you're looking for when you say generic database programming.  For now, it seems only partially fulfilled, particularly since the queries themselves don't have sufficient standardized abstractions (that I'm aware of anyways).  I know there is a SQL standard, but as far as I can tell, it is fairly limited and doesn't cover simple things like (as Frans pointed out) getting an db-generated identity value back.

The good news is that there are good abstractions that can take care of a very large portion of the provider-specific work for us.  Obejct-relational mappers are one such example, and with the advent of LINQ and DLINQ, I hope that we'll find less and less need to actually write SQL ourselves and rely, rather, on these higher-level, object-oriented abstractions to hide the complexities of data persistence for multiple providers.  Generic database programming isn't a myth; it's just a legend, something based in truth but not a full reality.  What do you think?

Tuesday, April 11, 2006 8:26:01 PM (Eastern Daylight Time, UTC-04:00)  #    Disclaimer  |  Comments [0]  | 
Comments are closed.

The opinions expressed herein are solely my own personal opinions, founded or unfounded, rational or not, and you can quote me on that.

Thanks to the good folks at dasBlog!

Copyright © 2019 J. Ambrose Little