views:

821

answers:

1

I would like to know, what would be the best way to manage a connection to a database in asp.net.

My application is built using an N-Tier architecture. The DAL consists of static classes with methods such as

Public Shared Sub Delete( _
    ByVal connection As MyConnectionClass, _
    ByVal contact_id As Integer, _
    ByVal contact_timestamp As Date _
)

    With connection.ProcParams
        .Add(New StoredProcParam("p_contact_id", contact_id, OracleDbType.Int32))
        .Add(New StoredProcParam("p_contact_timestamp", contact_timestamp, OracleDbType.Date))
    End With

    connection.Execute("PKG_DATA_ACCESS.DeleteContact")

End Sub

The point to note here, is that I pass the connection to the DAL from the BLL.

Here are the points I have considered, but none of them seems alright to me, so I'm just asking for an advice.

Should I create the connections in the MasterPage, store it in an object, then pass it to my business objects as I create them (the thing I'm trying to avoid)

Should I create a connection using a static class and calling a method like CreateConnection on it, from the constructor of my business objects (Something I'd like, but I don't want to have a connection per object, I'd like it to be shared for all instances of my objects, but since asp.net is multi-threaded, a static class doesn't make sense to store connections)

Ideally, the solution should also work well in a Windows Forms environment (So no connection storing in session, and retrieving it with a static method in a class, for the current context)

+12  A: 

Don't try to persist the connection at all. Instantiate it within each scope that you are using it. .Net will manage a pool of connections for you, wether in windows forms or in asp.net.

Trying to hold onto the connection is a session object or in a master page will actually hurt performance, not to mention introduce other problems.

From BC's comment:

connection strings must be equal strings to end up in the same pool.

Neil N
It's worthwhile to add here that the connection strings must be equal strings to end up in the same pool
BC
well, I didn't think about that, this solution fits my needs, and is simpler in terms of design, I like it :)
Martin
Oh, and thanks for quick response
Martin
@BC, thanks, I wasn't aware of that little tidbit. But i guess in most cases you would use a static global or a resource as the connection string so it would come out equals anyways.
Neil N