CodeFirstDbGenerator.Sqlite 3.0.1.6

CodeFirstDbGenerator

Motivation: allow to create migrations and update database without installing Entity Framework, for libraries like Dapper.

Db Supported:

• Sql Server (CodeFirstDbGenerator.SqlServer)
• Sqlite (CodeFirstDbGenerator.Sqlite)
• MySql (CodeFirstDbGenerator.MySql)
• Or write your own library

Languages supported:

• C#
• VB

Installation

Install packages : CodeFirstDbGenerator (Migration base class)

install-package CodeFirstDbGenerator

SqlServer, Sqlite, etc.

install-package CodeFirstDbGenerator.SqlServer

And CodeFirstDbGenerator.Tools (Visual Studio Package Manager Console)

install-package CodeFirstDbGenerator.Tools

.. or dotnet-cf (dotnet tool)

dotnet tool install --global dotnet-cf

Tip: To uninstall a previous version of the tool and list the tools

dotnet tool uninstall -g dotnet-cf 
dotnet tool list -g

Recommendation : Create a class Library .NET 5 or .NET 6 for Migrations

Create a migration

3 choices:

• Package Manager Console
• dotnet cf tool
• Code

Package Manager Console

Add-Migration InitialCreate

... Or dotnet cf tool

dotnet cf migrations add InitialCreate -p path/to/project

... Or code : create a class that inherits from Migration. Dont forget the migration attribute.

Define the entites to create/update the database. CF will discover columns, primary keys and foreign keys with Data Annotations

[Migration("637727087400479557_InitialCreate")]
public class InitialCreate : Migration
{
    public override MigrationOptions GetOptions()
    {
       return new MigrationOptions
       {
           DropDatabase = true
       };
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable<Company>();
        migrationBuilder.CreateTable<Employee>();
        // a stored procedure
        migrationBuilder.Sql(@"
            CREATE PROC usp_GetCompany
                @CompanyId int
            AS
            BEGIN
                SELECT *
                FROM Companies
                WHERE CompanyId = @CompanyId
            END
            GO
        ");
    }

}

// [Table("tbl_Companies")] allows to define the name of the table
public class Company
{
    // [Key] or auto discovered if property name equals ClassName + Id
    public int CompanyId { get; set; } // key

    [StringLength(100)]
    public string Name { get; set; } //required

    [Required]
    //[Column("MyPostalCode")] allows to rename the column
    public string PostalCode { get; set; } // required with data annotations

    [Column(TypeName ="ntext")] // allows to change the type
    public string? Address { get; set; }

    [MaxLength(50)] // or [StringLength(50)]
    public string? City { get; set; } // not required

    [Timestamp]
    public byte[] RowVersion { get; set; }

    public List<Employee> Employees { get; set; } = new(); // ignored
}

public class Employee
{
    [Key] // or not identity [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int EmployeeId { get; set; } // recommendation: make Key unique, dont use names like "Id" for all primary keys

    public string FirstName { get; set; }

    public string LastName { get; set; }

    // [ForeignKey("Companies")] use foreign key attribute if column name does not match with principal column name
    public int CompanyId { get; set; }

    public Company Company { get; set; } // ignored

    [NotMapped] // ignored
    public string FullName
    {
        get { return $"{FirstName} {LastName}"; }
    }
}

DataAnnotations Attributes:

• Key: for primary key (identity if int, short or long)
• Table: to define table name
• Column: to define column name and type name
• DatabaseGenerated + identity option: for a column identity
• StringLength ou MaxLength: to define string length (exeample "navarchar(100)")
• ForeignKey: to specify the principal table name

Its possible to define columns (default, value, unique, column type, etc.), multipe primary keys or foreign keys with fluent api. Example:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable<Company>();
    migrationBuilder
        .CreateTable<Employee>()
        .Column(x => x.FirstName, defaultValue: "X")
        .ForeignKey(column: x => x.CompanyId,
                principalTable: "Companies",
                principalColumn: "CompanyId",
                onUpdate: ReferentialAction.Cascade,
                onDelete: ReferentialAction.Cascade);

    migrationBuilder.CreateTable<Student>().PrimaryKey(columns: x => new { x.StudentKey, x.AdmissionNum });
}

Migrations History

By default a table "__CFMigrationsHistory" is created.

To use JsonMigrationsHistory:

var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";

var path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "CodeFirstDbGenerator\\SampleDb__MigrationsHistory.json");
runner.History = new JsonMigrationsHistory(path);
runner.UpdateDatabase();

Its possible to create a custom MigrationsHistory. Just implement IMigrationsHistory and change the history.

Define/ Use a runner

3 choices:

• RunnerOptions
• Service Collection
• Code

RunnerOptions

public class SqlServerOptions : RunnerOptions<SqlServerRunner>
{
    public override bool? Verbose => true;

    public override string GetConnectionString()
        => "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;";
}

// Sqlite
public class SqliteOptions: RunnerOptions<SqliteRunner>
{
    public override bool? Verbose => true;

    public override string GetConnectionString()
        => @"Data Source=C:\db\SampleDb.db;Cache=Shared"; // dont use relative path
}

// MySql
public class MySqlOptions : RunnerOptions<MySqlRunner>
{
    public override bool? Verbose => true;

    public override string GetConnectionString()
        => "server=localhost;database=SampleDb;uid=root";
}

... Or Service collection

services.AddRunner<SqlServerRunner>().WithConnection(() => Configuration.GetConnectionString("DefaultConnection"));

If you want to inject dependencies in migrations or runner options ctor. You have to register the services with the Service collection (or the container used)

Tip : auto register migrations with Microsoft.Extensions.DependencyInjection

services.AutoRegisterMigrations(typeof(InitialCreate).Assembly);

.. Or code

var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";
runner.UpdateDatabase();

Its possible to set the service provider. Example

runner.ServiceProvider = app.ApplicationServices;

Update database

3 choices :

• Package Manager Console (require CodeFirstDbGenerator.Tools)
• dotnet cf (require dotnet-cf tool)
• Code

With Package Manager Console and dotnet cf... CodeFirstDbGenerator search RunnerOptions or Runners registered in Service provider.

Package Manager Console

Update-Database

If you are not sure, you can provide the project name and the startup project name (for dependency injection). The Tab key provides intelliSense.

Update-Database -Project MyProject -StartupProject MyStartupProject

Its possible to provide a connection string

Update-Database -ConnectionString "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;"

Note: Sometimes its better to provide explicitly the startup project (when the current startup project is not the project expected or when the solution has multiple projects launched).

... Or dotnet cf tool

dotnet cf database update -c "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true" -a path/to/assembly.dll

With a library that contains migrations and a startup assembly (Application Web Asp.Net Core) for example :

dotnet cf database update -c "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true" -a path/to/assembly.dll -s path/to/startup-assembly.dll

Tip: create a bash file to execute multiple commands. Example test.sh and use GIT Bash 'sh test.sh'

echo '> Sample1'
dotnet cf database update -c "Server=(localdb)\mssqllocaldb;Database=Sample1Db;Trusted_Connection=True;MultipleActiveResultSets=true" -a "C:\Samples\Sample1\bin\Debug\net5.0\Sample1.dll"
echo '> Sample2'
dotnet cf database update -c "Server=(localdb)\mssqllocaldb;Database=Sample2Db;Trusted_Connection=True;MultipleActiveResultSets=true" -a "C:\Samples\Sample2\bin\Debug\net6.0\Sample2.dll"

... Or Code

var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;";

// with entry assembly
runner.UpdateDatabase();

// or with an assembly
runner.UpdateDatabase(typeof(MyMigration).Assembly);

Tip configure the host builder factory for a wpf app for example

Install packages

<ItemGroup>
	<PackageReference Include="Microsoft.Extensions.Hosting" Version="6.0.0" />
</ItemGroup>

Add a CreateHostBuilder function to the entry point of the application

using CodeFirstDbGenerator;
using CodeFirstDbGenerator.SqlServer;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Sample.Data;
using System;
using System.Windows;

namespace WpfDi
{
    public partial class App : Application
    {
        private IHost host;

        public App()
        {
            host = CreateHostBuilder().Build();
        }

        private void Application_Startup(object sender, StartupEventArgs e)
        {
            host.Start();

            var shell = host.Services.GetRequiredService<MainWindow>();
            shell.Show();
        }

        public static IHostBuilder CreateHostBuilder() =>
            Host.CreateDefaultBuilder()
            .ConfigureServices((context, services) =>
            {
                services.AddScoped<IMyService,MyService>();
                services.AutoRegisterMigrations(typeof(Sample_Migration).Assembly);
                services.AddScoped<MainWindow>();
                services.AddRunner<SqlServerRunner>().WithConnection(() => "Server=(localdb)\\mssqllocaldb;Database=TestWpfDi;Trusted_Connection=True;");
            });
    }
}

Sample migration with a dependency

[Migration("SampleMigration")]
public class SampleMigration : Migration
{
    public A_Migration(IMyService myService)
    {
        MyService = myService;
    }

    public IMyService MyService { get; }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
           
    }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable<Company>();       
    }
}

Create a library for a Database

For example MySql. Create a library

• Create a runner (MySqlRunner for example) that inherits from Runner
  • Create a TypeTranslator (MySqlTypeTranslator for example). The service receives a clr type (string, int, short, etc.) and returns the sql type (varchar(255), interger, tinyint, etc.) with length
  • Create a TableHistory (MySqlTableHistory for example) that inherits from TableHistory
  • Create a SqlQueryProvider (MySqlQueryProvider for example) that inherits from SqlQueryProvider base class
  • Create a DatabaseCreator (MySqlDatabaseCreator for example) that inherits from DatabaseCreator base class