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)