We help IT Professionals succeed at work.

Multiple database with ASP.NET

Medium Priority
209 Views
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:

ie:

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.

Thanks
Anubis
Comment
Watch Question

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

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

Author

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Anurag ThakurTechnical Manager

Commented:
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

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.