DbType and other provider specific types

At work I’ve been working with a couple of my very skilled co-workers (on who is a DB2/Cobol programmer, but I don’t hold that against him) to try to solve a problem in our data access layer.  Recently the IT department began a project to upgrade their DB2 servers from version 7.2 to 8.1.  As stated by the lead on that upgrade project, “There will be no problems in the upgrade process.”  Yah, right.  Short version is that of course there were issues.  The rest of that story should be told over a couple more drinks than I have in me right now.

Part of our debugging/research/stabs-in-the-dark today led us (well, one of the other guys, not me) down the path of database typing for command object parameters.  As our code exists today we are programming to interfaces.  IDbCommand, IDbDataParameter, IDataReader.  Things of that sort.  As a part of the IDbDataParameter code we are possibly, depending on the constructor overload you use, working with the DbType enumeration.  It is not required, but if you feel the need, you can dictate the exact type of the parameter being passed to your stored procedure or parameterized query.  In our case we’re working with the stored procedures, but I don’t think that this is a factor.

I decided to take a look around the DB2 native provider (IBM.Data.DB2.dll for those of you that care to follow along) code using Lutz Roeder’s Reflector.  My was I surprise at what I saw.  First, the DB2 native provider implements the IDbDataParameter interface on the DB2Parameter class as one would expect.  What I didn’t expect was that there was both a DbType and a DB2Type property on the DB2Parameter class.  When I started poking around in it I noticed that there were a number of different types in the DB2Type enumeration so I decided to take a look through the SqlClient and OracleClient classes to see if the same exists and to my surprise they do!

Because our code is using the DbType enumeration we can’t benefit from the full range of data types provided by the DB2 provider.  Perhaps this is the root of our problems?  I’ll update you if we find out.