Fluent Migrator

0

Category :

Fluent Migrator is a migration framework for .NET much like Ruby on Rails Migrations. Migrations are a structured way to alter your database schema and are an alternative to creating lots of sql scripts that have to be run manually by every developer involved. Migrations solve the problem of evolving a database schema for multiple databases (for example, the developer’s local database, the test database and the production database). Database schema changes are described in classes written in C# that can be checked into a version control system.
Gettings Started

Getting Started

Create a new Visual Studio library project for your migrations.

Migration

[Migration(1)]
public class CreateUserTable : Migration
{
    public override void Up()
    {
        Create.Table("Users")
                             .WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity()
                             .WithColumn("Name").AsString(255).NotNullable().WithDefaultValue("Anonymous");
    }

    public override void Down()
    {
        Delete.Table("Users");
    }
}

Execute Migration

Find Migrate.exe file and run command from same directory
migrate.exe /conn "data source=(local);Trusted_Connection=yes;database=FluentMigrator_TestDB" --provider sqlserver2008 --assembly "../../../Guides.FluentMigrator/bin/Debug
/Guides.FluentMigrator.dll" --task migrate  --output -- outputFilename migrated.sql
I installed FluentMigrator using Nuget so i had to navigate to the "packages\FluentMigrator.x.x.x\tools" directory and run the above command from there.
I think this exe can also be found in the "Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\NuGet Packages\EntityFramework.x.x.x\tools" directory.

Bat file to Execute Migration

Migrate Up
cd C:\sandbox\Guides.FluentMigrator\packages\FluentMigrator.1.6.2\tools

migrate.exe /conn "data source=(local);Trusted_Connection=yes;database=FluentMigrator_TestDB" --provider sqlserver2008 --assembly "../../../Guides.FluentMigrator/bin/Debug/Guides.FluentMigrator.dll" --task migrate:up  --output -- outputFilename migrated.sql

pause

Upgrade Database using SQL Scripts

create a separate folder in your project, name it like “Scripts” and put your SQL Script there.
Then, create another migration class, name it “M0002_CreateSP_GetAllMember.cs” and paste the following code in that class file:
using FluentMigrator;
namespace DatabaseMigration.Migrations
{
    [Migration(2)]
    public class M0002_CreateSP_GetAllMember : Migration
    {
        public override void Up()
        {
            Execute.EmbeddedScript("CreateSP_GetAllMember.sql");
        }

        public override void Down()
        {
            Execute.EmbeddedScript("DropSP_GetAllMember.sql");
        }
    }
}
NOTE: Don’t forget to set the Build Action property of both SQL files as Embedded Resource.


my thanks to:
https://github.com/schambers/fluentmigrator/wiki
http://www.codeproject.com/Articles/1012802/Fluent-Migrator-for-Database-Migration