CODEWITHSUNDEEP
c#entity-frameworkef-code-firstmigrationentity-framework-migrations
Kernel
Kernel

Reputation: 105

Using different connectionString when migration is needed or not

I'm writing a webapp with Entity Framework 6.1.3 Code First and using SQL Express Management Studio 2014. I'm using migrations for update the DB everytime my application grows. That looks great and works very good on my own db. But when I'm going to put this application on production server, I want to use two different users while running the application, a db_owner user that can alter the db structure and a db_read/write end-user. For doing this I had 2 options:

1) Using two web.config files, with different connectionString inside that specify the user I need. In this way, whenever I have a migration, I run first the db_owner build and, after the update, deploy again the db_read/write one;

2) Find a way to let the application know that there are some pending migrations and connect first with the db_owner user, then switch user and continue the execution.

The second is obviously the best choice.

Looking around I found out some stuff that didn't worked for me.

I've added in the web.config file:

<add name="UserWithPrivilegesConnectionString" connectionString="Data Source=.\SQLEXPRESS;   Initial Catalog=MyDb-test; Persist Security Info=True; MultipleActiveResultSets=True;  User ID=userWithPrivileges; Password=pass" providerName="System.Data.SqlClient" />
<add name="RandomUserConnectionString" connectionString="Data Source=.\SQLEXPRESS;   Initial Catalog=MyDb-test; Persist Security Info=True; MultipleActiveResultSets=True;  User ID=user; Password=pass" providerName="System.Data.SqlClient" />

Then in myInitializer.cs

class myInitializer : System.Data.Entity.MigrateDatabaseToLatestVersion<MyDbContext, Configuration>
public override void InitializeDatabase(MyDbContextcontext)
    {
        try
        {
            context.SetConnectionString(System.Configuration.ConfigurationManager.ConnectionStrings["RandomUserConnectionString"].ConnectionString);
            if (!context.Database.Exists() || !context.Database.CompatibleWithModel(false))
            {
                var configuration = new Configuration();
                var migrator = new DbMigrator(configuration);
                configuration.TargetDatabase = new DbConnectionInfo(System.Configuration.ConfigurationManager.ConnectionStrings["UserWithPrivilegesConnectionString"].ConnectionString, "System.Data.SqlClient");
                var migrations = migrator.GetPendingMigrations();
                if (migrations.Any())
                {
                    var scriptor = new MigratorScriptingDecorator(migrator);
                    string script = scriptor.ScriptUpdate(null, migrations.Last());
                    if (!String.IsNullOrEmpty(script))
                    {
                        context.Database.ExecuteSqlCommand(script);
                    }
                }
            }
            base.InitializeDatabase(context);

Where context.Database.ExecuteSqlCommand(script); executes, I get:

Exception thrown: 'System.Data.Entity.Core.EntityException' in EntityFramework.SqlServer.dll
Additional information: The underlying provider failed on Open.

The reason is that context still has the RandomUserConnectionString inside and can't find any way to change it. I tried also to add a setConnectionString method for context but no way. I've also tried another approach, still in myInitializer.cs

var configuration = new Configuration();
            var migrator = new DbMigrator(configuration);
            configuration.TargetDatabase = new DbConnectionInfo(System.Configuration.ConfigurationManager.ConnectionStrings["UserWithPrivilegesConnectionString"].ConnectionString, "System.Data.SqlClient");
            var migrations = migrator.GetPendingMigrations();
            if (migrations.Any())
            {
                 migrator.update();
            }
       }
       base.InitializeDatabase(context);

but nothing. I've also tried to close, set connectionString and open explicitly connection.

FYI, MyDbContext was

public MyDbContext() : base("name=UserWithPrivilegesConnectionString")
    {
        Configuration.LazyLoadingEnabled = false;
    }


    public MyDbContext(string nameOrConnectionString) 
        : base(nameOrConnectionString)
    {
        //disable initializer if necessary
        Configuration.LazyLoadingEnabled = false;
    }

and now I've removed the argument from the first base constructor.

Any ideas?

Upvotes: 1

Views: 1280

Answers (1)

Francesc Castells
Francesc Castells

Reputation: 2847

By default EF migrations uses the same connection string you use in your DbContext, but you can change it in your DbMigrationsConfiguration subclass (it's called Configuration and it is created in the Migrations folder when you enable migrations on a project). In the constructor, just add:

TargetDatabase = new DbConnectionInfo(ConfigurationManager.ConnectionStrings["UserWithPrivilegesConnectionString"].ConnectionString, "System.Data.SqlClient");

Now your migrations will use this connection string and your application will use whatever connection string you pass in the constructor.

Regarding your Database Initializer, I don't normally use the MigrateDatabaseToLatestVersion, but I believe it will do what you need without customizing anything.

As a side note, if you have control of the web app live environment, I think it's safer to generate the sql scripts from the migrations and apply them manually to your live DB. This way you can fine tune the generated scripts. This is specially interesting when you need to do major changes on existing tables with data.

Upvotes: 1

Related Questions