How to use a SQL connection string with ADO.NET Entity Data Model

Question

I am trying to use the ADO.NET Entity Data Model in a way that I can on the fly change which database I point too. Changing databases may require an entirely new connection string. Some databases are on different servers. So I need the ability to pass my ADO.NET Entity Data Model a custom connection string formated like so 'server=severaddress;database=database1;User ID=test;Password=test1234;'

Edited: My Entity Class implements ObjectContext. The three constructers I can use is the default, pass in connectionString, pass in an EntityConnection. When ever I use the overload constructers I get errors saying it doesn't recognize "server" in the connectionstring.

I need to either instantiate my repository with a custom connection string, or be able to set it before use.

Answer 1

ObjectContext accepts entity connection string in its constructor. Entity connection string consists of three parts:

• Metadata location (location of mapping XMLs produced by EDMX file)
• Data storage provider
• Data store connection string (that is what you want to provide)

You have several ways to achieve what you want. Generally what you need is combine two parts of connection string:

string format = "metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=\"{0}\"";
string connectionString = "server=severaddress;database=database1;UserID=test;Password=test1234;"

var context = ModelContext(String.Format(format, connectionString));

The format describes location of metadata from Model.edmx included as resources in assembly and Sql provider. Second part is your connection string.

Be aware that this will only work if all your databases have same schema and use same provider.