Migration with dynamic connection string in Entity Framework code-first (the connection string from text file)

Question

I'm using EF6 code-first and at the first, I put the connection string in a text file called 'Settings.txt'

The data in the 'Settings.txt' file is

DataProvider: sqlserver
DataConnectionString: Data Source=.\SQLEXPRESS;Initial Catalog=MyDb;Integrated Security=True;Persist Security Info=False;Enlist=False;

Here what I use for the dbContext class:

public class DbDataContext : BaseDbContext
{
    static DbDataContext()
    {
        Database.SetInitializer(new ContextInitializer());
    }

    public DbDataContext():base() { }

    public DbDataContext(string nameOrConnectionString)
        : base(nameOrConnectionString) { }

    ...    
}

[DbConfigurationType(typeof(MyDbConfiguration))]
public abstract partial class BaseDbContext : DbContext, IDbContext
{
    public BaseDbContext() : this(GetConnectionString())
    { }

    public BaseDbContext(string nameOrConnectionString) : base(nameOrConnectionString)
    { }

    public static string GetConnectionString()
    {
        if (DataSettings.DataSettings.Current.IsValid())
        {
            return DataSettings.DataSettings.Current.DataConnectionString;
        }

        throw Error.Application("A connection string could not be resolved for the parameterless constructor of the derived DbContext. Either the database is not installed, or the file 'Settings.txt' does not exist or contains invalid content.");
    }
}

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        IEfDataProvider provider = null;

        try
        {
            provider = (new EfDataProviderFactory(DataSettings.DataSettings.Current).LoadDataProvider()) as IEfDataProvider;
        }
        catch {
        }

        if (provider != null)
        {
            base.SetDefaultConnectionFactory(provider.GetConnectionFactory());
        }
    }
}

public partial class EfDataProviderFactory : DataProviderFactory
{
    public EfDataProviderFactory()
        : this(DataSettings.DataSettings.Current){ }

    public EfDataProviderFactory(DataSettings.DataSettings settings)
        : base(settings) { }

    public override IDataProvider LoadDataProvider()
    {
        var providerName = Settings.DataProvider;

        if (providerName.IsEmpty())
        {
            throw new Exception("Data Settings doesn't contain a providerName");
        }

        switch (providerName.ToLowerInvariant())
        {
            case "sqlserver":
                return new SqlServerDataProvider();

            case "sqlserverce":
                return new SqlServerCeDataProvider();

            default:
                throw new Exception(string.Format("Unsupported dataprovider name: {0}", providerName));
        }
    }
}

public class SqlServerDataProvider : IEfDataProvider
{
    public virtual IDbConnectionFactory GetConnectionFactory()
    {
        return new SqlConnectionFactory();
    }

    public bool StoredProceduresSupported
    {
        get { return false; }
    }

    public DbParameter GetParameter()
    {
        return new SqlParameter();
    }

    public string ProviderInvariantName
    {
        get { return "System.Data.SqlClient"; }
    }
}

I use a static function in 'BaseDbContext' class called 'GetConnectionString()'

This function just for return the connection string from the text file. This behavior is working very well at runtime, but it not working when adding a migration.

This is the problem: how can I add a migration by this way, knowing that when I put the connection string directly in the function like this

    public static string GetConnectionString()
    {
         return (@"Data Source=.\\SQLEXPRESS;Initial Catalog=MyDb;Integrated Security=True;Persist Security Info=False;Enlist=False;");
    }

the Add-Migration command is working

How can I solve this problem without forcing the connection string in the code?

Answer 1

I guess you use the Add-Migration command in the Package Manager Console.

If you are running Add-Migration manually you can just add the connection string as the -ConnectionString parameter:

Add-Migration -ConnectionString "Data Source=.\\SQLEXPRESS;Initial Catalog=MyDb;Integrated Security=True;Persist Security Info=False;Enlist=False;"

You will probably have to add parameter -ConnectionProviderName as well, unless you have a provider defined in your app.config.

I would recommend you to stop using this Settings.txt file and move your connection string to your app.config file, in the section connectionStrings. This is the recommended way to deal with connection strings, much easier than using a custom file like your Settings.txt file.

<connectionStrings>
  <add name="MyLocalDatabase" connectionString="Data Source=.\\SQLEXPRESS;Initial Catalog=MyDb;Integrated Security=True;Persist Security Info=False;Enlist=False;" />
</connectionStrings>

If you do that you can use the parameter -ConnectionStringName in the Package Manager Console, using the name you defined in the app.config:

Add-Migration -ConnectionStringName "MyLocalDatabase"

Also, with the connection string in your app.config file you can add a constructor to your context that receives the connection string name as a parameter and can be used when using the Package Manager console:

public DbDataContext():base("MyLocalDatabase") { }

This will allow you to run your commands in Package Manager Console without specifying any connection string parameters at all. Just make sure the right connection strings are included in the app.config file of the start project selected in the console.

And you can get rid of your GetConnectionString code. You are just re-implementing code that you have out-of-the-box when using app.settings connectionString section. That's how DbContext base constructors parameter NameOrConnectionString is meant to be used. You can provide either a full connection string or the name of a connection string defined in the app.settings file.

Answer 2

I solved this, the problem occurs in getting the file path (text file) in design-mode or even unit tests

string filePath = Path.Combine(MapPath("~/App_Data/"), "Settings.txt");

---

public static string MapPath(string path)
    {
            path = path.Replace("~/", "").TrimStart('/').Replace('/', '\\');

            var testPath = Path.Combine(baseDirectory, path);

            var dir = FindSolutionRoot(baseDirectory);

            if (dir != null)
                {
                    baseDirectory = Path.Combine(dir.FullName, "MyProjectName.WebAPI");
                    testPath = Path.Combine(baseDirectory, path);


            return testPath;
        }
    }

---

private static DirectoryInfo FindSolutionRoot(string currentDir)
    {
        var dir = Directory.GetParent(currentDir);
        while (true)
        {
            if (dir == null || IsSolutionRoot(dir))
                break;

            dir = dir.Parent;
        }

        return dir;
    }

    private static bool IsSolutionRoot(DirectoryInfo dir)
    {
        return File.Exists(Path.Combine(dir.FullName, "MySolutionName.sln"));
    }

---

and by this, we can get the file path in runtime-mode