.NET - Users and different databases

Question

Hello all,

I'm looking at having certain users access one database and other users accessing another database based on the company they belong to. What would be the best way to handle the connection strings and make sure the user connects to the right db when they login?

Thanks for any ideas.

Answer 1

Have a database table that stores the connection info for each user. Maybe something like the Provider Model would be useful?

Answer 2

I would use a dynamic connection string builder, and a master Database that mapped user accounts to other databases.

Instead of storing all those connection strings, I'd use my connection string builder method to create the connections as needed based on info from the master. I'd also use Windows Authentication, so as to avoid storing passwords in your database.

Answer 3

In Web.Config or App.Config

<connectionStrings>
  <add name="ConnectionForDudes" providerName="System.Data.SqlClient"
      connectionString="Data Source=___MALECONNECTIONHERE___"/>
  <add name="ConnectionForChicks" providerName="System.Data.SqlClient"
      connectionString="Data Source=___FEMALECONNECTIONHERE___"/>
</connectionStrings>

When it's time to open the database, you get your connection string this way:

bool UserIsMale = true;
string ConnectionStringName = "ConnectionFor" + UserIsMale ? "Dudes" : "Chicks";
string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;