Using DataContext, Creation of Initial Data and Generate Database Migration Script (Chapter 4)

After created data table, we needed to define the table as DataSet in the DataContext.

Adding “DbSet” to “ApiDataContext”

To add the table to DataContext, go folder and open “\DataProviders\ApiDataContext.cs”

namespace CCOMS.Api.DataProviders
{
    public abstract class ApiDataContext : DbContext
    {
        protected readonly IConfiguration Configuration;

        public ApiDataContext(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public DbSet<DeliveryMethodType> DeliveryMethodTypes { get; set; }
        public DbSet<ParcelItemType> ParcelItemTypes { get; set; }
        public DbSet<RestrictedItem> RestrictedItems { get; set; }
        public DbSet<RestrictedItemGroup> RestrictedItemGroups { get; set; }
        public DbSet<ShippingGoodsType> ShippingGoodsTypes { get; set; }
        public DbSet<ShippingServiceType> ShippingServiceTypes { get; set; }
        public DbSet<ShippingVendor> ShippingVendors { get; set; }
        public DbSet<ShippingFee> ShippingFees { get; set; }
        public DbSet<ConsolidatedOrderMaster> ConsolidatedOrderMasters { get; set; }
        public DbSet<ClientParcel> ClientParcels { get; set; }
        public DbSet<TrackingEventType> TrackingEventTypes { get; set; }
        public DbSet<TrackingMaster> TrackingMasters { get; set; }
        public DbSet<TrackingTranscoding> TrackingTranscodings { get; set; }
        public DbSet<Warehouse> Warehouses { get; set; }
        public DbSet<WarehouseArea> WarehouseAreas { get; set; }
        public DbSet<WarehouseAreaType> WarehouseAreaTypes { get; set; }
        public DbSet<WarehouseCell> WarehouseCells { get; set; }
        public DbSet<WarehouseType> WarehouseTypes { get; set; }
    }
}

Adding Special Primary Key Definition to “MsSqlApiDbContext”

All data tables should define an entry here as of above examples. Remember to configure the table with 2 or 3 field primary key tables if you have. Go to folder and open “\DataProviders\MsSqlApiDbContext.cs”

namespace CCOMS.Api.DataProviders
{    
    // To Add Migration : dotnet ef migrations add InitalCreate --startup-project CCOMS.Api --project CCOMS.Api --context MsSqlApiDbContext --output-dir DataProviders\Migrations\MsSql

    public class MsSqlApiDbContext : ApiDataContext
    {
        public MsSqlApiDbContext(IConfiguration configuration) : base(configuration)
        {

        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            // connect to sql server database
            options.UseSqlServer(Configuration.GetConnectionString("MsSqlConnection"));
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("dbo");

            modelBuilder.Entity<WarehouseArea>().HasKey(u => new
            { 
                u.WarehouseCode,
                u.AreaCode
            });

            modelBuilder.Entity<WarehouseCell>().HasKey(u => new
            {
                u.WarehouseCode,
                u.AreaCode,
                u.CellCode
            });
        }
    }
}

Creating Initial Data to new Data Table

For some tables, we may want to create some initial data to the newly created database. To add some initial data, go to folder and open “\DataProviders\ApiDataInitializer.cs”

namespace CCOMS.Api.DataProviders
{
    public class ApiDataInitializer
    {
        public static void Initialize(ApiDataContext _context)
        {
            if (!_context.WarehouseAreaTypes.Any())
            {
                var types = new WarehouseAreaType[]
                {
                    new WarehouseAreaType{AreaTypeCode="SS", AreaTypeName="Storage Space", DefBorderColor=0, DefFillColor=16776960},
                    new WarehouseAreaType{AreaTypeCode="TS", AreaTypeName="Temporary Storage", DefBorderColor=0, DefFillColor=65280}                    
                };

                _context.AddRange(types);
                _context.SaveChanges();
            }

            if (!_context.WarehouseTypes.Any())
            {
                var warehouseTypes = new WarehouseType[]                
                {
                    new WarehouseType{ WareHouseTypeCode="ST", WareHouseTypeName="Storage", DisplayOrder=1 },
                    new WarehouseType{ WareHouseTypeCode="RT", WareHouseTypeName="Retail", DisplayOrder=2 },
                    new WarehouseType{ WareHouseTypeCode="TT", WareHouseTypeName="Transit", DisplayOrder=3 },
                    new WarehouseType{ WareHouseTypeCode="PD", WareHouseTypeName="Product", DisplayOrder=4 },
                    new WarehouseType{ WareHouseTypeCode="MA", WareHouseTypeName="Material", DisplayOrder=5 },
                    new WarehouseType{ WareHouseTypeCode="RF", WareHouseTypeName="Refrigerated", DisplayOrder=6 },
                    new WarehouseType{ WareHouseTypeCode="TC", WareHouseTypeName="Temperautre Controlled", DisplayOrder=7 },
                    new WarehouseType{ WareHouseTypeCode="CS", WareHouseTypeName="Consolidation", DisplayOrder=8 },
                    new WarehouseType{ WareHouseTypeCode="OD", WareHouseTypeName="OutDoor", DisplayOrder=9 },
                    new WarehouseType{ WareHouseTypeCode="TS", WareHouseTypeName="Temporary Storage", DisplayOrder=10 }
                };

                _context.AddRange(warehouseTypes);
                _context.SaveChanges();
            }

            if (!_context.Warehouses.Any())
            {
                var warehouses = new Warehouse[]
                {
                    new Warehouse{ WarehouseCode="WH01", WarehouseName="Default Warehouse", WarehouseTypeCode = "ST", Active = true, CreateTimeUtc = DateTime.UtcNow, UpdateTimeUtc = DateTime.UtcNow, UpdateMan = "DBInitial", UpdateZone = "APIDBCONTEXT"}
                };

                _context.AddRange(warehouses);
                _context.SaveChanges();
            }

            if (!_context.WarehouseAreas.Any())
            {
                var areas = new WarehouseArea[]
                {
                    new WarehouseArea{ WarehouseCode="WH01", AreaCode="A", AreaTypeCode="SS", AreaLeft=1, AreaTop=1, AreaHeight = 100, AreaWidth = 100, AreaBorderColor = 0, AreaFillColor = 16776960, MaxCBM = 0, MaxLength = 0, MaxWeight = 0, MaxHeight = 0, MaxWidth = 0 }
                };

                _context.AddRange(areas);
                _context.SaveChanges();
            }
        }
    }
}

Creating Initial Data to new Data Table

Microsoft Entity Framework has the feature to auto database migration, which can create new database table or modify the changing of database structure. We can use this feature to create or update the tables automatically to ensure the table structure are 100% same as the definition. There are advantages when deployment to client system or update management of client system.

If you want to use Entity Framework in Visual Studio 2019, open the terminal console of the project :

Right-Click the project and select “Open in Terminal”, then you will see Developer Powershell window will open at the bottom of the IDE.

Setting up Database Connection

Open the “appsettings.json” files, and modify “ConnectionStrings : MsSqlConnection “

Using Entity Framework to create database creation and migration script

Make sure you are at location of the solution root folder, may be you just key in command “cd ..” to go to the root folder.

If your Visual Studio do not have the Entity Framework Tools or if it is first time installation, you have to install the tools first

dotnet tool install --global dotnet-ef

When the installation of the ef tools completed, install the latest “Microsoft.EntityFrameworkCore.Design” package

dotnet add package Microsoft.EntityFrameworkCore.Design

After installed all the tools, you can enjoy to use the Entity Framework auto database migration feature.

NOTE: Remember to ensure the database table definition is correct, or you can create table ONE by ONE to avoid incorrect table definition before creating the script

To Generate the database creation script:

dotnet ef migrations add "NameOfAction" --startup-project CCOMS.Api --project CCOMS.Api --context MsSqlApiDbContext --output-dir DataProviders\Migrations\MsSql

Noted: “NameOfAction” is the name you give the system for migration identifier.

If you found any problem of this script, you can rollback to remove this migration:

donet ef migrations remove --startup-project CCOMS.Api --project CCOMS.Api --context MsSqlApiDbContext

Run the project and see if the SQL table create correctly.

After you run the project, if you can see the Swagger API Document page, it means that your API started successfully. Go and create MSSQL Management Studio to check if the database tables has been created.

Yeah!!! All the tables created with initial data.

If you found some problems of table definition and cannot be rollback, try to fix it manually.

Inside the database, you will found at table {username}._EFMigrationsHistory. When the framework migrated the database, it will add a record to this table. If you want to rollback to previous status, simply delete the latest record of this table. And you can manually modify the table back to original definition. You can also delete the EF generated script manually and regenerate new script manually also. Delete the file as below: