" Bob" <bobatkpmg@yahoo.com> wrote in message
news:uTo6j9K3DHA.3496@TK2MSFTNGP11.phx.gbl...
> 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.....
> }
> }
>
>
>