Multiple database with ASP.NET

Posted on 2009-02-14
Medium Priority
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
  • 3
  • 2
LVL 15

Expert Comment

ID: 23643360
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"/>


Author Comment

ID: 23643376
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

aibusinesssolutions earned 2000 total points
ID: 23643427
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.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 15

Expert Comment

ID: 23643434
LVL 26

Expert Comment

by:Anurag Thakur
ID: 23643587
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

Author Closing Comment

ID: 31547027
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month16 days, 17 hours left to enroll

862 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