How to handle connections in data access layer ( .net )

Question

I am writing a data access layer. I am confused about managing connections in the system. I know that .net uses connection pooling. But I do not want to open and close database connections in all dml operations or in all sql query's. How can I handle this? Where and when ( maybe in global asax which uses data access layer or in the data access layer ) the connections should be managed?

Answer 1

You should open and close sql connections for each query, unless you are running a batch of statements.

"Open late, close early" is how you should always handle database connections.

If you are going about it in the traditional manner (making your own queries), MS has already written a nice data access interface. The enterprise library (application blocks) for data have all of the nice well formed bells and whistles.

If you dont want to bother with writing queries, I suggest you look at linq2Sql or linq2EF (preferred). They will greatly simplify your coding.