DevTeach 2007 -- Techniques for Testing Data Access Code with Roy Osherove

Roy’s writing a book.  The Art of Unit Testing.  Sample chapter available.

Why should I care

 Automated testing and regressions, data layer also can contain logic and the cost of changes is lowered.  How do you know if the impact of changes if you are asked to change something like a primary key definition after the application has gone into production?  Test and fix cycles will allow you to see defects, fix them and do it in a timely fashion.

How do you test logic that exists in triggers?  It’s real logic and it needs to be tested.

Definition of a unit testable system using PC-COF rules

P - Partial runs are possible

C - Configuration is not needed

C - Consistent pass/fail result

O - Order does not matter

F - Fast

Fast is not relative.  Fast needs to be fast.  All your unit tests need to run in a time frame that will not dissuade developers from running the full suite of tests.  i.e. 1/2 a second per test for 10,000 tests will take 1 hour and 23 minutes.  That is not fast.

Don’t ever do partial test runs.  This isn’t good integration.  You might know that a partial run is good enough, but will any other developer know that?

Unit Testing vs. Integration Testing

Integration test are slower by nature.  They need configuration and large parts or whole systems to complete.

Usually can be written later in the project (you need many parts or whole systems, you have to wait for them to be built).

Must be able to separate these two types.  Put integration tests in a different project.  You don’t want to run both types of tests together.

NEVER have developers get to the point where they say “It’s okay, it should fail”.

If you can’t run tests, they won’t pass.  If they don’t pass, no one will maintain them.  If you don’t maintain them, the test suite won’t be useful.

Broken window theory.  Set the standard and consistently apply them.  Always expect tests to pass.  It should be a requirement of your project at all times.

To mock or not to mock the DB?

Mocking the DB == Faster testing, no setup fees.

Problem – you don’t test the DB logic (i.e. keys, indexes, RI, etc.)

Never mock a database.  Mocking the data layer is not a bad thing though and the two are different.

Rolling back DB state

  • XML loading (semi manual)
    *   Identity fields are an issue.&nbsp; You will need to reseed or turn off and on the identity feature on&nbsp;the table if you want the ID to be the same.  <li>hard to maintain if:
        *   parent child relationships  <li>schema changes <li>Rolling back with COM+
  • James Newkirk* COM+ == System.EnterpriseServices

  • Distributed Transactions
  • MyObject:ServiceComponent
  • *   TestClass inherits from ServicedComponent and then every test runs inside a transaction which can be rolled back.&nbsp; The only problem is that identity columns will change.  <li>Requires the Transaction(TransactionOption.RequiresNew) attribute on the class  <li>Teardown requires code to run ContextUtil.SetAbort(); <li>what if you're testing other serviced components? <li>what if the objects under test require their own transactions? <li>Use COM+ v1.5 (Services without components)
  • doesn’t require inheritance from ServicedComponent* System.Transactions (.NET 2.0)

  • automatic promotion of transactions* very simple syntax (using clause)

  • will employ distributed transactions (COM+)
  • XtUnit
  • has rollback attribute which creates a transaction implicitly* must inherit from TestFixtureBase to use it

  • can attribute only some tests instead of all in the class
  • MbUnit

Agilista alert!  They aren’t in this presentation!  Instead I have Oren to be my eye candy.

Overview of Data Access Patterns

  • Rehash of Fowlers Data Access patterns

Working and testing with DataSets

 How do you compare 2 datasets?  Use Darrel Norton’s data compare extension to nUnit.

Pure DB tests with DataDude

Can auto generate a SQL unit test. Because the tests are in TSQL you can use BEGIN TRANS and ROLLBACK TRANS to handle ensuring data state.