Dot Net Core Migrations for Oracle Database

Suppose,
the name of your project is ERP
the name of your connection is Default

Add a new class file at location ERP.Web/Migrations/DefaultDB, you will get the following code,

using System;                                                           

using System.Collections.Generic;                          

using System.Linq;                                                  

using System.Threading.Tasks;                               

                                                                                 

namespace ERP.Web.Migrations.DefaultDB          

{                                                                               

    public class Class1                                              

    {                                                                           

    }                                                                           

}                                                                               

Edit this code as following

using FluentMigrator;
using System;

namespace ERP.Migrations.DefaultDB
{
    [Migration(1)]
    public class Class1 : Migration
    {
        public override void Up()
        {
        }
        public override void Down()
        {
        }
    }
}

In the above code, [Migration(1)] is the Migration attribute with a unique identifier. 

This identifier is just a value of type int64. Generally using a numbered date format such as yyyyMMdd is a good practice. This number is also for the ordering of migrations. 


note: For Serentiy Migration versions must be in yyyyMMddHHmmss format e.g. [Migration(20200411033300)]

Create Table

using FluentMigrator;

using System;

namespace ERP.Migrations.DefaultDB

{

    [Migration(20200411033300)]

    public class Class1 : Migration

    {

        public override void Up() 

        {

                this.CreateTableWithId32("Movie", "MovieId", s => s

                .WithColumn("Title").AsString(200).NotNullable()

                .WithColumn("Description").AsString(1000).Nullable()

                .WithColumn("Storyline").AsString(Int32.MaxValue).Nullable()

                .WithColumn("Year").AsInt32().Nullable()

                .WithColumn("ReleaseDate").AsDateTime().Nullable());             

        }

        public override void Down()

        {

        }

    }

}

We are creating a table Movie in the above code. MovieId will be its primary key. This will also create a sequence and trigger to auto add the MovieId key.

Note that, while using oracle database, if you have generated Code with SERGEN, you may need to replace stream type with respective types in classRow.cs, classForm.cs and classColumn.cs files.

Add or Delete Table Column

 Alter.Table("Movie")

                .AddColumn("PrimaryImage").AsString(100).Nullable()

                .AddColumn("GalleryImages").AsString(int.MaxValue).Nullable();

Using above code, we can add column to table. To delete a column use the following code.

Delete.Column("PrimaryImage")

                .FromTable("Movie");

Add or Delete Foreign Key

Suppose, we have schema named "movieSchema" and it has two tables Movie and Genre. GenreId is the primary key of Genre table. Movie table also has a column GenreId and it acts as a foreign key. We can add column and define its foreign relationship using following code,

Alter.Table("Movie")

                .AddColumn("GenreId")..AsInt32().NotNullable()

                    .ForeignKey("FK_MovieGenres_GenreId", "movieSchema", "Genre", "GenreId"));

            

We can delete a foreign key as shown in below code,

Delete.ForeignKey("FK_Movie_GenreId")

                .OnTable("Movie");

Directly Execute SQL Query

To execute SQL query directly, use the Execute.Sql function. 

Execute.Sql(

              @"INSERT INTO movieSchema.MovieGenres (MovieId, GenreId) 

                    SELECT m.MovieId, m.GenreId 

                    FROM mov.Movie m 

                    WHERE m.GenreId IS NOT NULL");

Insert Data in Table

We can insert two records in Movie table we created earlier in this post using the following code.

  Insert.IntoTable("Movie").InSchema("movieSchema")

            .Row(new

            {

                Title = "The Matrix",

                Description = "A computer hacker...  its controllers.",

                Storyline = "Thomas A. Anderson ... human rebellion.",

                Year = 1999,

                ReleaseDate = new DateTime(1999, 03, 31),

                Runtime = 136

            })

            .Row(new

            {

                Title = "Fight Club",

                Description = "An insomniac ... much more...",

                Storyline = "A ticking-time-bomb ... oblivion.",

                Year = 1999,

                ReleaseDate = new DateTime(1999, 10, 15),

                Runtime = 139

            });