Click here to Skip to main content
14,330,512 members

Data Access Testing Using RDO.Data

Rate this:
0.00 (No votes)
Please Sign up or sign in to vote.
0.00 (No votes)
8 Oct 2019CPOL
Easily writing tests for data access code using RDO.Data


When the code that you are testing has to touch a database, the amount of setup that you have to do gets quite a bit more complicated. This article explains how to easily write tests for data access code using RDO.Data.

Note: RDO.Data is part of RDO.Net, an open source framework under MIT license to handle data in .NET Framework.

Using the Code

The attached source code contains a sample application to display and manage a database of movies, using RDO.Net. You can follow the step-by-step tutorial in the documentation to create this sample.

In this article, we will focus on how to write tests for data access code.

Data Access Code

The following is the data access code which will be tested:

  • Db.GetMovieAsync: to get a single movie by id
  • Db.GetMoviesAsync: to get a list of movies with an optional filtering text
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);

Database Mocking

The following code mocks a database with some testing data. Calling MockMovie.CreateAsync will return an isolated Db object, in a known state with 4 records in the Movie table:

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._;
        _.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;
        return result;

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

This is the Arrange part of Arrange-Act-Assert (aka AAA) pattern, the most challenging part when dealing with database. This will be further discussed in the later "Point of Interest" section.


The following is the final testing code using MSTest, pretty straightforward:

public class DbTests

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

    public async Task Db_GetMovies()
        using (var db = await MockMovie.CreateAsync(CreateDb()))    // Arrange
            // Act
            var result = await db.GetMoviesAsync("comedy");
            // Assert
            Assert.AreEqual(3, result.Count);

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

    public async Task Db_GetMovie()
        using (var db = await MockMovie.CreateAsync(CreateDb()))    // Arrange
            // Act
            var result = await db.GetMovieAsync(1);
            // Assert
            Assert.AreEqual(1, result.Count);

Points of Interest

Integration Test or Unit Test?

Since a real database is created and consumed behind the scene, are the above tests integration tests or unit tests? Well, the answer lies in how you define integration test and unit test.

  • Logically, from single responsibility design standpoint, if you're testing more than 1 responsibility, it's an integration test. From this standpoint, the above tests are unit tests, if it fails, it tells you where exactly the bug is.
  • Physically, if you consider database as an external dependency, then the above tests can be considered integration tests.

Whether the above tests are integration tests or unit tests doesn't really matter. What matters is that your code should have test covered, as simple as possible. Because the rule No. 1 of testing is to test your own logic, testing data access code without database makes no sense. This blog post explains well why a database is needed to test data access code.

It's true that testing with database is slower, but this is something we have to live with. On the other hand, RDO.Data is optimized to run database tests with decent performance, for example, initializing a table with testing data is done with one server round trip instead of multiple inserts.

The DbMock Magic

The preceding MockMovie class, which is derived from the DbMock<T> class, plays the most important role in the testing. Each time you create a new instance of MockMovie class, an isolated database with testing data will be created automatically. How and where the database is created is implementation specific. In SQL Server, the mocking tables will be created within a separated schema named MockX in tempdb. Running preceding tests will end up with the following tables in tempdb, as shown in SQL Server Management Studio:

Image 1

You can safely debug your tests using existing database tools such as SQL Server Management Studio. Since every new instance of DbMock class ends up with different new tables created in the tempdb, data is well isolated in a known state. You don't have to worry about anything like rollback the database to an initial state, which is perfect for testing.

Writing your own DbMock class is very straightforward, all you need to do is to derive your class from DbMock<T> class, and override the Initialize() method with Mock(...) call(s), with provided DataSet to initialize the table.

For simplicity, the sample code contains only one table. You can specify multiple tables by calling multiple Mock(...) in the Initialize() override, in any order. Everything including the foreign keys will be sorted out automatically.

Tools Support

Writing code to provide DataSet for table initialization can be tedious. Tools support is provided to make it much easier. In order to benefit from the tools support, you must have RDO.Tools installed in Visual Studio (free if you're using Visual Studio community edition).

Right click anywhere In the Visual Studio code editor of your MockMovie class, the context menu will be displayed with "Generate Db..." and "Generate DataSet(s)...":

Image 2

You can have your DataSet(s) populated with data without writing a single line of code:

  • Click "Generate Db..." to transform your MockMovie class into a database.
  • Edit data in the database using your favorite tools such as SQL Server Management Studio.
  • Click "Generate DataSet(s)..." to transform your database back into DataSet(s).

Real World Usage

For simplicity, the sample code may look trivial. How will this work in the "real world"? Well, we ate our own dog food. Our backend website, the CRM to manage user registration, subscription purchasing/invoicing, license distribution and all, is 100% implemented using RDO.Data with tests fully covered.

Please note when using RDO.Data in a real world application, the data access code is no longer simple CRUD operations. It's more like implementing your business logic into strongly typed and well organized stored procedures (If you choose relational database as your centric data store, you should do it this way. This is another topic and will be discussed in another article). In the end, you're not testing CRUD operations, you're testing your business logic. Your application will be much more robust if you have well written tests.


  • 7th October, 2019: First release


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Weifen Luo
China China
I'm the author of RDO.Net [^] and a few .Net components.

Comments and Discussions

-- There are no messages in this forum --
Posted 8 Oct 2019


3 bookmarked