Database Initialization

In RDO.Data, database creation and initialization are extensively supported. You can:

• Generate database with all tables, or subset of tables, to any specified destination, with or without initial or testing data;
• Generate DataSet (C# or VB code) from existing database so that you can use existing external database editing tool such as SQL Server Management Studio to edit initial or testing data, then save these data into DataSet source code.

You application should have a separate console application project to handle database creation and initialization. This project:

• By convention is named with a .DbInit suffix;
• References DevZest.Data.DbInit nuget package;
• Call RunDbInit at entry point;
• Defines classes derived from DbSessionProvider<T>, DbGenerator<T> or DbMock<T>.

RDO.Tools runs this console application project from Visual Studio, just like unit test runner runs your unit test project. For detailed information, please refer to Tutorial and AdventureWorksLT samples.

Database Session Provider

Database session provider is your class derived from DbSessionProvider<T>. It creates your Db object as the destination to create and initialize your database. The following is an example of database session provider:

• C#
• VB.Net

[EmptyDb]
public sealed class _DevDb : DbSessionProvider<Db>
{
    public override Db Create(string projectPath)
    {
        var dbFolder = Path.Combine(projectPath, @"LocalDb");
        string attachDbFilename = Path.Combine(dbFolder, "AdventureWorksLT.Design.mdf");
        File.Copy(Path.Combine(dbFolder, "EmptyDb.mdf"), attachDbFilename, true);
        File.Copy(Path.Combine(dbFolder, "EmptyDb_log.ldf"), Path.Combine(dbFolder, "AdventureWorksLT.Design_log.ldf"), true);
        var connectionString = string.Format(@"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=""{0}"";Integrated Security=True", attachDbFilename);
        return new Db(connectionString);
    }
}

public sealed class DevDb : DbSessionProvider<Db>
{
    public override Db Create(string projectPath)
    {
        var dbFolder = Path.Combine(projectPath, @"LocalDb");
        string attachDbFilename = Path.Combine(dbFolder, "AdventureWorksLT.Design.mdf");
        var connectionString = string.Format(@"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=""{0}"";Integrated Security=True", attachDbFilename);
        return new Db(connectionString);
    }
}

The above example code declares two destinations: _DevDb and DevDb. They both point to LocalDb/AdventureWorksLT.DbInit.mdf database, relative to current project folder. However, _DevDb is decorated with EmptyDbAttribute indicating it's an empty database, whereas DevDb is a existing database with tables created/initialized. The later one is used to generate DataSet from database.

You can also define database session provider for production environment:

[EmptyDb]
public class _PrdDb : DbSessionProvider<Db>
{
    public override Db Create(string projectPath)
    {
        var connectionString = string.Format($"Server=127.0.0.1,4000;Database=AdventureWorksLT;User Id=sa;Password={Password};");
        return new Db(connectionString);
    }

    [Input(IsPassword = true)]
    public string Password { get; set; }
}

Your source code should not contain any sensitive data such as database connection password. You can use InputAttribute to decorate a public settable string property so that the runner can prompt the user to enter the value, or read value from environment variable, when executed.

You can generate database from database session provider, with all tables and without initial data. In Visual Studio, right click your database session provider class source code which decorated with EmptyDb attribute (as _DevDb in previous example), to show the context menu:

• C#
• VB.Net

Click Generate Db... from context menu, the following dialog will be displayed:

Click OK button, the database will be created:

Database Initializer

A database initializer is a class that takes care of database creation and initialization. You can initialize your database with all tables via deriving your database initializer class from DbGenerator<T> class, or you can mock your database with subset of tables via deriving your database initializer class from DbMock<T> class:

• DbGenerator<T> is concrete class, can be used directly with database session providers. The preceding database generation example is using this class implicitly. You can optionally call SetData in InitializeData override.
• DbMock<T> is abstract class, must be derived to override Initialize to call Mock to specify table(s) and/or data explicitly.

You can generate the database from your database initializer class by right clicking the source code in Visual Studio, as described in previous example.

Both SetData and Mock methods accept a delegate to provide initial/testing data. This delegate must be a static method which returns a DataSet:

• C#
• VB.Net

public sealed class MockSalesOrder : DbMock<Db>
{
    private static DataSet<SalesOrderHeader> Headers()
    {
        ...
    }

    private static DataSet<SalesOrderDetail> Details()
    {
        ...
    }

    protected override void Initialize()
    {
        // The order of mocking table does not matter, the dependencies will be sorted out automatically.
        Mock(Db.SalesOrderDetail, Details);
        Mock(Db.SalesOrderHeader, Headers);
    }
}

If your database initializer class contains any static method returns DataSet, right clicking the source code in Visual Studio will display Generate DataSet(s)... context menu:

Click Generate DataSet(s)... context menu item, the following dialog will be displayed:

You can use existing external database editing tool such as SQL Server Management Studio to edit initial or testing data, then select the existing database from DbSessionProvider, after clicking button OK, the static methods will automatically populated with code to initialize the DataSet:

• C#
• VB.Net

private static DataSet<SalesOrderHeader> Headers()
{
    DataSet<SalesOrderHeader> result = DataSet<SalesOrderHeader>.Create().AddRows(4);
    SalesOrderHeader _ = result._;
    _.SuspendIdentity();
    _.SalesOrderID[0] = 1;
    _.SalesOrderID[1] = 2;
    ...
    _.ResumeIdentity();
    return result;
}

private static DataSet<SalesOrderDetail> Details()
{
    DataSet<SalesOrderDetail> result = DataSet<SalesOrderDetail>.Create().AddRows(32);
    SalesOrderDetail _ = result._;
    _.SuspendIdentity();
    _.SalesOrderID[0] = 1;
    _.SalesOrderID[1] = 1;
    ...
    _.ResumeIdentity();
    return result;
}