Multiple database with ASP.NET

Posted on 2009-02-14
Last Modified: 2013-12-17
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.

Question by:Anubis2005
    LVL 15

    Expert Comment

    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"/>

    LVL 1

    Author Comment

    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.
    LVL 15

    Accepted Solution

    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.
    LVL 15

    Expert Comment

    LVL 26

    Expert Comment

    by:Anurag Thakur
    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
    LVL 1

    Author Closing Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now