Simulating SQL Server (Part 1)

In my last post, I talked about creating random values for all kinds of interesting types.  One of the biggest complaints I get when encouraging developers to implement unit tests (or dare I say even start thinking about TDD as a development methodology) is that testing code that accesses databases is a huge pain.  For this reason, most code is written to use a data layer which is accessed by easily mockable interfaces.

But what of the data layer itself?  Well too often it goes untested, occasionally some brave developer might introduce a ‘test database’ into the solution, or even a ‘test script’ for building a test database.  But that takes time, it’s a pain and nine times out of ten it’s overkill.

As an alternative, I decided to create a really simple set of classes that implement IDataReader and IDataRecord, and (as much as possible) allow simulation of a SQL Server connection.  The goal was to allow unit tests to be created quickly and (most importantly) consistently without requiring a separate database.

The first step to achieving this aim was to be able to define what a recordset looks like.  All this information is required by the above interfaces, so it was a good starting place.  To do this I started by looking in the System.Data.SqlClient namespace which is defined in the System.Data assembly.  Using reflection I quickly found the internal MetaType  class, which handily defines all the known types supported by SQL Server.

From the information gleaned from reflection, I could create my own class that given a SqlDbType (or even a DbType) could fill in all the other useful information for you automatically, using an ugly (but highly effective) large switch statement.

In most cases the type is enough, however for more complex types, more complex information is needed.  The constructor accepts a number of optional parameters:

/// <summary>
/// Initializes a new instance of the <see cref="ColumnDefinition" /> class.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="sqlDbType">The columns type.</param>
/// <param name="length">The length (if fixed length).</param>
/// <param name="fill">if set to <see langword="true" /> expects the column to be full (only appropriate for fixed length columns).</param>
/// <param name="isNullable">if set to <see langword="true" /> the column is nullable.</param>
/// <param name="defaultValue">The default value (required if column is not nullable).</param>
/// <exception cref="System.ArgumentOutOfRangeException">Thrown if the <see paramref="sqlDbType"/> is unsupported.</exception>
/// <exception cref="System.ArgumentOutOfRangeException">Thrown if the <see paramref="defaultValue"/> is invalid for the specified <see paramref="sqlDbType"/>.</exception>
/// <remarks></remarks>
public ColumnDefinition([NotNull] string name, SqlDbType sqlDbType, int length = -1, bool fill = false,
                        bool isNullable = true, object defaultValue = null)
{
    ...
}

The fill  parameter helps indicate whether any values used fill the length (e.g. for fixed length types like char) or whether the length indicates a maximum (e.g. for varchar).  The isNullable  parameter is self-explanatory, but it is notable that the defaultValue  must always be supplied if isNullable  is false.

One we have column definitions we use them to define a recordset, this is done with our RecordSetDefinition  class, which accepts an enumeration of ColumnDefinition  objects in its constructor.  For example:

// To create a record that implement IDataRecord we start with a record set definition.
RecordSetDefinition recordSetDefinition = new RecordSetDefinition(
    new ColumnDefinition("ID", SqlDbType.Int),
    new ColumnDefinition("Name", SqlDbType.Char, 50),
    new ColumnDefinition("Description", SqlDbType.NVarChar),
    // This column is not nullable so defaults to true
    new ColumnDefinition("Active", SqlDbType.Bit, isNullable: false, defaultValue: true)
    );

This immediately meets our goal of providing a clean intuitive syntax for quickly defining a table (or recordset) to be used by our reader.

Once we have a definition for a recordset we can create records.  The easiest way is with the ObjectRecord  class.

IObjectRecord dataRecord = new ObjectRecord(recordSetDefinition, 1, "Test", "This is my test record");

The constructor used accepts a RecordSetDefinition  and a set of values (of type object ) which are validated against the definition.  Any missing values (in the above example the ‘Active’ column is not supplied) are set to their defaults as defined by the RecordSetDefinition .

If we don’t want to explicitly set the values, and we want more than their defaults, we can use the other constructor –

/// <summary>
/// Initializes a new instance of the <see cref="ObjectRecord" /> class.
/// </summary>
/// <param name="recordSetDefinition">The table definition.</param>
/// <param name="randomData">if set to <see langword="true" /> fills columns with random data; otherwise fills them with their default values.</param>
/// <param name="nullProbability">The probability of a column's value being set to SQL null (0.0 for no nulls) -
/// this is only applicable is <see cref="randomData" /> is set to <see langword="true" /> [Defaults to 0.1 = 10%].</param>
/// <param name="columnGenerators">The column generators is an array of functions that generate a value for each column, if the function is
/// <see langword="null" /> for a particular index then a random value is generated, if it is not null then the function is used.  The function takes
/// the current row number as it's only parameter and must return an object of the correct type for the column.</param>
/// <param name="rowNumber">The optional row number to pass to the generator.</param>
/// <exception cref="System.ArgumentException">Thrown if the number of column generators exceeds the number of columns in the record set definition.</exception>
/// <remarks></remarks>
public ObjectRecord([NotNull] RecordSetDefinition recordSetDefinition, bool randomData = false,
                    double nullProbability = 0.1,
                    Func<int, object>[] columnGenerators = null, int rowNumber = 1)
{
    ...
}

which will randomly fill in the columns with valid values, using the code we wrote in Generating Randomness.  For example –

IObjectRecord randomRecord = new ObjectRecord(recordSetDefinition, true);

With these two classes, we can create an object that faithfully implements IDataRecord, without installing a database into our test project.

Next time we will look at creating a record set and a data reader, but in the meantime, you can view the code on GitHub or use the download link below to see the complete project.