mustang_larry
asked on
Asp.net on the fly connection string changes
I am building a VS2005 ASP.net C# application that requires database access
with on the fly connection string changes based on user logged in.
All that needs to change is the database name / server name
using a trusted connection and no user password is stored in the string.
This is what I was thinking of doing.. or do you have a better
solution?
Create a class that uses the connection string builder class
look up the user name and the associated db server/dbname stored
in a database table and return the connection string.
On each page that requires access to the db use the class to set the connection string .
Thanks
with on the fly connection string changes based on user logged in.
All that needs to change is the database name / server name
using a trusted connection and no user password is stored in the string.
This is what I was thinking of doing.. or do you have a better
solution?
Create a class that uses the connection string builder class
look up the user name and the associated db server/dbname stored
in a database table and return the connection string.
On each page that requires access to the db use the class to set the connection string .
Thanks
a little flaw in the logic... you need a connection string to connect to a database to look up in the table for a connection string... unless of course you're going to have some kind of system connection string and then customizable customer/user connection string...
Depending on the number of databases it would probably make sense to keep it in web.config file or if it's too big, probably in some separate file (possible xml file as it can be structured in proper data-storage way). having a separate "system" connection is acceptable also, but little bit strange.
Having a separate class for this seems to me a bit overdoing. you can initialize your connection string once when you user logs in, and then you either can pass your connection string along the pages (using session variable) or initialize it on each page.
good luck,
yurich
Depending on the number of databases it would probably make sense to keep it in web.config file or if it's too big, probably in some separate file (possible xml file as it can be structured in proper data-storage way). having a separate "system" connection is acceptable also, but little bit strange.
Having a separate class for this seems to me a bit overdoing. you can initialize your connection string once when you user logs in, and then you either can pass your connection string along the pages (using session variable) or initialize it on each page.
good luck,
yurich
ASKER
Yurich thanks for your ideas
My thought was in the web.config have one connection string to the "master" users
database which the class would use.
The main goal is to have one set of aspx pages for all users.
The number of connection strings would be 20-50 for now but could grow.
I am using the Membership Classes was thinking that adding the server and dbnames
linked to the users id. I want to keep things clean instead of maintaining separate xml file or
web.conf entries since they would really need to be synced when users
are added or removed.
The session variable sounds like a good idea this way it won't have to
make multiple db requests for each page load.. As long as this isn't
a security risk.
My thought was in the web.config have one connection string to the "master" users
database which the class would use.
The main goal is to have one set of aspx pages for all users.
The number of connection strings would be 20-50 for now but could grow.
I am using the Membership Classes was thinking that adding the server and dbnames
linked to the users id. I want to keep things clean instead of maintaining separate xml file or
web.conf entries since they would really need to be synced when users
are added or removed.
The session variable sounds like a good idea this way it won't have to
make multiple db requests for each page load.. As long as this isn't
a security risk.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Anybody else done this? and pros cons other possible methods?
I'm still interested.
Regards,
Yurich
Regards,
Yurich
string u = Request("name");
string cs = "Driver={SQL Server};Server=myServerAdd
SqlConnection sqlconn;
etc..
That would work for your case, but mine was a bit different I did require a specifc password to verify against so I did the same thing though except passed it encrypted.
Hope this helps
Chris