Sunday, June 14, 2009

DbDataReader.DoesColumnExist

Have you ever wanted to code something like the following:
var dbCommand = Database.DbProviderFactory.CreateCommand();
dbCommand.CommandText = "select * from MyTable";
var reader = Database.ExecuteReader(dbCommand, null);
if (reader.Read())
{
if (reader.DoesColumnExist("mycolumn")
{
//Then we know its safe to select the column.
}
}
You're probably thinking, why would I want to do that? well if you're building a generic data handler library and you don't know what columns have been selected but you know the columns that could be selected then it is very useful as using the indexer to return the data for the specified column will throw an exception if it doesn't exist.

I thought this extension method solves this problem and would be useful for others:
public static class DbDataReaderExtensions
{
public static bool DoesColumnExist(this IDataReader reader, string column)
{
if (reader.IsNull()) throw new ArgumentNullException("reader");
if (string.IsNullOrEmpty(column)) throw new ArgumentNullException("column");

for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i).Equals(column))
return true;
}
return false;
}
}
It's relatively straight forward but saves you from writing the same piece of code over and over again.

The unit test class for this is as follows:
[TestClass]
public class DataReaderExtensionsTests
{
[TestMethod]
public void ThrowsIfNullReaderIsPassed()
{
const MockDbDataReader reader = null;
AssertExt.Throws<ArgumentNullException>(() => reader.DoesColumnExist("foo"));
}

[TestMethod]
public void ThrowsIfNullColumnIsPassed()
{
var reader = new MockDbDataReader();
AssertExt.Throws<ArgumentNullException>(() => reader.DoesColumnExist(null));
AssertExt.Throws<ArgumentNullException>(() => reader.DoesColumnExist(string.Empty));
}

[TestMethod]
public void ValidExists()
{
var reader = new MockDbDataReader();
Assert.IsTrue(reader.DoesColumnExist("One"));
Assert.IsTrue(reader.DoesColumnExist("Two"));
Assert.IsTrue(reader.DoesColumnExist("Three"));
Assert.IsTrue(reader.DoesColumnExist("Four"));
Assert.IsTrue(reader.DoesColumnExist("Five"));
}

[TestMethod]
public void InvalidExists()
{
var reader = new MockDbDataReader();
Assert.IsFalse(reader.DoesColumnExist("foo"));
Assert.IsFalse(reader.DoesColumnExist("bar"));
}
}
You'll notice I have mocked the DbDataReader. Because there are mocking frameworks on the Compact Framework and probably will not be for some time (due to CF limitations) I have created a mocked class implementing the IDataReader interface. This mock class looks like the following:
public class MockDbDataReader : IDataReader
{
readonly Dictionary<int, string> getName = new Dictionary<int, string>();
public MockDbDataReader()
{
getName.Add(0, "One");
getName.Add(1, "Two");
getName.Add(2, "Three");
getName.Add(3, "Four");
getName.Add(4, "Five");
}

#region IDataReader Members

public void Close()
{
throw new NotImplementedException();
}

public int Depth
{
get { throw new NotImplementedException(); }
}

public DataTable GetSchemaTable()
{
throw new NotImplementedException();
}

public bool IsClosed
{
get { throw new NotImplementedException(); }
}

public bool NextResult()
{
throw new NotImplementedException();
}

public bool Read()
{
throw new NotImplementedException();
}

public int RecordsAffected
{
get { throw new NotImplementedException(); }
}

#endregion

#region IDisposable Members

public void Dispose()
{
throw new NotImplementedException();
}

#endregion

#region IDataRecord Members

public int FieldCount
{
get { return 5; }
}

public bool GetBoolean(int i)
{
throw new NotImplementedException();
}

public byte GetByte(int i)
{
throw new NotImplementedException();
}

public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}

public char GetChar(int i)
{
throw new NotImplementedException();
}

public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}

public IDataReader GetData(int i)
{
throw new NotImplementedException();
}

public string GetDataTypeName(int i)
{
throw new NotImplementedException();
}

public DateTime GetDateTime(int i)
{
throw new NotImplementedException();
}

public decimal GetDecimal(int i)
{
throw new NotImplementedException();
}

public double GetDouble(int i)
{
throw new NotImplementedException();
}

public Type GetFieldType(int i)
{
throw new NotImplementedException();
}

public float GetFloat(int i)
{
throw new NotImplementedException();
}

public Guid GetGuid(int i)
{
throw new NotImplementedException();
}

public short GetInt16(int i)
{
throw new NotImplementedException();
}

public int GetInt32(int i)
{
throw new NotImplementedException();
}

public long GetInt64(int i)
{
throw new NotImplementedException();
}

public string GetName(int i)
{
return getName.ContainsKey(i) ? getName[i] : string.Empty;
}

public int GetOrdinal(string name)
{
throw new NotImplementedException();
}

public string GetString(int i)
{
throw new NotImplementedException();
}

public object GetValue(int i)
{
throw new NotImplementedException();
}

public int GetValues(object[] values)
{
throw new NotImplementedException();
}

public bool IsDBNull(int i)
{
throw new NotImplementedException();
}

public object this[string name]
{
get { throw new NotImplementedException(); }
}

public object this[int i]
{
get { throw new NotImplementedException(); }
}

#endregion
}
All we have done is returned 5 for the FieldCount, then provided some data so when GetName() is called we return the relevant item in the dictionary to resemble a real data reader object.

We could have written an integration test but this is pointless as we know the DataReader class has been tested and works.

The unit test class above gives 100% code coverage.

5 comments:

Daniel Marbach said...

Hi Simon
Sorry to bother you again ;) I usually tend to program hybrid code which then can be tested on the pc platform with NUnit and famous mocking frameworks like NMock2. I try to minimize the difference between the two platforms such that the general "logic" can be normally tested with NUnit and mocking frameworks. I hope you understand what I want to say because I'm a bit tired...

Greets Daniel

Simon Hart said...

Hi Daniel,

Good point. Your point works to some degree. We use Rhino with MSTest - MSTest because it's integrated with TFS. Of course there are no mocking frameworks for the Compact Framework, sadly.
The example I posted could easy be tested with Rhino, MOQ or NMock etc but I think there is no substitue for testing device code on a actual device. If I were to test it on a desktop with a desktop mocking framework, then the test project would have to be a full .NET project. It's then a question of which classes can be tested on the desktop and the device and which ones can only be tested on the device. I find it easier to have a blanket rule to always test on the device against the CF.

Of course testing would be a lot easier if using a mocking framework. The thing I'm working on a at the moment could easily take advantage of desktop testing and mocking frameworks. It just means I'd have to divide my test project up.

Simon.

Unknown said...

Hi Simon,

Valid point but I think in this case all you are doing is validating that your extension method is calling the correct method on the IDataRecord you've not changed any of the native functionality.

Matt

Simon Hart said...

Hi Matt,

Long time no speak!

We've just had another one, a little girl so we are very pleased!

Going back to this, this is the whole point of mocking implementations out as this test is a unit test I'm not interested in spanning multiple dependencies as it makes my test more complex and brittle.

I shouldn't need to test the native implementation DbDataReader because i don't own it and it has already been tested. If this was an integration test, I wouldn't mock IDataReader.

Simon

Unknown said...

I wasn't very clear but my point was your extension method is not specific to the compact framework and their is nothing preventing you from using it with in .net as a whole.

My suggestion is that although you are using a cut down version of .net it doesn't mean you need to cripple your test library. Your blanket rule of testing against your device is all well and good but in this case you are making more work for yourself just to test that your code calls a method on an interface. Surly this is what integration tests are for.