Data Access and Movies.Test Project

In this section, we will add data access code to Movies project and add tests to Movies.Test project.

Add Data Access Code

Add class file Db.Api.cs (or Db.Api.vb if using VB.Net) to project Movies:

C#
VB.Net

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

namespace Movies
{
    partial class Db
    {
        public Task<DataSet<Movie>> GetMoviesAsync(string text, CancellationToken ct = default(CancellationToken))
        {
            DbSet<Movie> result = Movie;

            if (!string.IsNullOrWhiteSpace(text))
                result = Filter(result, text);

            return result.ToDataSetAsync(ct);
        }

        private static DbSet<Movie> Filter(DbSet<Movie> movies, _String text)
        {
            return movies.Where(_ => _.Title.Contains(text) | _.Genre.Contains(text));
        }

        public Task<DataSet<Movie>> GetMovieAsync(_Int32 id, CancellationToken ct = default(CancellationToken))
        {
            return Movie.Where(_ => _.ID == id).ToDataSetAsync(ct);
        }
    }
}

Imports System.Threading
Imports DevZest.Data

Partial Class Db
    Public Function GetMoviesAsync(text As String, Optional ct As CancellationToken = Nothing) As Task(Of DataSet(Of Movie))
        Dim result As DbSet(Of Movie) = Movie

        If Not String.IsNullOrWhiteSpace(text) Then
            result = Filter(result, text)
        End If

        Return result.ToDataSetAsync(ct)
    End Function

    Private Shared Function Filter(movies As DbSet(Of Movie), text As _String) As DbSet(Of Movie)
        Return movies.Where(Function(x) x.Title.Contains(text) Or x.Genre.Contains(text))
    End Function

    Public Function GetMovieAsync(id As _Int32, Optional ct As CancellationToken = Nothing) As Task(Of DataSet(Of Movie))
        Return Movie.Where(Function(x) x.ID = id).ToDataSetAsync(ct)
    End Function

End Class

Movies.Test Project Setup

Make the following changes to Movies.Test project:

Step 1. Delete UnitTest1.cs (or UnitTest1.vb) from project.

Step 2. Add project reference Movies.DbDesign to this project.

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

C#
VB.Net

Step 4. Add EmptyDb.mdf and EmptyDb_log.ldf to this project as linking, by right clicking Movies.Test project in Solution Explorer tool window, then click context menu item "Add" -> "Existing Item...", select All Files(.) from right bottom combobox, then navigate to LocalDb subfolder of Movies.DbDesign project, then select file EmptyDb.mdf and EmptyDb_log.ldf:

Click the little dropdown arrow next to the Add button and select Add as Link, instead of copying the file into the project directory, Visual Studio will create a link to the original:

Select these two files, then right click and select context menu item Properties, make sure Copy to Output Directory is set to Copy Always:

Add Tests

Add class DbTests.cs (or DbTests.vb if you're using VB.Net) to project Movies.Test:

C#
VB.Net

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.IO;
using System.Reflection;
using System.Threading.Tasks;

namespace Movies
{
    [TestClass]
    public class DbTests
    {
        private static string GetConnectionString()
        {
            string mdfFilename = "EmptyDb.mdf";
            string outputFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string attachDbFilename = Path.Combine(outputFolder, mdfFilename);
            return string.Format(@"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=""{0}"";Integrated Security=True", attachDbFilename);
        }

        private static Db CreateDb()
        {
            return new Db(GetConnectionString());
        }

        [TestMethod]
        public async Task Db_GetMovies()
        {
            using (var db = await MockMovie.CreateAsync(CreateDb()))
            {
                var result = await db.GetMoviesAsync("comedy");
                Assert.AreEqual(3, result.Count);
            }

            using (var db = await MockMovie.CreateAsync(CreateDb()))
            {
                var result = await db.GetMoviesAsync("ghost");
                Assert.AreEqual(2, result.Count);
            }
        }

        [TestMethod]
        public async Task Db_GetMovie()
        {
            using (var db = await MockMovie.CreateAsync(CreateDb()))
            {
                var result = await db.GetMovieAsync(1);
                Assert.AreEqual(1, result.Count);
            }
        }
    }
}

Imports System.IO
Imports System.Reflection
Imports Microsoft.VisualStudio.TestTools.UnitTesting

<TestClass>
Public Class DbTests
    Private Shared Function GetConnectionString() As String
        Dim mdfFilename As String = "EmptyDb.mdf"
        Dim outputFolder As String = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
        Dim attachDbFilename As String = Path.Combine(outputFolder, mdfFilename)
        Return String.Format("Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=""{0}"";Integrated Security=True", attachDbFilename)
    End Function

    Private Shared Function CreateDb() As Db
        Return New Db(GetConnectionString())
    End Function

    <TestMethod>
    Public Async Function Db_GetMovies() As Task
        Using db = Await MockMovie.CreateAsync(CreateDb())
            Dim result As Object = Await db.GetMoviesAsync("comedy")
            Assert.AreEqual(3, result.Count)
        End Using

        Using db = Await MockMovie.CreateAsync(CreateDb())
            Dim result As Object = Await db.GetMoviesAsync("ghost")
            Assert.AreEqual(2, result.Count)
        End Using
    End Function

    <TestMethod>
    Public Async Function Db_GetMovie() As Task
        Using db = Await MockMovie.CreateAsync(CreateDb())
            Dim result As Object = Await db.GetMovieAsync(1)
            Assert.AreEqual(1, result.Count)
        End Using
    End Function
End Class

Build and run tests in Visual Studio Test Explorer, you should have two tests passed.