The IDataReader - Frustration in DAL land and other frustrations
The problem with programming sometimes is learning the annoyances that plague a development language. I was troubleshooting my SQL DAL, which instead of using the "it COULD work with every data provider" (the caveat being - if the only data relationships are those you generate from drag and drop IDE)...has a semi-generic abstract base, but utilizes generics such that parameters and provider specific annoyances can be fully exploited and not merely addressed.
Most DAL implementations either go to one extreme where an incredible use of reflection is utilized to cast types back and forth. The other extreme is creating a DAL that uses the ever limited SQL Helper class that comes from DAAB but yet for every Business Method there is an equal corresponding DAL method. My approach is using a dictionary in the DAL that contains the specific DataProvider commands for parameters. The BLL passes over a LIST<T> that contains a key value that matches a key in the Data Provider specific DAL and contains the value to populate it once the translation is made.
The reason for that approach, is that I can either pass a SQL stored procedure, auto-generate a sql text based on few sets of parms, or execute a a pure ADO command text that is passed in based on a key pair match. Ultimately, it eliminates the need for the BLL to manage anything data related.. ALMOST..
Gripe 1. You can do ExecuteNonQuery, ExecuteScalar, but not ExecuteReader in a manner that the DAL actually handles closing the underlying connection. The flaw lies in the underlying Interfaces. For instance calling specifically the SQL Data reader, you can manage the connection within the DAL, however if you use the IDataReader - you specifically have to provide the Close and Dispose (but not the open) and mark the DAL with IDisposable. Otherwise, you will get nothing but a conjucture of "Parameter is already contained within another collection"... which to mean indicates that if you call the parameters.Clear() - it should take of it - and it doesn't leaving the taste there thread safety issues...
Gripe 2. Try .. Catch.. statements.. Sadly, you cannot do something like this:
public object MyClass(something something)
{ try
{ myObject obj = DAL.something;}
catch(Exception ex)
{
//log this
}
Finally
{
Dal.close()
Dal.dispose()
}
return obj;
Sadly a object reference to an object occurs with returning the obj as it is declared in the Try statement. So instead, you would have to init the obj before the Try statement. Now, I now this has been around for ages and that we all deal with it... but does it really make sense? Programming wise, it is a waste of allocating objects first only to find you can't do something with it anyways... Secondly, if the Try succedes shouldn't whatever is declared be accessible outside the scope of the trapping?
Which brings me to Gripe #3
Even when specifying a Dispose, Close, and Open in the DAL / IDAL and calling it within the BLL - when an error occurs within the DAL (say a SQL Parameter mismatch) - the connections never get disposed in the finally statement EVEN though it is explicitly called from the BLL. I spent a better part of all day tracking down "Parameter already contained in a collection" error - only to discover the real error was that I had renamed the variables used as passed parameters. (from @sectionid to @sid)..
Even with it wrapped in proper try / catch and finally blocks, when the error occurred, the connection was never closed or disposed even though the Dispose and close methods of the DAL were called in the finally block. They work properly when everything goes without an exception being caught..
This to me is contrary to the way the whole exception handling is supposed to work. And what is worse - since this is the method documented for handling errors - this has to be a bug..
Secondly, using the IDatareader reader.. do something (CommandBehavior.CloseConnection) does not work although intellisense gives you the option to use it. I had thought the whole purpose of this was to allow the not explicitly closing a reader but allowing the reader to automatically close the connection when no more data is being read. It works naturally if you use the SQL reader but not the lower level IDataReader which is a requirement if you are designing n-tier structures...
Just a rant for the day...