Sometimes it really baffles me how some things in ASP.NET turn out to be a lot harder than they really "should" be. (I think that their unofficial slogan "Do the impossible. Go home early." is totally ironic, as I've had pretty much the exact opposite experience of spending hours doing things that I thought were easy.) Today I had a perfect example of this. Our application is using MS Access as a test database, and is then switching to SQL Server for the live database. So database commands are all taken care of through the ADO.NET layer, the details of the individual databases are extracted away, and I can just flip a switch to make the changeover from one set of drivers to another, right? Wrong.
It's clear to me that Microsoft planned to allow easy changeovers between databases, due to the fact that connection strings to different databases are stored in the web.config file. Switching to a different database on the same platform is a simple matter of changing the connection string. But the problem comes when you have to switch between platforms, such as my instance of switching from Access to SQL Server. I'm sure this never comes up for a lot of projects, but I can't imagine that changing database platforms is so rare that it could be safely ignored without impeding the efforts of too much of their audience.
So the first problem of switching from Access to SQL Server is that the two databases use entirely different classes in completely separate namespaces for communication. To talk to an Access database, you have to use OleDbConnection, OleDbCommand, OleDbReader, etc. But for a SQL Server database, you have to use SqlConnection, SqlCommand, SqlReader, etc. And naturally, the two sets of classes are not interoperable. Instead, you're forced to declare your allegiance early and unequivocally. You choose one of several parallel but disjoint tracks and you have to stick with it. It's kind of Microsoft's version of "You're with us or you're against us", and I think we all know how well that one went over.
Now, I sure was not going to write code which would have required me to change OleDb objects to the corresponding Sql objects in every single one of my business model objects. Thus, I created a Database class that attempted to abstract the details away as much as possible and simplify the code that would have to go into my business models. It boils down to a method that takes in a SQL string and a data structure of parameters and spits out the result as a DataTable. Honestly though, I would've probably done this anyway, just because I like to be able to avoid writing the same code over and over again, so this in and of itself was not really that much of a problem.
However, the thing I found out today is that much of a problem. What I found is that Access and SQL Server handle parameter binding in very, very different ways. Let's say you have an INSERT query with three parameters, and you want to bind them to variables in your code. Access doesn't particularly care what you call the parameters in the query, and will simply bind your three variables to the first three things it doesn't recognize in your query. SQL Server, on the other hand, has a very strict syntax for parameters, and will expect you to associate each variable with the corresponding parameter name in the query.
This creates a serious problem when you're trying to create a database wrapper class like what I had, in particular deciding on a data structure to represent parameters for the query. Internally, ADO.NET uses an OleDbParameterCollection or SqlParameterCollection for this, but as mentioned before, I don't think it makes sense for the business models to use a database-specific variable. So we're down to collection classes that don't rely on one database or the other. I had started with an ArrayList of parameters in order, which was fine for Access. However, it did not include the parameter names, which SQL Server requires. In an attempt to associate parameters with their names, I switched the whole thing over to use Hashtables instead, which SQL Server was happy with. However, since Hashtables do not return their contents in a deterministic order, Access totally jumbled the parameters, resulting in complete failure of my queries. I had the same result with SortedLists too. So nothing I tried would work for both databases.
So in the end, given the time pressure I was under, I essentially raised the white flag and created two copies of my business models - one of which sends an ArrayList to an OleDbConnection for Access, and one of which sends a Hashtable to a SqlConnection for SQL Server. If I had more time, I might've looked into using an ArrayList of Pair objects, the first item being the key and the second item being the value, but unfortunately I didn't have that luxury. Instead, I'm going to have to live with a really ugly and easily breakable solution. I really just don't understand how Microsoft could spend this much time on a database layer if I was going to wind up writing so much on top of it anyway.
The punch line to this story is that I have had experience with database layers that let you change to a database on a different platform with the flick of a switch (of course, after you clean up incompatible SQL that needs to get fixed no matter what database layer you use). In particular, I've used PHP's ADOdb class library to switch from Access to MySQL completely painlessly. What's funny about this is that the ADOdb project was developed entirely independently from both Access and MySQL, and yet it is able to bridge the gap perfectly - as opposed to this instance of three technologies (ADO.NET, Access and SQL Server) all created by the exact same company that cannot work together at all. It really boggles the mind.
EDIT: I wound up resolving this by using the ArrayList of Pair objects to pass parameters. Additionally, I created an abstract Database class that both my SQL Server class and my Access class inherit from. This class has a static method that will return either the SQL Server object or the Access object based on the value of a static Boolean flag. So now, I can switch from Access to SQL Server just by flipping a switch and correcting the incompatible SQL statements, but it's still pretty clear that ADO.NET did not work this way right out of the box.
No comments:
Post a Comment