Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Managing connection to database in an asp.net application

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)

like image 672
Martin Avatar asked Mar 17 '09 14:03

Martin


1 Answers

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.

like image 92
Neil N Avatar answered Oct 18 '22 23:10

Neil N