Stored Connection Strings per user

Question

In the past I've used a Singleton Pattern to load the connection string when the application starts via the global.asa file.

I have a project now where each user has a unique connection string to the database. I would like to load this connection string once. The issue is that the singleton pattern will not work for me since each user has there own connection string. Basically the connection string is created dynamically.

I do not want to store it is session. If anyway has a clever way of doing this in .NET let me know ?

We are taking the existing connection string. Then replacing the USER ID and Password with a specific one that is stored in the database. This is so we can track each user that logins to the database. I wish I could set the connection string one time when the user logins and jsut have my code call it when needed. I don't want to have to constantly build it each time I need it. — pehaada
– pehaada, Commented May 24, 2010 at 20:53

Dean Harding · Accepted Answer · 2010-05-21 22:16:46Z

2

Connections to the database are quite expensive, in terms of resources, and I personally would suggest that you reconsider your requirements of having one per user. Unless you can guarantee that the total number of users is going to be very small (say no more than 5-10).

Having said that, you can just store the connection in the User object that represents your user. Or have a global dictionary that maps user ids to connection strings.

If the only difference between the connection strings is the username/password, you could consider impersonating the client and using Windows authentication in SQL Server instead.

answered May 21, 2010 at 22:16

Dean Harding

72.9k13 gold badges151 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pehaada Over a year ago

What do you mean in the USER object ? Right now the USERID is stored in session. I don't want to store the connection sting in session.

Collectives™ on Stack Overflow

Stored Connection Strings per user

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related