Hello again. First off let me re-introduce myself. I'm the guy who, a long time ago, used to run this web site. I was even known to post on this blog from time to time. Hard to believe, I know. 

OK, humor aside, it's been quite a while since I posted, mainly because I was on this killer project that just about absorbed all of my nights and weekends for 8 months. So feeling somewhat burned out, and not really having any energy, I just couldn't bring myself to do anything useful for EasyObjects during this period. BUT...

That project is now over, and I have had some time to recover (and sleep!), so now it's time to get back to the business at hand! I have a lot of things to cover, but I'm not going to try to catch up all in one blog posting. Instead I will focus on one item and cover the rest in the next few posts. Here are the topics, in no particular order:

• Enterprise Library 3.0, and 3.1
• Other database providers (specifically Access, VistaDB, MySQL and Firebird)
• The EasyObjects.NET "Store"
• LINQ and Orcas
• Design-time databinding
• Signing the EasyObjects DLL
• Breaking compatibility with the EntLib

Now, as you have probably guessed by now (from the title of this post), we'll be tackling the last item first.

To start with, a little history: in the Enterprise Library version 1.1 (June 2005), there was a function called GetDataAdapter() that was used mostly internally to create a database-specific data adapter. For example, a SqlDbAdapter for SQL Server, or an OracleDbAdapter for Oracle. In this version of the EntLib, GetDataAdapter() was defined as an abstract method. It didn't really expose the adapter directly to an outside calling entity (such as EO), but that's not such a big deal, really, because EO 1.x doesn't directly use the data adapter. It calls UpdateDataSet(), which eventually creates the adapter in order to do the update.

But this functionality changed drastically in the EntLib 2.0. Now the function is no longer abstract, they define the data adapter in the base Database class.

You may now be asking, why is this important? As it turns out, SQL Server and Oracle are almost unique in one respect when compared to other database engines: they both allow developers to submit multiple SQL statements in the same command. For example, if I want to get the value of an IDENTITY column after I insert a row, I can do it in one step on SQL Server:

"INSERT INTO [Customers] (c1, c2, ...) VALUES (@p1, @p2, ...); SELECT @id = SCOPE_IDENTITY();"

You just define the parameters @p1, @p2, etc. as INPUT and the ID parameter @id as OUTPUT, and you can get the IDENTITY value right away. The problem is, this simple example ONLY WORKS IN SQL SERVER AND ORACLE. Almost every other database that I have run into so far does not support running multiple queries in the same command.

(Note: before you Firebird people start sending me emails, yes I know about the batch command object in Firebird. All that does is submit multiple commands for you, they each have their own FbCommand. It does not run multiple statements in a single command object.)

Lacking this ability to get the IDENTITY value back after an INSERT presents quite a challenge when supporting other databases. That really leaves only a couple of solutions: use stored procedures or try to figure out how to retrieve the data from the new row in the data adapter's RowUpdated event (see? I told you there was a connection!).

Now, MySQL v5 and Firebird support stored procedures, so that's one work-around for those platforms. But what about MS Access? The knowledge base has an article that says that the only way to retrieve the last inserted IDENTITY value is to capture it in the RowUpdated event. OK, that means we need to wire up the event after the data adapter is created.

So I created the MSAccessDatabase class, inherit from Database, and add an override method for GetDataAdapter()...

Except that you can't override that method. Well, crap. I tried various methods of getting around the problem, but without success. So my last, and worst, choice was to modify the Database class in the Enterprise Library so that we can add support for these other databases.

Those of you that have followed the EasyObjects development path know that I do not take this course lightly. Modifying the core EL code is a serious step, especially now that Microsoft distributes the signed versions of the library. But it is my belief that it is necessary, and I have submitted a request to the Patterns & Practices team to change the code in the official distribution.

In the meantime, I have initial versions of Access, Firebird and MySQL providers working for EasyObjects. But more on that in a later post. It's good to be back!