How do I generate a dynamic connection string in Entity Framework 4?

Question

Is it possible to choose a specific database that will be used with the entity framework? At this time, I use the connection string in the Web.Config as database authentication.

I have twelve databases, each of them has the same structure and the same stored procedure.

There is one database for each client. When a client wants to login into the system, he needs to choose his database name from a listbox.

I would like to create a dynamic connection string which will include the database name chosen by the client.

Answer 1

One method might be to load all the 12 choices in a dropdownlist.

When the user makes a choice, put that key into Session.

 Session["UserChosenConnString"] = Request.Form("ddMyDatabaseTarget");

Then supply that conn string to your repo/data layer at runtime.

 string connString = Session["UserChosenConnString"].ToString();
 var db = new CustomerEntities(connString);

There are lots of opportunities to improve this:

• store the 12 choices in a KeyValuePair<int,string>, and store only the key integer in Session. Then look up the value when hitting the database.