dynamic connection string?

[kb]

ASP.NET C#

how can i set this up:

i have a login page

if i login as clientA then i want to use clientA database
if i login as clientB then i want to use clientB database

both databases are on the same sql server, iam thinking could i have a dynamic connection sting in the web.config or somewhere else? so i would have something like this:

Persist Security Info=False;Data Source=local;Initial Catalog= + databaseNameGoesHere +;User ID=user;Password=pwd

any suggestions would be much appriciated

thanks
kb

 

[MasterGaurav]

Make connection string as:

connectionStringKey = ... Initial Catalog = {0}; user=....

When you want to use connection string:

string connString = String.Format(connectionStringKey, database_name);

--
Cheers,
Gaurav Vaish
http://www.mastergaurav.org
http://mastergaurav.blogspot.com
----------------------

 

[Johnpc]

You could maintain a server side xml file for your user like

<users>
<user name='[username]'><sqldb>[dbname]</sqldb></user>
</users>

and return the db name for the user when an as needed using

dbname = xml.selectSingleNode("users/user/[. =
'[username]']/sqlbd").nodeTypedvalue '!?chech my xpath for correctness

and insert it into your connection string

Persist Security Info=False;Data Source=local;Initial Catalog= + dbname
+;User ID=user;Password=pwd

 

[Steve C. Orr [MVP, MCSD]]

here's what the appSettings section of your web.config could look like:

<configuration>
<appSettings>
<add key="DSN" value="Server=(local);Database=DBName;UID=sa;PWD="/>
<add key="OtherConnectionString" value="Server=(local);Database=DBName2;UID=sa;PWD="/>
</appSettings>
</configuration>

This is a nice way to manage it. You can change the connection string
easily without rebuilding the app or restarting IIS or anything, and the
change goes into effect immediately.

Then in your code behinds you can get the connection string like this:
Dim sConn as string
If User = UserA Then
sConn = ConfigurationSettings.AppSettings("DSN")
Else
sConn = ConfigurationSettings.AppSettings("OtherConnectionString")
End If

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net


ASP.NET C#

how can i set this up:

i have a login page

if i login as clientA then i want to use clientA database
if i login as clientB then i want to use clientB database

both databases are on the same sql server, iam thinking could i have a dynamic connection sting in the web.config or somewhere else? so i would have something like this:

Persist Security Info=False;Data Source=local;Initial Catalog= + databaseNameGoesHere +;User ID=user;Password=pwd

any suggestions would be much appriciated

thanks
kb

 

[quaester]

It depends what you want to achieve, the solution suggested by others is
workable.

I've implemented my solution for this scenario by puting my connection
string in database and prior to login to my portal, all database access is
using a minimal rights account, which can execute only a few stored procedure
to get authenticated and retrieve the connection string. In this manner, your
connection string can be really "dynamic".

Another method that you may try out is to use the username and password
(some security stuff need to be taken care here) to login to the sql, if it
throws you out, you are not athorized. And in your sql, you need to setup
those accounts for the users (the logins in sql), and ofcourse, the default
database. That means you will no longer set the initial catalog in your
connection string, but keep the setting in your sql accounts management.

there's not really any right or wrong, or which is best, as it depends on
your architecture and how many users and also how the maintenance work you
would want.