How to test if a record was created in the database?

Question

I am coming from Laravel and new to ASP.net MVC. In Laravel I used to do this to assert if a record was created in database or not:

public function test_a_user_is_created_in_database()
{
    // Arrange
    // Act

    $this->assertDatabaseHas('users', [
        'email' => '[email protected]'
    ]);
}

Is there a way to accomplish the same thing in Xunit?

Answer 1

There is probably a more elegant way to accomplish the goal, but this works fine for my purposes:

public static void AssertDatabaseHas(string table, Dictionary<string, object> filters, 
    bool assertMissing = false) {

    using (MySqlCommand cmd = new MySqlCommand()) {
        cmd.Connection = GetDbConnection();

        // Assemble the WHERE part of the query
        // and add parameters to the command.
        var filterStr = "1 = 1";
        foreach (KeyValuePair<string, object> item in filters) {
            if (string.IsNullOrEmpty(item.Value.ToString())) {
                filterStr += " AND " + item.Key + " IS NULL";
            } else {
                filterStr += " AND " + item.Key + " = @" + item.Key;
                cmd.Parameters.AddWithValue(item.Key, item.Value);
            }
        }

        // Put the query together.
        cmd.CommandText = string.Format("SELECT 1 FROM {0} WHERE {1}", table, filterStr);

        // Execute the query and check the result.
        using (MySqlDataReader rdr = cmd.ExecuteReader()) {
            if (assertMissing) {
                Assert.False(rdr.HasRows, "Undesired record exists.");
            } else {
                Assert.True(rdr.HasRows, "Desired record does not exist.");
            }
        }
    }
}

A reverse of the function is also easily added:

public static void AssertDatabaseMissing(string table, Dictionary<string, object> filters) {
    AssertDatabaseHas(table, filters, assertMissing: true);
}

When both are added to a MyCustomAssertions class, they can be called like this:

public void test_a_user_is_created_in_database()
{
    MyCustomAssertions.AssertDatabaseHas("users", new Dictionary<string, object> {
        { "email", "[email protected]" }
    });

    MyCustomAssertions.AssertDatabaseMissing("users", new Dictionary<string, object> {
        { "email", "[email protected]" }, { "id", "10" }
    });
}

Note:

• The code can be easily adapted for MSTest if you happen to be using that; all you need to change is Assert.False to Assert.IsFalse and the same for True.
• This example uses MySQL but can probably be modified for any engine. For Npgsql (PostgreSQL) for example, change MySqlCommand to NpgsqlCommand and MySqlDataReader to NpgsqlDataReader.