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");
    }
}

Ghyath Serhal · Accepted Answer · 2011-05-14 16:17:15Z

45

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();
}

answered May 14, 2011 at 16:17

Ghyath Serhal

7,6326 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Danny Tuppeny Over a year ago

Thanks, I got further, but it does on CREATE DATABASE. Installing SQL Express so I can script the DB - but I don't understand what the Metadata property should be set to for Code-First

Danny Tuppeny Over a year ago

Works fine. The metadata property I included must work, even though I've no idea where it's finding those metadata files :D

Ghyath Serhal Over a year ago

usually these metadata are read from the configuration file. In my case i removed them the configuration file and read them from the database.

Christopher Stevenson Over a year ago

These metadata files are generated by Entity Framework dynamically upon the first connection to the database.

Ghyath Serhal Over a year ago

You should use the below properties, sqlBuilder.UserID = "userId"; sqlBuilder.Password = "password";

|

rraszewski · Accepted Answer · 2013-03-15 16:12:21Z

13

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

answered Mar 15, 2013 at 16:12

rraszewski

1,1457 silver badges21 bronze badges

1 Comment

Piotr Kula Over a year ago

This helped me figure out that I needed Entity string builder for database first :) +1

user2882366 · Accepted Answer · 2014-09-26 13:31:21Z

8

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.

answered Sep 26, 2014 at 13:31

user2882366

1011 silver badge2 bronze badges

Comments

Vinod Srivastav · Accepted Answer · 2019-06-21 13:05:48Z

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("\"","&quot;"));
    }

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=&quot;Data Source=srv;Initial Catalog=db;User ID=vinodsrivastav;Password=nopassword;MultipleActiveResultSets=True&quot;

Collectives™ on Stack Overflow

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

4 Answers 4

10 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related