How to manage multiple connections for different users

Question

i am working on a CMS solutions for real estate agencies. so different users/groups will use the same tool.

i created 1 asp.net membership Database where i manage ALL users. The users are grouped in different roles.( 1 role = 1 agency office)

Then - for every group i have another Database. In this database i manage the real estates and customers of the given office. (These Databases have the same structure.)

Currently i am using the "custom ASP.NET Profile class" where i store the connectionsstring for the specific database. I create this custom profile if the user logs in.

Now i have the problem, if an anonymous user is visiting the page ( there is a public section ) i get connectionstring errors cause there is no "custom profile" where my functions can read the connectionstring

My Custom Profile Class looks like: Public Class UserProfile Inherits ProfileBase Public Shared Function GetUserProfile(username As String) As UserProfile Return TryCast(Create(username), UserProfile) End Function

    Public Shared Function GetUserProfile() As UserProfile
        Return TryCast(Create(Membership.GetUser().UserName), UserProfile)
    End Function

    <SettingsAllowAnonymous(False)> _
    Public Property role() As String
        Get
            Return TryCast(MyBase.Item("role"), String)
        End Get
        Set(value As String)
            MyBase.Item("role") = value
        End Set
    End Property


    <SettingsAllowAnonymous(False)> _
    Public Property UsersCustomConnectionString() As String
        Get
            Return TryCast(MyBase.Item("UsersCustomConnectionString"), String)
        End Get
        Set(value As String)
            MyBase.Item("UsersCustomConnectionString") = value
        End Set
    End Property


End Class

Then i can read my connection string like

Dim currentprofile As UserProfile = UserProfile.GetUserProfile() 
Dim strcon As String = currentprofile.UsersCustomConnectionString

How could i solve this issue ? Or should i use another way to solve the "many connection strings" issue ? if yes, how ? (i think session varaiables won't work)

Thanks in advance

Answer 1

This is what we do: put in web.config an entry for each connection string in the

<appSettings> 

section like this:

<add key="connection_string_key" value="YourDBServerConnectionString"/>

Then you read the value from your db access class like this:

System.Configuration.ConfigurationManager.AppSettings[connection_string_key]

And if you want to change them dynamically make some factory class where you read them all and return the right connection string based on the role of the current user or if they are logged in or not.