Entity Framework runtime connection string

Question

I'd like to supply the connection string for my database at runtime. I am using the Entity Framework. This is what I have so far

class MyClassDBContext:DbContext
{
  public MyClassDBContext(string str) : base(str)
  {
    this.Database.Connection.ConnectionString = str;
  }
}

To use the above code, I tried

//create connection string
EntityConnectionStringBuilder myConn = new EntityConnectionStringBuilder();
myConn.Provider = "System.Data.SqlClient";
myConn.ProviderConnectionString = "user id=xxxx;password=xxxx;server=localhost;database=xxxx;connection timeout=30";

//inject the connection string at runtime
MyClassDBContext a = new MyClassDBContext(myConn.ToString())

The above code gave me an error saying "Provider keyword not supported". To attempt to debug this error, I tried the following

 MyClassDBContext a = new MyClassDBContext("metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=user id=xxxx;password=xxxx;server=localhost;database=xxxx;connection timeout=30")

Now, I got an error saying "metadata keyword not supported". So I changed my code to

MyClassDBContext a = new MyClassDBContext("provider=System.Data.SqlClient;provider connection string=user id=xxxx;password=xxxx;server=localhost;database=xxxx;connection timeout=30")

Now I got an error saying "provider keyword not supported". So I again changed my code to

 MyClassDBContext a = new MyClassDBContext("user id=xxxx;password=xxxx;server=localhost;database=xxxx;connection timeout=30")

and now it works!. My question is : how do I specify the provider and metadata at runtime? It looks like only the connection string is being accepted. I am using Entity 4.3.1 from Nuget.

Thanks

Answer 1

edmx file based EF require the "Provider" and "Metadata" content. Code-first based EF doesn't require this, requiring only the regular connection string. You could use a SqlConnectionBuilder (instead of EntityConnectionStringBuilder) to build this normal connection string if you'd like. But as you've seen, you need only specify the actual connection details. The Provider and Metadata aren't needed in EF 4.3.1's DbContext Code-first paradigm.

Answer 2

The EntityConnectionStringBuilder class can be used to to specify provider and metadata at runtime

e.g.

var entityConnectionStringBuilder= new EntityConnectionStringBuilder(); entityConnectionStringBuilder.Provider = "System.Data.SqlClient"; entityConnectionStringBuilder.Metadata = "res:///Example.csdl|res:///Example.ssdl|res://*/Example.msl";

Please see for more on CSDL, SSDL & MSDL in Metadata

Answer 3

It's old question but maybe it will be useful for someone

 var provider = (DbProviderFactory)System.Data.Entity.DbConfiguration
            .DependencyResolver
            .GetService(typeof(DbProviderFactory), "invariant provider name");

        var conn = provider.CreateConnection();
        //conn.ConnectionString = "sample connection string";

        DbInterception.Dispatch.Connection.SetConnectionString(conn, new DbConnectionPropertyInterceptionContext<string>()
            .WithValue("sample connection string"));

return new SampleDbContext(conn,true);

Answer 4

Building on HatSoft's answer:

var entityConnectionStringBuilder= new EntityConnectionStringBuilder();
entityConnectionStringBuilder.Provider = "System.Data.SqlClient";
entityConnectionStringBuilder.ProviderConnectionString = <your SQL Server connection string>;
entityConnectionStringBuilder.Metadata = "res://*";

MyClassDBContext a = new MyClassDBContext(entityConnectionStringBuilder.ToString());

Answer 5

I followed this link

and also this one

How to use EF Code-First without an app.conf file?

Basically what I do is almost like you, create a constructor with a string and calling the base.

But I also set the provider in this constructor.

here's a example

public Context(string ConnectionString) : base(ConnectionString) { 
    Database.DefaultConnectionFactory = new SqlCeConnectionFactory("Oracle.DataAccess.Client"); 
}

That way you can specify the provider. And you won't get the provider keyword error since you don't need to specify in the connection string

Here's how I call it.

var dbCont = new ClassLibrary1.Models.Context("DATA SOURCE=xxx;PASSWORD=xxx;USER ID=xxx");

Hope that helps took me long time to find it