?
Solved

Asp.net on the fly connection string changes

Posted on 2007-04-03
9
Medium Priority
?
720 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:mustang_larry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
9 Comments
 
LVL 1

Expert Comment

by:cmwhite
ID: 18845954
I personally in the past have done roughly that, except had my Connection String roughly prebuilt so no reason for the connection builder. IE:

string u = Request("name");

string cs = "Driver={SQL Server};Server=myServerAddress;Database=" + u + "Trusted_Connection=Yes";

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
0
 
LVL 21

Expert Comment

by:Yurich
ID: 18846385
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
0
 

Author Comment

by:mustang_larry
ID: 18846553
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.
0
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
LVL 21

Accepted Solution

by:
Yurich earned 500 total points
ID: 18847208
The session variable is quite secure as it's server-based (it does consume some server resources, but it's reasonable in your situation).

I didn't realize it would be 20-50 variations, I thought of 3-4, in whcih case it does make sense to keep your master connection settings in web.config and everything else in a master database.

Cheers,
Yurich
0
 

Author Comment

by:mustang_larry
ID: 18853890
Anybody else done this? and pros cons other possible methods?
0
 
LVL 21

Expert Comment

by:Yurich
ID: 19250700
I'm still interested.

Regards,
Yurich
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

752 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