Pass connection string to code-first DbContext

Question

How do I pass a connection string to entity framework's code-first DbContext? My database generation works correctly when both DbContext and the connection string in web.config is in the same project and named the same way. But now I need to move the DbContext to another project so I'm testing passing a connection string to it as follows:

Model & Context

public class Dinner
{
    public int DinnerId { get; set; }
    public string Title { get; set; }
}

public class NerdDinners : DbContext
{
    public NerdDinners(string connString)
        : base(connString)
    {
    }
    public DbSet<Dinner> Dinners { get; set; }
}

Action

    public ActionResult Index()
    {
        var db = new NerdDinners(ConfigurationManager.ConnectionStrings["NerdDinnerDb"].ConnectionString);

        var dinners = (from d in db.Dinners
                      select d).ToList();
        return View(dinners);
    }

Web.Config

<connectionStrings>
  <add name="NerdDinnerDb" connectionString="Data Source=|DataDirectory|NerdDinners.sdf" providerName="System.Data.SqlServerCe.4.0"/>    
</connectionStrings>

If I set a breakpoint in the action an analyze the db, the connection string is there, but it does not create or find the database or anything.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Answer 1

A little late to the game here, but another option is:

public class NerdDinners : DbContext
{
    public NerdDinners(string connString)
    {
        this.Database.Connection.ConnectionString = connString;
    }
    public DbSet<Dinner> Dinners { get; set; }
}

Answer 2

After reading the docs, I have to pass the name of the connection string instead:

var db = new NerdDinners("NerdDinnerDb");

Answer 3

Thought I'd add this bit for people who come looking for "How to pass a connection string to a DbContext": You can construct a connection string for your underlying datastore and pass the entire connection string to the constructor of your type derived from DbContext.

(Re-using Code from @Lol Coder) Model & Context

public class Dinner
{
    public int DinnerId { get; set; }
    public string Title { get; set; }
}

public class NerdDinners : DbContext
{
    public NerdDinners(string connString)
        : base(connString)
    {
    }
    public DbSet<Dinner> Dinners { get; set; }
}

Then, say you construct a Sql Connection string using the SqlConnectioStringBuilder like so:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(GetConnectionString());

Where the GetConnectionString method constructs the appropriate connection string and the SqlConnectionStringBuilder ensures the connection string is syntactically correct; you may then instantiate your db conetxt like so:

var myContext = new NerdDinners(builder.ToString());

Answer 4

In your DbContext, create a default constructor for your DbContext and inherit the base like this:

    public myDbContext()
        : base("MyConnectionString")  // connectionstring name define in your web.config
    {
    }

Answer 5

from here

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["BloggingDatabase"].ConnectionString);
    }

note you may need to add Microsoft.EntityFrameworkCore.SqlServer

Answer 6

I have a little solution example for that problem.

MyDBContext.cs

 public MyDBContext(DBConnectionType ConnectionType) //: base("ConnMain")
  {
      if(ConnectionType==DBConnectionType.MainConnection)
       {
         this.Database.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnMain"].ConnectionString;
       }
      else if(ConnectionType==DBConnectionType.BackupConnection)
       {
         this.Database.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnBackup"].ConnectionString;
       }
  }

MyClass.cs

public enum DBConnectionType
 {
    MainConnection=0,
    BackupConnection=1
 }

frmMyForm.cs

 MyDBContext db = new MyDBContext(DBConnectionType.MainConnection);
                               //or
//MyDBContext db = new MyDBContext(DBConnectionType.BackupConnection);

Answer 7

When using an EF model, I have a connection string in each project that consumes the EF model. For example, I have an EF EDMX model in a separate class library. I have one connection string in my web (mvc) project so that it can access the EF db.

I also have another unit test project for testing the repositories. In order for the repositories to access the EF db, the test project's app.config file has the same connection string.

DB connections should be configured, not coded, IMO.

Answer 8

If you are constructing the connection string within the app then you would use your command of connString. If you are using a connection string in the web config. Then you use the "name" of that string.

Answer 9

Check the syntax of your connection string in the web.config. It should be something like ConnectionString="Data Source=C:\DataDictionary\NerdDinner.sdf"

Answer 10

Can't see anything wrong with your code, I use SqlExpress and it works fine when I use a connection string in the constructor.

You have created an App_Data folder in your project, haven't you?

Answer 11

For anyone who came here trying find out how to set connection string dinamicaly, and got trouble with the solutions above (like "Format of the initialization string does not conform to specification starting at index 0.") when setting up the connection string in the constructor. This is how to fix it:

public static string ConnectionString
{
    get {
        if (ConfigurationManager.AppSettings["DevelopmentEnvironment"] == "true")
            return ConfigurationManager.ConnectionStrings["LocalDb"].ConnectionString;
        else
            return ConfigurationManager.ConnectionStrings["ExternalDb"].ConnectionString;
    }
}

public ApplicationDbContext() : base(ConnectionString)
{
}