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.

imageAs 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:

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:

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.

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 –

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

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.

Comments 2

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.