Data and Football

I'm pleased to inform everyone (if you don't already know) that Football season kicked off (the pre-season at least) Saturday morning at 5am in Japan.  Apparently the Bucs beat the Jets, I wasn't up to watch the game and i don't think it was televised live anyways(at least in the US).  I did however watch what there was of the KC vs Green Bay game.  It ended with 5 min left in the third (thunderstorms, rain, etc).  The game was a preseason game and not really all that interesting but, it was covered in HDTV by ABC and that looked spectacular.  I cannot wait to see all of the college games in HDTV, You will most likely not hear from me on weekends in the fall for this very reason.  NY Giants play on thursday at 7 I believe on ESPN (which is not in HD yet in my area, i think TW should get on the ball in that regard)

Ok, let's blog about some .NET, in previous blogs I'd discussed in brief the data abstraction layer I'd developed for use on my projects (DataBroker).  Some people commented that this was also a good technique because it allowed you to overcome differences in Code Access security that were presented by different data access layers.  I honestly haven't run into this scenario, so I can't say for sure.  But I do want to take a look at my data access layer and see if it needed some tweaking.  Right now I've got functions to return DataReaders and DataAdapters, to Run execute Stored procedures (that don't return rows) and to manipulate parameters that are sent into or out of stored procedures.  I also have functions to handle transactional stuff (begin,rollback, commit etc..), to open and close connections, and a function to set the application user context.  I guess the application user context thing needs some explaining, well I've always been a fan of using the database to the fullest extent of its capabilities.  In one particular area is application level auditing (ie a record something to the effect of: User XYZ Created User ABC) I know this is business logic, but with triggers & tables i can make this completely invisible.  Which is nice for security reasons.  Anyways, the problem is in order to get the Application user id from the database, I either have to pass it in every time (there goes the trigger idea), use the database users as app users (forget connection pooling) or use a feature of sql server called SET CONTEXT_INFO 'Some Binary Value'.  This lets me place a small amount of information (128 Bytes i think) in the database Session's context info.  This info is retrievable from triggers and procedures etc..  I usually place the user's session id (i track sessions in the database) in there, that way i can find a specific user easily. Other databases support similar features, oracle for instance has a notion of a “proxy user” that you can connect through.  I've not looked to deep into that but I'm reasonably sure I'll be able to modify the OraDataBroker (my oracle databroker implementation/provider) to make use of that feature. 

Ok, let's get back to the question.  I'm thinking of redesigning my data layer, but i have recently just been arriving at the same conclusions in terms of necessary functionality.  The Only new thing I think would be nifty would be to add an implementation of my RunSQL() method that used a ThreadPool to make the call asynchronous for the calling app.  I've heard of people writing/implementing their own DataAdapter to add some concurrency checking capabilities, but I'm not convinced this is something I'd ever use.   Does anyone have a favorite database access function that they wrote? If so what does it do?  What's missing in your opinion from data access layers?

Ok, Enough for now.