One thing that keeps stumping me, and I do not see much mention of it in books/blogs, is how to handle DB operations in a system that really don't fall under the jurisdiction of DAOs or Repositories. I like using the approach of generic DAOs/Repositories to handle common DB operations, but what about dealing with things that aren't entities? For example, say I am building a system and in a few cases I need to call a stored procedure to run a batch operation and just return a success code. Or, I need to just load a date from a miscellaneous table. Or, I want to load a list of US states from a table. These cases certainly do occur and they really may not have anything to do with an entity or other object in the system. Without dropping in a nasty "misc" DB class that forgoes something like NHibernate to manually use ADO.NET to do these types of operations, what are some other standard approaches from the OOP crowd?
Bypassing the DAO and working directly with the ADO connector (or native driver) is exactly what everyone does, and there is nothing "nasty" or wrong about it. If these are indeed edge cases for you, then what kind of framework would you expect? What is worse is when people wrap all kinds of weird shenanigans around their DAO to do something it sucks at just in the name of "not going around <insert DAO here>".
I mean if you have a stored proc, then you have obviously decided that DB agnosticism is out the window (it's a overrated goal anyway) so why have misgivings about using ADO.Net? Just make it very explicit in the code, don't hide it. Say it loud and proud "I'm using the database, and I don't give a flick what anyone thinks!". Oh and please make sure its still seperated from the rest of your logic. I don't want my unit tests to get slow because of your stored proc.