programmatically manage db connection while using datasets

Question

I was just thinking if there was a way to programmatically open and close connections to the database via the dataset classes we've added into the project.

I am trying to avoid one specific problem. Assume there is an EmployeeTableAdapter. It has two methods IsValid(string EmployeeID) and HasDepartmentAccess(string EmployeeID, string DeptID). Afaik each of these methods, open a connection, does data retrieval, and then closes it.

The opening and closing of the connection is done internally. I kind of want to override this, and close the connection upon a page unload event which (I'd manually register it with the datadapter class somehow).

Answer 1

If you have connection pooling enabled (or disabled, because it's enabled by default) I don't recommend you to close a connection manually. Because now on every "close" connections doesn't becomes closed, they just becomes marked as closed and returns to pool and becomes to be ready to be used into a next query.

Read more at MSDN