Movies.DbInit Project

In this section, we will work on Movies.DbInit project to create and initialize database.

Project Setup

Make the following changes to Movies.DbInit project:

Step 1. Add project reference Movies to this project.

Step 2. Add NuGet Package DevZest.Data.DbInit to this project.

Step 3. Change default namespace (root namespace in Visual Basic) from Movies.DbInit to Movies, by right clicking the project Movies.DbInit in Solution Explorer tool window, then click Properties in context menu:

Step 4. Change Program.cs (Program.vb in Visual Basic) to following:

using DevZest.Data.DbInit;

namespace Movies
{
    class Program
    {
        static int Main(string[] args)
        {
            return args.RunDbInit();
        }
    }
}

Create LocalDB

Step 1. Create a LocalDb subfolder by right clicking Movies.DbInit project in Solution Explorer tool window, then click "Add" -> "New Folder" in the context menu.

Step 2. Open SQL Server Management Studio, use Server name (localdb)\MSSQLLocalDB, and select Windows Authentication:

image

Step 3. Create a new query in SQL Server Management Studio by pressing CTRL-N, then execute the following query:

CREATE DATABASE EmptyDb ON (
  NAME='EmptyDb', 
  FILENAME='%path_to_your_solution%\Movies.DbInit\LocalDb\EmptyDb.mdf')

You should have 2 files EmptyDb.mdf and EmptyDb_log.ldf created under the folder created in Step 1.

Step 4. Add class _DevDb to project Movies.DbInit:

using DevZest.Data.DbInit;
using System.IO;

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

Step 5. Right click in code editor of class _DevDb, click "Generate Db..." context menu item:

image

Click button OK, a database named Movies containing a table Movie will be generated. You can verify it under folder created in Step1 and SQL Server Management Studio.

Note

Database files Movie.mdf and Movie_log.ldf should be ignored from source control.

Add Testing Data

Step 1. In SQL Server Management Studio, right click table dbo.Movie of the database Movies created previously, then click Edit Top 200 Rows context menu item to add following testing data:

image

Step 2. Add class DevDb (without leading underscore) to project Movies.DbInit:

using DevZest.Data.DbInit;
using System.IO;

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

Step 3. Add class MockMovie to project Movies.DbInit:

using DevZest.Data;

namespace Movies
{
    public sealed class MockMovie : DbMock<Db>
    {
        private static DataSet<Movie> GetMovies()
        {
            return DataSet<Movie>.Create();
        }

        protected override void Initialize()
        {
            Mock(Db.Movie, GetMovies);
        }
    }
}

There is a compile-time warning: DbMock implementation should expose a static factory method to wrap a call to DbMock.MockAsync method. Fix this warning by moving the caret to the class name MockMovie, then pressing CTRL-. in Visual Studio. Select context menu item Add factory method, the following code will be inserted automatically:

public static Task<Db> CreateAsync(Db db, IProgress<DbInitProgress> progress = null, CancellationToken ct = default(CancellationToken))
{
    return new MockMovie().MockAsync(db, progress, ct);
}

Step 4. Right click in code editor of class MockMovie, click "Generate DataSet(s)..." context menu item:

image

Click button OK, the code for data set will be generated automatically:

using DevZest.Data;
using DevZest.Data.DbInit;
using DevZest.Data.Primitives;
using System;
using System.Threading;
using System.Threading.Tasks;

namespace Movies
{
    public sealed class MockMovie : DbMock<Db>
    {
        public static Task<Db> CreateAsync(Db db, IProgress<DbInitProgress> progress = null, CancellationToken ct = default(CancellationToken))
        {
            return new MockMovie().MockAsync(db, progress, ct);
        }

        private static DataSet<Movie> GetMovies()
        {
            DataSet<Movie> result = DataSet<Movie>.Create().AddRows(4);
            Movie _ = result._;
            _.SuspendIdentity();
            _.ID[0] = 1;
            _.ID[1] = 2;
            _.ID[2] = 3;
            _.ID[3] = 4;
            _.Title[0] = "When Harry Met Sally";
            _.Title[1] = "Ghostbusters";
            _.Title[2] = "Ghostbusters 2";
            _.Title[3] = "Rio Bravo";
            _.ReleaseDate[0] = Convert.ToDateTime("1989-02-12T00:00:00");
            _.ReleaseDate[1] = Convert.ToDateTime("1984-03-13T00:00:00");
            _.ReleaseDate[2] = Convert.ToDateTime("1986-02-23T00:00:00");
            _.ReleaseDate[3] = Convert.ToDateTime("1959-04-15T00:00:00");
            _.Genre[0] = "Romantic Comedy";
            _.Genre[1] = "Comedy";
            _.Genre[2] = "Comedy";
            _.Genre[3] = "Western";
            _.Price[0] = 7.9900M;
            _.Price[1] = 8.9900M;
            _.Price[2] = 9.9900M;
            _.Price[3] = 3.9900M;
            _.ResumeIdentity();
            return result;
        }

        protected override void Initialize()
        {
            Mock(Db.Movie, GetMovies);
        }
    }
}