CODEWITHSUNDEEP
c#entity-framework
Jason Zambouras
Jason Zambouras

Reputation: 182

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

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.

Upvotes: 5

Views: 12827

Answers (5)

Jas
Jas

Reputation: 546

For passing connectionstring to ObjectContext derived class, I found a solution from the following link:

http://blogs.msdn.com/b/rickandy/archive/2008/12/09/explicit-connection-string-for-ef.aspx

Upvotes: 0

Sugar Bowl
Sugar Bowl

Reputation: 1792

Try this:

In web.confige, create a copy of your existing connection string and give it a different name. Change the connection string attributes for this new connection string. e.g. If you previously had a connection string declared as 

   <add name="MyEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MySQL1;initial catalog=MyDB;user id=sa;password=password123;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Add another one giving it a new name 

<add name="MyEntities2" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MySQL2;initial catalog=MyDB;user id=sa;password=password987;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Then in your code files, add the "using System.Data.EntityClient;" Namespace. Declare an entityConnection object and assign the default server.

EntityConnection econn = new EntityConnection("name=MyEntities");

YOu can access the default DB using the following code

using (var _context = new MyEntities(econn))
        { .......}

Later in code you can switch between the servers using the following code:

econn = new EntityConnection("name=MyEntities2");

YOu can access the second DB using the following code

using (var _context = new MyEntities(econn))  <== This will point to second server now.

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364359

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.

Upvotes: 7

StriplingWarrior
StriplingWarrior

Reputation: 156624

Entity contexts have constructors that take the connection string as an argument. I believe you can either provide the string itself, or the name of a string from your connectionStrings element in your config files.

So the trick is to avoid creating your contexts by just saying new EntityContext(). One simple way to do this is to use a factory.

public class ContextFactory : IContextFactory
{
    public EntityContext Get()
    {
        var connectionString = ... // Do some logic to get the connection string
        return new EntityContext(connectionString);
    }
}

...

using (var context = new ContextFactory().Get())
{
    ...
}

The ideal way to use this is in connection with dependency injection, so that you're not even tightly coupled with the new ContextFactory() implementation:

public class MyRepository : IMyRepository
{
    private readonly IContextFactory _contextFactory;
    public MyRepository(IContextFactory contextFactory)
    {
        _contextFactory = contextFactory;
    }
    public GetObjects()
    {
        using(var context = _contextFactory.Get())
        {
        }
    }
}

This gives you ultimate flexibility when it comes to deciding how your contexts will be instantiated.

Upvotes: 3

Davide Piras
Davide Piras

Reputation: 44605

I have done this before, just pass the connection string in one of the overloaded constructor of the auto generated DbContext derived class.

Upvotes: 3

Related Questions