Connectionstring depending on logged in user

Hi,

I have an asp.net site that hosts codes for several different organisations. I would like to be able to form the connection string differently depending on the user that is logged in, since each organisation has their own database, but uses the same asp.net code.

For example a connection string can look like this for Organisation A:
 <add name="dbConn" connectionString=" Data Source=.\SQLEXPRESS;Initial Catalog=db1DBLocal;Integrated Security=True" providerName="System.Data.SqlClient" />

Open in new window


And for Organisation B:
 <add name="dbConn" connectionString=" Data Source=.\SQLEXPRESS;Initial Catalog=db2DBLocal;Integrated Security=True" providerName="System.Data.SqlClient" />

Open in new window


It's important that the name of the connection is the same but that the catalog can be altered.

Worth mentioning is that I also in the current system has added a class called config.vb. In that class I have put the following code to easily be able to use the connection string in my code behind pages:
 Public Shared ReadOnly Property ConnectionString() As String
        Get
            Return ConfigurationManager.ConnectionStrings("dbConn").ConnectionString     
        End Get
    End Property

Open in new window


So if the solution in some way can be compatible with this waqy of working it would be great!

I have two parameters called systemID and one called systemDatabase as profiles in the system and those parameters decides what system and what database the user belongs to.

If someone has a good solution to how I easily can solve this I would be most grateful.

Thanks for help!

Peter
Peter NordbergIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
Well you need to be able to change following

 Public Shared ReadOnly Property ConnectionString() As String
        Get
            Return ConfigurationManager.ConnectionStrings(OrganisationSpecificConnectionStringName).ConnectionString    
        End Get
    End Property


How you get that OrganisationSpecificConnectionStringName depends on how you link your logged in user to organisation.
LordWabbitCommented:
A minor change in your config
Return ConfigurationManager.ConnectionStrings("dbConn").ConnectionString .Replace("db1DBLocal", "db2DBLocal")
ArkCommented:
Public Shared ReadOnly Property ConnectionString(ByVal userCatalog As String) As String
    Get
        Dim csb As New SqlClient.SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings("dbConn").ConnectionString)
        csb.InitialCatalog = userCatalog
        Return csb.ConnectionString
    End Get
End Property

Open in new window

Or
Public Shared Property UserCatalog As String
Public Shared ReadOnly Property ConnectionString() As String
    Get
        Dim csb As New SqlClient.SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings("dbConn").ConnectionString)
        If Not String.IsNullOrEmpty(UserCatalog) Then csb.InitialCatalog = userCatalog
        Return csb.ConnectionString
    End Get
End Property

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.