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 ?

Answer 1

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.