What is the best way to dynamically load connection strings

Question

I've seen some other posts in this topic, but thought I would ask the question with a description on my problem.

I have a login page which uses SQL provider for the user login. Then the user should be redirected to the main application. This is a web based CRM application in which we have multiple clients.

My goal is to use a single web site, but multiple SQL databases. And the user that signs in should use a connection string that corresponds to the company he belongs.

Any good ideas?

you mean, if you have 25 clients, then you have 25 databases? — Muhammad Akhtar
– Muhammad Akhtar, Commented Dec 14, 2009 at 11:49
No, he has 25 groups (companies) and 1 db per group. It's in the question. — Henk Holterman
– Henk Holterman, Commented Dec 14, 2009 at 13:39
Yes, 25 groups(with many clients), then 25 databases and one web site. — espenk
– espenk, Commented Dec 15, 2009 at 14:42

Mark Seemann · Accepted Answer · 2009-12-14 11:51:07Z

1

Would it work to create a simple map between the user's organization and the appropriate database connection?

It could be as simple as a Dictionary<string, string> initialized in global.asax. The key would be the company and the name of a connection string defined in web.config.

If you need to make this configurable, you can load the values for such a dictionary from a custom configuration section.

answered Dec 14, 2009 at 11:51

Mark Seemann

234k51 gold badges448 silver badges778 bronze badges

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

3 Comments

espenk Over a year ago

That could work. But the problem is still to use the connection string from this dictionary to make a connection. I use LINQ for everything and need to be able to refference a connectionstring

Mark Seemann Over a year ago

In both LINQ to SQL and LINQ to Entities the data contexts have overloaded constructors that allow you pass a connection string. You can use those. You can't make it pick up a connection string from .config based on arbitrary logic.

espenk Over a year ago

Okay. I will take a look at this aproach

Anuraj · Accepted Answer · 2009-12-14 11:50:15Z

1

Use ConnectionStrings in Web.Config. Check this link for more information : How to: Read Connection Strings from the Web.config File. You can create a Key as the Name of the organization and value as the connection string that corresponds to the organization.

answered Dec 14, 2009 at 11:50

Anuraj

19.6k7 gold badges61 silver badges84 bronze badges

4 Comments

espenk Over a year ago

This is what I do today. The problem is that all the users are going to use the same web site, but differnet databases depending on the company wich they belong.

Anuraj Over a year ago

I think there is no problem. So how you are authenticating users? may be from a centralized Database right? After authenticating, pick the connection string from the Web.Config.

espenk Over a year ago

You mean I should save all the ConnectionStrings in the Web.Config, and after the user authenticated against the SQL server, then i pick the coresponding connectionString?

Anuraj Over a year ago

Yes. I mean that only - After authentication you can get the organization of the User, and you can load the connection string like connectionstrings[variable].ConnectionString.

Collectives™ on Stack Overflow

What is the best way to dynamically load connection strings

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related