Access the database name inside Up in DbMigration

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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