Access the database name inside Up in DbMigration

code first migrations existing database
entity framework code first deploy to production
code first migrations to update the database
add-migration does not create table
how to add new table to existing database code first
entity framework core add-migration
update-database connection string
entity framework code first add table to existing database

I have the following code in a code-first migration:

public partial class RunSQLInit : DbMigration
{
    public override void Up()
    {
        this.Sql(SqlFiles.Create_Job_ClearExpiredData);
    }

    [...]
}

where SqlFiles.Create_Job_ClearExpiredData is a resx-file returning an SQL-file as a string. The SQL creates and schedules a Server Agent job in SQL Server.

Now, the SQL have two "variables" I need the ability to change before running the migration on a database: the name of the database the job should run on, and the name for the actual job. That would be fine if I had one environment only, but I deploy this code-first project to a number of environments, who differ in database names and also in what I should call the Job.

I see three possibilities:

  1. Get hold of the connection string currently being used inside the Up()-method, and use that to parse out the database name and do a replace for it to the SQL-string before running it. But how would I go about getting the "connection string currently being used"?
  2. Somehow pass a variable to the migrations when running Update-Database in the Package Manager Console, which could be accessible inside the migrations. How would I go about that?
  3. Use another solution for creating the Job. We rely heavily on the ease of deploying from the code-first models to all environments, so it should be easily automated somehow without additional labour when updating databases, and producing deployment scripts (with Update-Database -script)

A small cut out from the SQL-script in question is this:

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP', 
    @step_id=1,
    @subsystem=N'TSQL',
    @command=N'exec sp_ClearExpiredData;', 
    @database_name=@databaseName, --This is where I need the varying database name!
    @flags=0

The answer to my problem was to simply use this inside the SQL-file:

set @databaseName = db_name()

Since the current context is already the database I will use for the job, it's already there. Too easy.

.net - Access the database name inside Up in DbMigration, The answer to my problem was to simply use this inside the SQL-file: set @​databaseName = db_name(). Since the current context is already the database I will  The point of migrations is to keep the database schema in sync with the model. If this isn't done, your application won't work. The possibilities are: 1) you're trying to retrofit migrations into a database that doesn't have them, in which case you should manually sync both the schemas and the data in __MigrationHistory.

I had to declare my @DBName variable in order to set it. Error otherwise:

Must declare the scalar variable "@DBName".

Complete code:

Sql("DECLARE @DBName nvarchar(50), @SQLString nvarchar(200)\r\n" +
    "SET @DBName = db_name();\r\n" +
    "SET @SQLString = \'ALTER DATABASE [\' + @DBName + \'] COLLATE Latin1_General_100_CI_AS\'\r\n" +
    "EXEC( @SQLString )", suppressTransaction: true);

Code First Migrations with an existing database, The Code First to an Existing Database topic provides detailed This command will create a folder in your solution called Migrations, and put a single class inside it called Comment out all code in the Up method of the newly created When targeting an existing schema, these calculated names may not  Base class for code-based migrations. Entity Framework Migrations APIs are not designed to accept input provided by untrusted sources (such as the end user of an application). If input is accepted from such sources it should be validated before being passed to these APIs to protect against SQL injection attacks etc.

Option 1 is good for your database name. See Is there any connection string parser in C#?

The Job probably needs to come from the config file. Put the name in AppSettings then you can do

string jobName = ConfigurationManager.AppSettings["jobName"]

You could do the same for dbName too of course:

string dbName = ConfigurationManager.AppSettings["dbName"]

I have a config class that looks like this:

public class Config
{
    public static string ConnectionStringName
    {
        get { return GetValue("ConnectionStringName", "SID_2013Context"); }
    }    

    private static string GetValue(string key, string defaultValue)
    {
        return ConfigurationManager.AppSettings[key] ?? defaultValue;
    }
}

My DBContext class constructor looks like this:

    public SID2013Context()
        : base("Name=" + Config.ConnectionStringName)
    {
    }

My web.config has a connection string:

  <connectionStrings>
    <add name="SID_2013Context" connectionString="etc" providerName="System.Data.SqlClient" />
  </connectionStrings>

So I could add a couple of properties to my config class:

public static string ConnectionString
{
    get { return ConfigurationManager.ConnectionStrings[ConnectionStringName]
                                     .ConnectionString; }
}

public static string DatabaseName
{
    get
    {
        var builder = new System.Data.SqlClient
                      .SqlConnectionStringBuilder(ConnectionString);
        return builder.DataSource;
    }
}

Then in my Up() migration:

   public override void Up()
   {
         string databaseName = Config.DatabaseName;
   }

Tutorial: Use EF Migrations in an ASP.NET MVC app and deploy to , Disable the initializer that you set up earlier by commenting out or deleting the At the PM> prompt enter the following commands: (If you missed the step above that directs you to change the database name, Migrations will find the In that case, you want the Seed method to insert into the database only  Copy the Access database file to another location. Open the copied database. On the Tools menu, point to Security, and then select User and Group Permissions. Select the Users option, select the Admin user, and then ensure that the Administer permission is selected. Select the Groups option,

Update an Existing Database using Code First Migrations with ASP , This is where you need to add an extra step for an existing database. migration for the database, type the following command into Package public partial class add_product_description : DbMigration { public override void Up() SQL Server Object Explorer and enter some descriptions, as shown below:. Usage of db-migrate. To use db-migrate, you call it via the command line. When entering only the command without paramaters you will see something like this: Usage: db-migrate [up|down|reset|create|db] [[dbname/]migrationName|all] [options] Down migrations are run in reverse run order, so migrationName is ignored for down migrations.

Migrations, The database needs to be kept in sync with the model. [Command Line]; dotnet ef migrations add <name of migration>; [Package Manager console] The Up method contains C# code that applies any changes made to the model to the  DbUp began life as the database migration tool inside FunnelWeb, an open source ASP.NET MVC blog engine. Despite its humble origins, growing up, DbUp always thought it could aspire to something bigger. Now it is out in the world on its own, making its own way in life, the little script runner that could.

Handling Entity Framework Core database migrations in production , NET Core” in which he used “migrating a database” as an example of Tips, · Watch out for error messages when you run the Add-Migration method. A “free” comparison tool is built into Visual Studio 2017 (all versions) called SQL If you right-click on a database, then you can access the “Compare  When you migrate an Access database, it's similar to a split database in that SQL Server is acting as a new back-end for the data that is now located on a server. As a result, you can still maintain the front-end Access database with linked tables to the SQL Server tables.

Comments
  • The problem is that I need the names to change, depending on what database I deploy to. An AppSetting would set the name for the whole app, regardless of what I deploy it to.
  • Option 1 does not work, as I cannot access the "current running" connection string in the Up-method. That is my problem.
  • It is normal for the ConnectionString and AppSettings to change from environment to environment. You need to look at having different build configurations (msdn.microsoft.com/en-us/library/dd465318.ASPX ) so that they don't apply regardless of what you deploy to.
  • I do have several config transforms and build configs. But I deploy the databases using the Nuget Console, typing Update-Database. And so I have several connectionstrings inside the Code-First project's (a Class Library) App.config. Maybe that is where I go wrong?
  • Its a while since I set it up, and it took a day or so to learn how to do it but now, when I deploy, I change the configuration to "Release", right-click on the solution and select "Publish". That opens a dialog with another "Publish" button. Click that 2nd button and it's done. Database migrations, website, settings the lot. I'm still using VS2010 BTW.I expect newer versions of Visual Studio make it even easier