Dynamic Connection String for EF Code First

Question

My current connection string that resides in Web.config is this :

  <add name="PersonDBContext" 
       connectionString="Server=111.111.1.11;
       Database=MyProgram;
       User Id=admin;
       Password=12345;
       Integrated Security=False" 
       providerName="System.Data.SqlClient" />

I want to give the same connection string to the program in a dynamic way, this is my attempt :

    EntityConnectionStringBuilder csb = new EntityConnectionStringBuilder();

    csb.ProviderConnectionString = "Data Source=111.111.1.11;Initial Catalog=MyProgram;User Id=admin;Password=12345;Integrated Security=False";
    csb.Provider = "System.Data.SqlClient";

    String entityConnStr = csb.ToString();
    return entityConnStr;

And this is what I get :

Keyword not supported: 'provider'.

Can you tell me what I am doing wrong? And do I need metadata for Code First connection string? Thanks.

EDIT : I figured that I either shouldn't use EntityConnectionStringBuilder or I should give a Metadata for the EntityConnectionStringBuilder class. Can you tell me one of the ways to how to do this?

Answer 1

Why not pass it as a plain string in the DbContext-derived class' constructor? Like

var ctx = new MyContext("Data Source=111.111.1.11;Initial Catalog=MyProgram;User Id=admin;Password=12345;Integrated Security=False");

Answer 2

What I had to do to have a dynamic connection string, so that users were able to key in or select the server they wanted to connect to, was the following:

In the Model.Context.cs file that EF creates I changed the constructor to:

public partial class Entities : DbContext
{
    public Entities()
        : base(BuildConnectionString)
    {
    }
    ...
}

And then I wrote an extension class EntitiesEx.cs

partial class Entities : DbContext
{
    private static string BuildConnectionString 
    {
        get
        {
            // Specify the provider name, server and database.
            string providerName = "System.Data.SqlClient";
            string serverName = DatabaseController.Server;
            string databaseName = <DatabaseName>;

            // 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.UserID = <user>;
            sqlBuilder.Password = <password>;

            sqlBuilder.IntegratedSecurity = false;

            sqlBuilder.PersistSecurityInfo = true;

            sqlBuilder.MultipleActiveResultSets = 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;

            //assembly full name
            Type t = typeof(Entities);
            string assemblyFullName = t.Assembly.FullName.ToString();

            // Set the Metadata location.
            entityBuilder.Metadata = string.Format("res://{0}/", //Models.Model.csdl|Models.Model.ssdl|Models.Model.msl", 
                assemblyFullName);

            try
            {
                //Test de conexion
                using (EntityConnection conn = new EntityConnection(entityBuilder.ToString()))
                {
                    conn.Open();

                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Connection error" + ex.Message);
            }

            return entityBuilder.ToString();
        }
    }

As a con, every time you generate your model from the database, you'll have to change your constructor in the Entities class (Model.Context.cs)

Answer 3

Dynamic connection string for Code First use simple sql connection string or not EntityConnectionStringBuilder. So you can achieve it as following way.

public static string  DynamicConnectionString(SqlConnectionStringBuilder builder)
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = "ServerName";
    builder.InitialCatalog = "DatabaseName";
    builder.UserID = "UserId";
    builder.Password = "Password";
    builder.MultipleActiveResultSets = true;
    builder.PersistSecurityInfo = true;    
    return builder.ConnectionString.ToString();
}

Answer 4

It was simply as this :

string entityConnStr = "Data Source=111.111.1.11;Initial Catalog=MyProgram;User Id=admin;Password=12345;Integrated Security=False";