I already posted this on SADeveloper, so you can check it out there too.
Analysing the Microsoft Data Access Application Block
Creating a Data Access Application Block for ODBC
Introduction
I was recently faced with the pain of needing to connect to a data source that was only available via ODBC. Despite many newer and better forms of data access, it seems some data providers choose to only provide access to their databases via old technology. I guess there’s no point moaning about it if there’s no other way, but rather find the best solution for the problem.
From past experiences using the Microsoft Data Access Application Block (DAAB), I knew that there was no better way to access data than by the methods provided in their code. I decided that my best option was to make a similar “Application Block” for ODBC data sources. After beginning the process of replicating the functionality, I soon discovered a few complications surrounding ODBC data access and thought it would be best to share the experiences with the SADev community. That way, hopefully I’ll be able to help someone facing a similar problem.
The Structure of the DAAB
The DAAB is broken down functionally into different types of static ExecuteXXX methods. The methods are all static as there is no need to instantiate the class to make a call to the data source, and this significantly simplifies the code needed to use the class. Each method type is overloaded about 8 or 9 times to provide for the most common possibilities of executing an action against a data source. The overloads are the same for all types of method, so I’ll just glance at some of the parameters that are required for different overloads.
Connections
The connection that will be used for the ExecuteXXX method can be created in a number of ways. It can be passed as a SqlConnection object or as a connection string. Depending on the method being called, the DAAB handles the connection in different ways.
ExecuteReader Methods:
The DAAB has an internal enumeration called SqlConnectionOwnership containing two members, Internal and External. This is used to track whether the DAAB owns the SqlConnection object or whether the caller owns it. The only reason why it is needed here is because the SqlDataReader object requires an open connection to the data source. If ownership is to External the SqlDataReader is instantiated using the ExecuteReader method of the SqlCommand that accepts no parameters. If ownership is Internal the SqlDataReader is instantiated using the ExecuteReader method of the SqlCommand that accepts a CommandBehavior parameter, and the CommandBehavior.CloseConnection member is specified to ensure that the connection is closed at the same time as the SqlDataReader.
Non-ExecuteReader Methods:
For all other methods the connection is either created from a connection string passed as a parameter, or the SqlConnection object itself is passed. For the ExecuteXmlReader method, the connection can only be passed as a SqlConnection object or as a connection within a SqlTransaction object. Regardless of how the connection is created, the DAAB will only ever open the connection. The responsibility of closing the connection is up to the caller. This is very important to note as it can cause problems depending on your data access strategy. When passing a connection string that uses SQL Authentication you’ll obviously not set the “Persist Security Info” parameter to true, which means that the password parameter will be lost after the connection has been made. If you close this connection and try to open it again, the connection will fail due to the missing password. The safest route here would be to use Windows Authentication, thus eliminating the problem at the root.
Commands
A command is slightly more complicated as there are a number of different command types (as specified in the CommandType enum). The caller is able to execute either a parameterised stored procedure, or merely execute a text query with no parameters. There are overloads that cater for all possibilities, and the caller is able to pass the command as a stored procedure name or as a string of text to be executed. Where the method has no way of identifying whether a stored procedure or a string query is being executed, the caller must specify the CommandType as one of the parameters. The caller is faced with a number of possibilities: call a stored procedure with / without parameters, execute a string query with / without parameters. This level of flexibility is essential in avoiding writing unnecessary method overloads when an extra parameter (CommandType) can solve the same problem.
CommandText
The command text is a much simpler parameter, as this can only be a string. Whether the method must execute this string as a stored procedure or as a straight query is covered above in the Commands section.
Parameters
The caller is also given freedom in the way parameters are to be passed. A params object array is the simplest of all, as no work needs to be done by the caller to instantiate SqlParameter objects before calling the DAAB. The DAAB handles the job of assigning the object values to parameters within the command. The caller is still given the option of passing a params SqlParameter array in case this is needed. This obviously allows the caller to pass the parameters in any order, whereas the object array must strictly be in the same order as the parameters in the stored proc itself.
Transactions
When a SqlTransaction is supplied, the DAAB uses the connection associated with it to execute the query. A transaction is not always needed, as there are many different ways of handling transactions. Not all methods require a transaction, but it is catered for in case the caller wants to use ADO for transaction management.
Parameter Cache and Parameter Discovery
The DAAB has significant power when it comes to command parameters. It has a parameter cache class that provides methods for parameter caching and discovery. This is particularly useful when ensuring that the number of object parameters passed to the method match the number of actual parameters declared in the stored procedure. The SqlCommandBuilder has a static method called DeriveParameters, which accepts a SqlCommand that is used to retrieve the list of parameters associated with a stored procedure. This obviously will have an effect on performance due to the additional call being made to the database to merely retrieve a list of parameters. This is countered by the fact that the discovered parameters are cached in a Hashtable. The next time a command is executed the cache is checked first, and if not found the DAAB makes a call the database. This cache is indexed by the connection string and the command text. This is another reason for using as few connection strings as possible. Obviously you won’t be able to use the cache if two connection strings, although containing the same information, are not typed identically.
Parameter Caching Problem
One important point to know regarding use of the parameter caching is that the parameters are not cached with any expiry date / time. This shouldn’t be a problem because stored procedures shouldn’t change once they’ve been deployed live. For some, this may be a serious problem and is worth your attention. I ran a simple test of the parameter caching function by creating a stored procedure and having a Windows Forms app run this procedure, displaying the result in a Textbox. The test involved running the stored procedure to ensure that the parameters were cached, then changing the stored procedure in the database and running the code again (without stopping and restarting the test application, of course). The first run was successful and wasn’t too fast, but successive runs were substantially faster. This proved the performance hit of retrieving the parameters as well as the performance gain of only doing this once per stored procedure. Then I changed the stored procedure on the server and ran the code again. This time I got an exception. Obviously you’ll be enclosing all data access in try… catch blocks, but this is a flaw worth checking. It’s not really a flaw as there’s no way to tell that the parameters have changed unless you get the parameters every time (and not use a cache). It just worth noting that if you’re using the DAAB with a long running application (e.g. Windows Service) then any changes to any stored procedures will require that you restart your service to clear the parameter cache.
Changes to Support ODBC
Now the problem I faced was creating a working equivalent to support ODBC. Luckily the use of a Managed Provider for ODBC ensures that your code works the same as that of the Managed Provider for SQL. So it’s simple… just a matter of doing a find and replace from “Sql” to “Odbc” should do it, right? Nope! Once all procedures have been painstakingly analysed to ensure that no Sql objects are being used, you’ll find that stored procedures still don’t execute successfully!
I searched all over the Net to find a solution and came across the following Microsoft Support KB Article on executing parameterised queries on ODBC using C#: http://support.microsoft.com/default.aspx?kbid=310130
This clearly states that ODBC requires the use of the “CALL” keyword before the name of your stored procedure as well as a list of bracketed parameter delimiters to indicate the parameters. This means that your stored procedure would look as follows (if you had 4 parameters):
{CALL InsertEmployee (?, ?, ?, ?)}
Luckily this is no big issue as the code has been written correctly and the change only needs to be made in one method. There’s a private PrepareCommand method that does the following tasks:
- Opens the connection, if not already open.
- Attaches the connection to the command.
- Attaches the command text to the command.
- Attaches the transaction to the command.
- Attaches the parameters to the command.
All we need to change is the way step 3 is accomplished. Now, instead of merely attaching the command text parameter to the command, we add additional information before and after. Firstly we put the CALL keyword before the name of the stored procedure. Then we add one question mark for each parameter in brackets and append that after the name of the stored procedure. This new string is attached to the command as the command text.
Once this has been done, you have a fully working ODBC Data Access Application Block!
Conclusion
After analysing the Microsoft Data Access Application Block, we find that it isn’t without problems. There are still issues that we may face when using this, depending on the type of application we’re writing or the way modifications are made to our stored procedures. The point to remember is that the code was released so that developers could use and modify it to support their respective environments. When working in a largely unchanging environment, the generic DAAB suits perfectly and saves a LOT of coding effort. When working in an environment that is not immune to changes, whether that be application changes, business changes, or database changes, we are faced with the decision of whether to modify the generic DAAB to suit our needs better or to work around such issues.
The DAAB is certainly an excellent starting point for creating a data access strategy for other data sources. It was developed for SQL Server specifically, so some concepts employed will not necessarily be the best for other data source, but it does provide a robust framework from which to start. I’ve made use of this to provide a Data Access Application Block Framework giving access to DB2, ODBC, and SQL data sources. I’ve included this as a download attached to this article, and you’re free to play with it and modify it. It’s all based entirely on the Microsoft SQL DAAB, but will save a lot of coding and testing if you need something like this.
I hope this article has helped shed some light on the Microsoft Data Access Application Block, and has hopefully shown where potential problems may arise when using it. All in all, I’d rate it as a 9 out of 10 for data access. It’s certainly a time-saver and is very well written to support data access to SQL Server.
If anyone has any comments about this or any problems with the code attached, please post a thread for this article and the group can give some insight from other perspectives.