I'm in the process of adding a Data Access Layer for our ASP.Net 2.0 web application that was written almost exclusively using in-line SQL calls and copious use of copy/paste. Due to business concerns I am only able to re-factor small portions of the application at a time (can't settle in and do a huge re-design), so I have to live with certain design decisions until things are loosely coupled enough to make big changes and test them properly.
I've decided to use the Enterprise Library Data Access Application Block as the plumbing for the Data Access Layer going forward, and one such previous design decision is causing me problems. Currently we get the "main" connection string for our application from an admin database based on the account id provided by the user so that we can allow a single installation of the application to access multiple databases. My problem deals with trying to figure out the best practices way of getting that connection string (or the account id) to the DAL.
The previous implementation was storing the encrypted connection string in a cookie, so my current hack-approach is to get the connection string from there and then use DAAB in the following manner:
Public Shared Function GetResultsByKeywords(ByVal key1 As String, ByVal key2 As String, ByVal key3 As String, ByVal key4 As String) As DataTable
Dim db As SqlDatabase = New SqlDatabase(Connection.GetConnectString())
Dim dt As DataTable = New DataTable
Using dr As IDataReader = db.ExecuteReader("sel_eHELP_ITEMS", key1, key2, key3, key4)
dt.Load(dr)
End Using
Return dt
End Function
Where Connection.GetConnectString() is fetching the connection string from the cookie.
I know this is nowhere near the best way to do this and I need to fix it, but I'm struggling with the "right" way to get it done. Maybe create a base class that I can initialize with the connection string and inherit all other classes from, but I'm not sure how this would look and where I would first initialize an instance of that class. On every page before accessing any DAL functions?
Any guidance would be appreciated. Thank you.
Update
Let me clarify. At most there will be 3 or 4 different connection strings for an installation. The "account id" is not a user id and is used by multiple users to essentially specify which database to connect to.