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

Discussion in 'ASP .Net' started by Bob, Jan 17, 2004.

  1. Bob

    Bob Guest

    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.....
    }
    }
     
    Bob, Jan 17, 2004
    #1
    1. Advertisements

  2. Bob

    Alvin Bruney Guest

    yes that will work
     
    Alvin Bruney, Jan 17, 2004
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.