A Lightweight Library to generate quickly Databases from entities.
Concepts:
- 0 Migration. No "MigrationsHistory" table. Avoids a directory with hundred of migrations.
- Scaffold command to generate/synchronize entities, DbModel and Snapshot with Database
- Generate script or update database command to update the database
Db Supported: SQL Server, SQLite, MySQL|MariaDB, PostgreSQL
... 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:
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";
}
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
var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";
runner.UpdateDatabase();
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'
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
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
