• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Multiple database with ASP.NET

Hello All,

I'm in the process of creating an application where there is a central 'global' database which contains all the aspnet Membership and Role info as well as info which is global to the application as well as multiple 'client' databases each responsible for containing the individual client's data.

Each of the client DB's will be identical in schema (tables, stored procs, etc) but will have different permissions:


User1 is permtted to update ClientA and ClientC and should therefor have access to client database A & C
User2 is permitted to update only ClientB and only has access to database B
User3 is permitted to update all clients and therefor has access to all client databases.

What I'm trying to work out is how best to achieve the accesses to the individual databases.  The intended process for handling user access would be the following:

User Login > 
    User redirected to main user page > 
        User selects client to work with > 
            User redirected to client's main page

One of the main thoughts I had was to stored the connection string for the selected client in a session variable which is specific to the users session however I'm wondering if there is a better way.

  • 3
  • 2
1 Solution
Just create multiple connection strings in the web.config file, and then call on the appropriate one in the client's main page.

    <add name="ClientAConnectionString" connectionString="Data Source=server;Initial Catalog=ClientA;Persist Security Info=True;User ID=user;Password=pass" providerName="System.Data.SqlClient"/>
    <add name="ClientBConnectionString" connectionString="Data Source=server;Initial Catalog=ClientB;Persist Security Info=True;User ID=user;Password=pass" providerName="System.Data.SqlClient"/>
    <add name="ClientCConnectionString" connectionString="Data Source=server;Initial Catalog=ClientC;Persist Security Info=True;User ID=user;Password=pass" providerName="System.Data.SqlClient"/>

Anubis2005Author Commented:
I had thought about this but there could be quite a large number of clients and managing this would become very difficult.  I really need to opt for something dynamically controlled.
I see.  If you have a main database table with the client info, I would store the connection string for each client there.

After a user clicks on the client, you could then have it write to an Application variable, which will be available globally until the application restarts.

Just check to see if it exists first, if it doesn't, create the application variable.  You could also look at the Cache() class, I'm not sure which would be better performance wise.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Anurag ThakurTechnical ManagerCommented:
create multiple connection string in the web config as suggested
i am assuming that when you create roles and permissions for the user you have the information which user has access to which databases

when the user logs in bring all the role and permission related data and save in the session object as it will be applicable to the complete session
when the user wants to take an action fetch the information from the session to se whether the user has access to that database and if yes then use the connection string from the web config
Anubis2005Author Commented:
This is the sort of thing I was thinking of, however I'll alter the result to use a session variable instead as the client's db connections should only be unique to that client.  Thanks for the assistance.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now