We have a SQL utility class that takes the name of a stored procedure an its input parameters, and returns the results in datatable. The reasoning behind this is so that we don't have to worry about forgetting to close connections and having connection leaks. Also so that we can reduce code by not having to recreate datadapters and datareaders in our data access layers.
The problem I have with this is that we're populating a datatable so that we can loop through it to create our objects, so we're basically using it like a datareader. I've read about classes that will return a datareader or dataadapter. But the problem with this is either client has to open and close connections, or you have to close the connection in a Finalize method. It seems that you wouldn't want garbage collection being responsible for closing your database connections.
To sum up, we want to have a class so that we can reduce code by not having to create datareaders for every query and so that we can ensure database connections are closed.
What is the best way of handling this?
UPDATE: Still thinking about this, but so far it seems that the best practice is to still return a datareader, use CommandBehavior.CloseConnection, and then trust who ever uses the class to call dr.Close()?