I have a web app that is capable of connecting to multiple databases that are identical in structure but contain different data; each of our clients who uses the app gets their own copy of the database to avoid commingling of different clients' data. We determine which database to connect to by looking up the client's ID in a master database.
That sounds like a pretty solid design - however I have a question about what we should do with the connection strings once we retrieve them. Right now most of the data access functions look something like this:
Public Function GetUsersByRole(ByVal roleID As Integer, ByVal connectionString As String) As SqlDataReader ' connect to database using connection string passed in ' execute query ' return results End Function
And we call it like so:
Dim connstr = Session("DotNetConnectionString") ' this session variable is populated from the master database when you log in Dim drAdmins As SqlDataReader = DBFunctions.GetUsersByRole(1, connstr)
However I think it would be cleaner to instead have a central location to retrieve the connection string, so we don't have to keep retrieving it over and over again and passing it in every time we want to do a query:
Public Shared ReadOnly Property ConnectionString As String Get Return Session("DotNetConnectionString") End Get End Property Public Function GetUsersByRole(ByVal roleID As Integer) As SqlDataReader ' connect to database using connection string from shared property defined above ' execute query ' return results End Function
Then all you have to do to call it is:
Dim drAdmins As SqlDataReader = DBFunctions.GetUsersByRole(1)
So much easier!
Does my approach make sense or should I leave things as is? (In general, I'm not changing the existing data access methods; I'm mostly just doing it this way when I create new ones. There are too many data access functions for changing them all to be worthwhile!) I can see one potential disadvantage: it's now impossible to query any client database other than the current client's database. But really, why would you want to?! That sounds like a really bad idea, or a huge mistake waiting to happen! 😉