RoundhouseRefreshDatabaseEF

##RoundhousE RefreshDatabase EntityFramework Migrations == Rebuild Your Database without leaving Visual Studio!

Sample and source located: https://github.com/chucknorris/roundhouse-entityframework

Introduction

If you use Entity Framework Migrations, you can use it to generate your sql scripts as well. There is a NuGet package RoundhousE.RefreshDatabase.EF that brings some code to a console application (x86) to allow you to generate your SQL files and apply it to your local database immediately!

## Notes You may not have the same setup on everyone's local machine for database instances. In cases like this, you can use SQL Configuration Manager set the same client alias for everyone to use. Then you use the alias in the server parameter. Right now it is `(local)` so you can set that as a client alias (if you don't already have that as an instance). See [setup an alias](http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/) and [alternate](http://geekswithblogs.net/twickers/archive/2009/12/08/136830.aspx).

Set up

1. Create a console application (C# project) named RefreshDatabase. Make sure it is not set to Client Profile. It will not build.
2. Make it x86.
3. Use NuGet to install roundhouse.refreshdatabase.ef.
4. Set the start up program to RefreshDatabase.Main().
5. Add a project reference to your application's DLL that has the Code Migrations.
6. Open RefreshDatabaseParameters.cs and make adjustements:

/// <summary>
/// Should we drop the database prior to running? You want this to continue making changes to the same script
/// </summary>
public static bool DropDatabaseFirst = true;

/// <summary>
/// Should we restore the database from a backup prior to running?
/// You want to restore if you have a production backup that is small enough. 
/// Otherwise you get into a bit more advanced scenario that this package doesn't cover well
/// </summary>
public static bool RestoreDatabase = false;

/// <summary>
/// This is the path to your scripts folder where Up/Views/Functions/Sprocs are the next folder below. This is a relative path from bin\Debug. The three sets of parent folders already here should get it out of your project folder so you can traverse into the database project folder. 
/// </summary>
public static string PathToSqlScripts = @"..\..\..\__NAME__.Database\__NAME__";

/// <summary>
/// The path to your source control repository. Used only for information sake.
/// </summary>
public static string RepositoryPath = "https://github.com/__NAME__/";

/// <summary>
/// This is the path to the restore file, likely on the network so everyone can get to it
/// </summary>
public static string PathToRestore = @"\\nowhere\to\befound.bak";

/// <summary>
/// The is the custom options for the restore, like moving logical files to the correct location
/// </summary>
public static string RestoreCustomOptions = @"MOVE '__NAME__' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\{{DatabaseName}}.mdf', MOVE '__NAME___log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\{{DatabaseName}}_log.ldf'";

/// <summary>
/// Add a reference to the migrations assembly. After adding a reference, the file will be in the build directory, so you can just add the name of the dll here.
/// </summary>
public static string MigrationsAssemblyPath = @".\__NAME__.dll";

/// <summary>
/// The database information
/// </summary>
public static class Database
{
    /// <summary>
    /// Name of your database - hopefully on your local default instance
    /// </summary>
    public static string Name = "__NAME__";

    /// <summary>
    /// This is the server, it is highly recommended that this is either . or .\SQLExpress
    /// </summary>
    public static string Server = @".";

    /// <summary>
    /// The user name for the connection string - leave blank for SSPI=true
    /// </summary>
    public static string UserName = "";

    /// <summary>
    /// The user password for the connection string. If the UserName is blank this will not be used.
    /// </summary>
    public static string UserPassword = "";

    /// <summary>
    /// Gets the connection string.
    /// </summary>
    /// <returns>The connection string</returns>
    public static string GetConnectionString()
    {
        return string.Format("Data Source={0};Initial Catalog={1};{2}", 
            Server, 
            Name, 
            UserName == string.Empty ? "Integrated Security=SSPI;" : string.Format("User Id={0};Password={1}", UserName, UserPassword));
    }
}  

Running it

1. Whenever you have added a new migration to your project or have updated any database sql files, you can just run the project you created for instant local feedback.
2. Right click on the project and select Debug -> Start new instance.
3. Watch it run. If any errors occur, you will be able to see them in the console or in the database it was working with under the ScriptsRunErrors table.
4. If the errors occur with the schema generation, hopefully that hangs in the console long enough for you to decipher what is wrong.

Notes

1. There is a video and blog post showing this in action. Please see Refresh Database - Speed up your development cycles