How do I programmatically set the connection string for Entity-Framework Code-First?

Question

I'm trying to write some code that allows me to switch between SQLCE (locally on my dev machine) and full SQL (on AppHarbor). With SQL CE, the connection string is all handled for me, but I have to construct it myself for SQL. My code so far is below, however it gives this error:

Keyword not supported: 'metadata'

I've been looking online for hours, but all the solutions involve using a "ContextBuilder" class which I can't find (I've installed EF via the NuGet package).

Here's the current code (running at startup via WebActivator):

public static void Start()
{
    // Read the details from AppSettings. Locally, these will be empty.
    var databaseHost = ConfigurationManager.AppSettings["DatabaseHost"];
    var databaseName = ConfigurationManager.AppSettings["DatabaseName"];
    var databaseUsername = ConfigurationManager.AppSettings["DatabaseUsername"];
    var databasePassword = ConfigurationManager.AppSettings["DatabasePassword"];

    // Check whether we have actual SQL Server settings.
    if (!string.IsNullOrWhiteSpace(databaseHost) && !string.IsNullOrWhiteSpace(databaseName))
    {
        // Set up connection string for a real live database :-O
        var connectionString = string.Format("metadata=res://*/DB.csdl|res://*/DB.ssdl|res://*/DB.msl;"
            + "provider=System.Data.SqlClient; provider connection string='Data Source={0};"
            + "Initial Catalog={1};User ID={2}; Password={3};MultipleActiveResultSets=True'",
            databaseHost, databaseName, databaseUsername, databasePassword);

        Database.DefaultConnectionFactory = new SqlConnectionFactory(connectionString);
    }
    else
    {
        // Set a custom database initializer for setting up dev database test data.
        Database.SetInitializer<BlogDataContext>(new BlogDataIntializer());

        // Set the connection factory for SQL Compact Edition.
        Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
    }
}

Answer 1

you should use the EntityConnectionStringBuilder class

string providerName = "System.Data.SqlClient";
string serverName = ".";
string databaseName = "AdventureWorks";

// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();

// Set the properties for the data source.
sqlBuilder.DataSource = serverName;
sqlBuilder.InitialCatalog = databaseName;
sqlBuilder.IntegratedSecurity = true;

// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();

// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();

//Set the provider name.
entityBuilder.Provider = providerName;

// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;

// Set the Metadata location.
entityBuilder.Metadata = @"res://*/AdventureWorksModel.csdl|
                        res://*/AdventureWorksModel.ssdl|
                        res://*/AdventureWorksModel.msl";
Console.WriteLine(entityBuilder.ToString());

using (EntityConnection conn =
new EntityConnection(entityBuilder.ToString()))
{
conn.Open();
Console.WriteLine("Just testing the connection.");
conn.Close();
}

Answer 2

In Entity-Framework Code-First use SqlConnection. You cannot use EntityConnectionStringBuilder because in code-first there is no metadata files.

Answer 3

You can also set it directly on your context. You have to attach the mdf to the SqlServer instance you want to use first. Not exactly elegant but it worked for me

public void DoImportWork()
{
   var ctx = new StatisticsContext(); << your DbContext 

   ctx.Database.Connection.ConnectionString = @"Data Source=localhost\SQLEXP;AttachDbFilename=""C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXP\MSSQL\DATA\StatisticsData.mdf"";Integrated Security=True";

   ctx.Database.Connection.Open();
}

as usual EF will auto-generate everything when you add your first row to the context.

Answer 4

This code will come handy for creating connection string with Entity Framework

    public string GenerateEFConnectionString(string serverName, string dbName,string ModelName,string userName,string password)
    {   
        // Initialize the connection string builder for the underlying provider.
        SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = dbName;
        //sqlBuilder.IntegratedSecurity = false;
        sqlBuilder.UserID = userName;
        sqlBuilder.Password = password;
        sqlBuilder.MultipleActiveResultSets = true;

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        entityBuilder.Provider = "System.Data.SqlClient";
        entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

        // Set the Metadata location.
        entityBuilder.Metadata = string.Format("res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl",ModelName); 
        return (entityBuilder.ToString().Replace("\"","""));
    }

It can be called from a set of parameters as

    GenerateEFConnectionString("srv","db","mod","vinodsrivastav","nopassword");

to generate a connection string like this

metadata=res://*/mod.csdl|res://*/mod.ssdl|res://*/mod.msl;provider=System.Data.SqlClient;provider connection string="Data Source=srv;Initial Catalog=db;User ID=vinodsrivastav;Password=nopassword;MultipleActiveResultSets=True"