As you might have read, I am lately doing a lot of stuff in .NET with DB2 as a database server. In the past, the only way to connect to a DB2 database from .NET environment was via ODBC. Nowadays, IBM provides us with DB2 data provider for .NET. On top of that, launch of DB2 data provider kind of made ODBC connection unsupported. Fine. Data provider is slicker anyway.

Except that it is not.

Now, I don’t mean to dish out on data provider. All in all, it is a fine piece of software. Until you start using typed datasets. Now, I have a lot to say about typed datasets and how it is breaking my main development rule of keeping software at the place where it should be, but we’ll get to that in some other post.

Anyway… back to topic at hand. DB2 Data Provider for .NET and typed data sets. For starters, they do not mix well. Try having a CASE statement in your select and you lost any chance of creating a typed dataset. Took me about 2 hours to narrow it down. As error message is as friendly as it can get, it said that there is an error at “,sd.”, pwhere sd was alias of the table I was using. So, by means of exclusion, I located a culprit and after thorough testing, I came to conclusion that thou shall not have CASE statement in SELECT statement when using typed data sets and IBM DB2 Data Provider for .NET. The funny thing. That was never an issue when using DB2 ODBC driver.

But that was not the end of it. After some additional testing, I came to conclusion that the “thou shall not” statement needs an extension to: “Thou shall not use ought else, but simplest statements and perhaps some classic string functions in your SELECT statement”. Yeap, that about sums it up.

Interesting enough, that everything works OK, if you are using data provider in your code. It even works, if you already have a typed dataset and you add a function with CASE statement and it throws an erra, which is odd. But if you are building a dataset from scratch, forget about anything advanced in SELECT statement.

Now, I still think that IBM DB2 Data provider for .NET is a great way to go, but I do wish that this glitches would get fixed.