Mocking Data Access

[Nik]

Hello All,

I am trying to test my data access layer. I currently have an
interface (ILookupUser) which has two implementations:
LookupUserFromAD and LookupUserFromDB. Each of these objects have
their "guts" injected into them - be it a
DirectoryServices.DirectoryEntry or a EnterpriseLibrary.Data.Database.

I would like to mock these underlying objects, so I can test the way
LookupUserFromX acts when there is a problem. (SQL Exception or no
connection to domain for example.)

After much struggling, it appears to me that you cannot mock out these
objects. (I've also tried to mock out ADO.NET objects to make the DB
stuff work.) However, every "intro to mocking" article I find points
to database interactions as the "goto" reason for mocking. I must be
doing something wrong!

I have found one actual example of how to do it on the CSLA forum (I
am not using CSLA) at http://forums.lhotka.net/forums/thread/13097.aspx.
However, when I try to run that same code I get an error on the second
line: "Database db = mocks.DynamicMock<Database>();".

An help would be appreciated, especially a concrete example!

Thanks,
Nik

[Ayende Rahien]

The problem is that you are trying to mock interfaces that you don't own. This is fine when they were build with testing in mind, but the stuff that you are talking about is most certainly not easily testable.
I suggest that you would create IDirectoryEntry, which would map to DirectoryEntry, and an DirectoryEntryAdapter that forward all calls to the internal DirectoryEntry.
All your code would refer to IDirectoryEntry, which you control, and hence can mock.
Likewise for the database.

[Nik]

Ayende,

Great response time! I really appreciate that.

That makes a lot of sense to me, although it is bittersweet to hear.
I'm glad because I'm not going crazy and missing some really easy. It
is unfortunate that this stuff was not built with testing in mind,
especial the EnterpriseLibrary stuff, since it's just a wrapper on
ADO.NET classes to begin with.

Thanks for your help, and hopefully Microsoft will add some interfaces
we can mock to the (System|EnterpriseLibrary).Data.* namspace soon!

On Apr 2, 11:05 am, "Ayende Rahien" <aye...@ayende.com> wrote:
> The problem is that you are trying to mock interfaces that you don't own.
> This is fine when they were build with testing in mind, but the stuff that
> you are talking about is most certainly not easily testable.
> I suggest that you would create IDirectoryEntry, which would map to
> DirectoryEntry, and an DirectoryEntryAdapter that forward all calls to the
> internal DirectoryEntry.
> All your code would refer to IDirectoryEntry, which you control, and hence
> can mock.
> Likewise for the database.
>

> On 4/2/07, Nik <nikm...@gmail.com> wrote:
>
>
>
>
>
> > Hello All,
>
> > I am trying to test my data access layer. I currently have an
> > interface (ILookupUser) which has two implementations:
> > LookupUserFromAD and LookupUserFromDB. Each of these objects have
> > their "guts" injected into them - be it a
> > DirectoryServices.DirectoryEntry or a EnterpriseLibrary.Data.Database.
>
> > I would like to mock these underlying objects, so I can test the way
> > LookupUserFromX acts when there is a problem. (SQL Exception or no
> > connection to domain for example.)
>
> > After much struggling, it appears to me that you cannot mock out these
> > objects. (I've also tried to mock out ADO.NET objects to make the DB
> > stuff work.) However, every "intro to mocking" article I find points
> > to database interactions as the "goto" reason for mocking. I must be
> > doing something wrong!
>
> > I have found one actual example of how to do it on the CSLA forum (I

> > am not using CSLA) athttp://forums.lhotka.net/forums/thread/13097.aspx.

> > However, when I try to run that same code I get an error on the second
> > line: "Database db = mocks.DynamicMock<Database>();".
>
> > An help would be appreciated, especially a concrete example!
>
> > Thanks,

> > Nik- Hide quoted text -
>
> - Show quoted text -

[Ayende Rahien]

Not likely, the problem is with the granularity of the expectations.
The entire System.Data.* namespace is very granular, and it makes for hard testing.
There isn't really a technical hurdle to testing the System.Data.IDbConnection , etc.
The problem is the amount of code that it takes and how much time it consumes.
A simple example would be.

mockConnection = mocks.CreateMock<IDbConnection>();
mockCommand = mocks.CreateMock<IDbCommand>();

Expect.Call(mockConnection.CreateCommand())
  .Return(mockCommand);

mockParameter = mocks.CreateMock<IDataParameter>();

Expect.Call( mockCommand.CreateParameters() ).Return(mockParameter);

mockParametersList = mocks.CreateMock<IDataParameterList>();

SetupResult.For(mockCommand.Parameters).Return(mockParametersList );

mockParameter.ParameterName = "p1";
mockParameter.Value = "foo";
mockParametersList.Add(mockParameter);//should add parameter.

mockCommand.CommandText = "SELECT * FROM Tbl where id = @P1";

mockReader = mocks.CreateMock<IDataReader>();
Expect.Call(mockCommand.ExecuteReader()).Return(mockReader);

//etc

On 4/2/07, Nik <nik...@gmail.com > wrote:

[Nik]

Interesting,

That makes a lot of sense, there is quite a bit of setup work for just
executing a query, so I can understand why testing would be so much as
well. However, this example that you just wrote out is the most
complete example of mocking data access I have ever seen! (Thanks)

I have in the past just wrapped my data access code in a
TransactionScope and rolled back the transaction at the end of the
test. I'll debate continuing to do that on the DB, but I think I will
create an adapter for DirectoryEntry, since it's class usage is less
"wide".

On Apr 2, 11:21 am, "Ayende Rahien" <aye...@ayende.com> wrote:
> Not likely, the problem is with the granularity of the expectations.
> The entire System.Data.* namespace is very granular, and it makes for hard
> testing.
> There isn't really a technical hurdle to testing the

> System.Data.IDbConnection, etc.

> The problem is the amount of code that it takes and how much time it
> consumes.
> A simple example would be.
>
> mockConnection = mocks.CreateMock<IDbConnection>();
> mockCommand = mocks.CreateMock<IDbCommand>();
>
> Expect.Call(mockConnection.CreateCommand())
> .Return(mockCommand);
>
> mockParameter = mocks.CreateMock<IDataParameter>();
>
> Expect.Call( mockCommand.CreateParameters() ).Return(mockParameter);
>
> mockParametersList = mocks.CreateMock<IDataParameterList>();
>
> SetupResult.For(mockCommand.Parameters).Return(mockParametersList );
>
> mockParameter.ParameterName = "p1";
> mockParameter.Value = "foo";
> mockParametersList.Add(mockParameter);//should add parameter.
>
> mockCommand.CommandText = "SELECT * FROM Tbl where id = @P1";
>
> mockReader = mocks.CreateMock<IDataReader>();
> Expect.Call(mockCommand.ExecuteReader()).Return(mockReader);
>
> //etc
>

> > > - Show quoted text -- Hide quoted text -

[Eric Nicholson]

I think you're on track with the ILookupUser data layer interface.  Rhino should let you test the components that consume that guy pretty easily.  That should let you test everything up to (but not including) the data-layer.  At that point you're probably better served by hitting the database directly because if your data layer is pretty lean then there isn't a whole lot of benefit from testing it with mock objects, and like Ayende suggested, the value/cost ratio gets low fast.

That's when you can setup system tests specifically to test everything from your data layer down to the actual database or AD.  I still use NUnit to kick-off my DB tests, but I keep them separate (in a serparate solution or with NUnit categories) so I don't have to wait for them to run every time I run all tests inside the IDE.  Rolling back the transaction is a good option, and if you want a more realistic approach (actually hitting the tables) or finer grained control you could use a tool like MassDataHandler to populate a test DB with test data.

Take all that with a grain of salt of course...

Cheers,
Eric

On 4/2/07, Nik <nik...@gmail.com > wrote:

[Philip Nelson]

I think you are all right on the difficulty of mocking the system.data namespace. There is a possibly dead sourceforge project, http://sourceforge.net/projects/dotnetmock/, that did it basing the code on nmock. It actually implements nearly the full namespace. Unfortunately it seems to have died last summer. I had embedded it as an alternate data "provider" in my snapdal project and am now wondering which way to go.

One of the things I like about the way I used .net mock objects in snapdal is that test data doesn't have to be in the test source code. Lets face it, even in small doses, maintaining blocks of test data in string in code is PITA.

 

Philip - http://xcskiwinn.org/community/blogs/panmanphil

[Ayende Rahien]

For myself, I usually use an in memory / embedded database for this.
It makes it much easier to work with the whole thing.

[Nik]

Eric, thanks for the tool reference! I've not heard of that one
before, it looks interesting. I'm always poking around different
database automation projects, I'm still looking for "the solution" to
incremental automated database development. But that topic could be
devoted to its own, very long thread - best served in another list.

Philip, Ayende: Do you guys have any references for how you manage
your test data? I've found very little in the way of best practices
for this and would love to see how you guys go about managing this
data. Embedded database sounds interesting, especially if it allows
me to run the same test with different inputs.

Thanks!

On Apr 3, 9:52 am, "Ayende Rahien" <aye...@ayende.com> wrote:
> For myself, I usually use an in memory / embedded database for this.
> It makes it much easier to work with the whole thing.
>

> On 4/3/07, Philip Nelson <panmanp...@yahoo.com> wrote:
>
>
>
>
>
> > I think you are all right on the difficulty of mocking the system.datanamespace. There is a possibly dead sourceforge project,

> >http://sourceforge.net/projects/dotnetmock/, that did it basing the code
> > on nmock. It actually implements nearly the full namespace. Unfortunately it
> > seems to have died last summer. I had embedded it as an alternate data
> > "provider" in my snapdal project and am now wondering which way to go.
>
> > One of the things I like about the way I used .net mock objects in snapdal
> > is that test data doesn't have to be in the test source code. Lets face it,
> > even in small doses, maintaining blocks of test data in string in code is
> > PITA.
>

> > Philip -http://xcskiwinn.org/community/blogs/panmanphil

>
> > ----- Original Message ----
> > From: Eric Nicholson <enichol...@gmail.com>
> > To: Rhino...@googlegroups.com
> > Sent: Monday, April 2, 2007 4:45:52 PM
> > Subject: Re: Mocking Data Access
>
> > I think you're on track with the ILookupUser data layer interface. Rhino
> > should let you test the components that consume that guy pretty easily.
> > That should let you test everything up to (but not including) the
> > data-layer. At that point you're probably better served by hitting the
> > database directly because if your data layer is pretty lean then there isn't
> > a whole lot of benefit from testing it with mock objects, and like Ayende
> > suggested, the value/cost ratio gets low fast.
>
> > That's when you can setup system tests specifically to test everything
> > from your data layer down to the actual database or AD. I still use NUnit
> > to kick-off my DB tests, but I keep them separate (in a serparate solution
> > or with NUnit categories) so I don't have to wait for them to run every time
> > I run all tests inside the IDE. Rolling back the transaction is a good
> > option, and if you want a more realistic approach (actually hitting the
> > tables) or finer grained control you could use a tool like MassDataHandler
> > to populate a test DB with test data.
>
> > Take all that with a grain of salt of course...
>
> > Cheers,
> > Eric
>

> > On 4/2/07, Nik <nikm...@gmail.com > wrote:
>
> > > Interesting,
>
> > > That makes a lot of sense, there is quite a bit of setup work for just
> > > executing a query, so I can understand why testing would be so much as
> > > well. However, this example that you just wrote out is the most
> > > complete example of mocking data access I have ever seen! (Thanks)
>
> > > I have in the past just wrapped my data access code in a
> > > TransactionScope and rolled back the transaction at the end of the
> > > test. I'll debate continuing to do that on the DB, but I think I will
> > > create an adapter for DirectoryEntry, since it's class usage is less

> > > "wide".- Hide quoted text -

[Eric Nicholson]

Personally I've been keeping test data in XML files inside the test project.  I work in VB.Net most of the time (political reasons) so maintining the data in code is not just painful like Philip mentioned, it's not an option.  So I had to move it out.  Even if I was using some in-memory testing technique, I think it's mostly still applicable.

I don't know if it would qualify as "best practices", but what I've settled on is setting up test projects like:
SomeDataLayer.DataTests
   SomeDataObjectTests.vb
   AnotherDataObjectTests.vb
   TestData
      SomeDataObject.xml
      AnotherDataObject.xml
   Scripts
      CreateDB.sql - creates the actual SQLServer/SQLExpress DB if it doesn't exist
      DropTables.sql - deletes everything
      SetupDB.sql - creates all the tables/indexes/SP's/etc

The vb files are NUnit test fixtures for each major data object/class.  The xml files each correspond to a test fixture and contain test data that's loaded into the SQL server db by MassDataHandler.  Then either in the setup or individual test methods there is a call like "LoadTestData(filename, datasetName)" that cleans out the relevant tables and loads an xml fragment from the specified file into the DB.   The sql files are run once when the test process starts up to initialize the db (only takes a few seconds even on my old system). I plan to make the scripts a little more sophisticated soon in order to actually test upgrading between new releases of the application/DB structure.
 
Hope that helps.  I look forward to seeing what everybody else is doing.  And sorry if I've hijacked the thread, but like you Nik, I haven't seen much detail about this topic anywhere.

-Eric