Is this a good way to close DB Connection in ASP.NET?


B

Bob

Is this a good way to close DB Connection in ASP.NET?

I've been trying to come up a way to close DB connection automatically (or
sort of) in my ASP.NET application without having to write the close()
method everywhere. Every web page in my application needs to call the
database, and all DB access and SQL Command code are wrapped in a separate
class (called DBAccess below). I don't want to open and close the
connection within every public method of the DBAccess class as some of the
pages need to call several methods. My goal is to make one page instance
create one instance of the DBAccess and use only one connection, and no need
for page developer to explicitly open and close the underlying connection
(as it should be wrapped inside DBAccess). Here's what I came up with and
would like to get some comments on whether this is a good approach.

---------------------------------------------------------------
/*******************************************************
The DBAccess class implements IDisposable so the public Dispose
method can be called to close the connection.
********************************************************/
public class DBAccess : IDisposable {
private System.Data.SqlClient.SqlConnection _conn;

public DBAccess() {
//Initialize _conn and call _conn.Open()
}

public System.Data.DataTable SomeDBCall() {
//Call a Stored Proc to return a DataTable
}

public void AnotherCall() {
//Call a Stored Proc to update a table
}

//more methods.........

public void Dispose() {
_conn.Close();
}
}

/*********************************************************
A sample web page class. The using statement guarantees the
DBAccess.Dispose() would be called. Alternatively, the Page
class' Dispose method can be overridden to call DBAccess.Dispose().
Probably more flexible to override Page.Dispose
*********************************************************/
public class MyWebPage : System.Web.UI.Page {
private DBAccess _db = new DBAccess();

private void Page_Load(object sender, System.EventArgs e) {
//Do something....
using (_db) {
//execute calls to the DB;
}
//Do some other thing.....
}
}
 
Ad

Advertisements


Top