Techniques for Testing Data Access code

1. Roy Osherove SelaGroup Roy@Osherove.com ISerializable.com (blog) Techniques for Testing Data Access code

2. About Me • Agile Development • Consulting • Training • Mentoring • www.TeamAgile.com • .NET • Architecture • Practices • More..

3. ArtOfUnitTesting.com Book: The Art Of Unit Testing

4. Agenda • Unit Testing Vs. Integration Testing • To mock or not to mock the DB? • Rolling back DB state • Restore, Manual load, COM+, XTUnit, System.Transactions • Working and testing with DataSets • Pure DB tests with DataDude • Short song?

5. Demos • Rollbacks: • COM+ 1.0 • COM+ 1.5 (ServiceConfig) • XtUnit • MbUnit [DataRollback] • MbUnit [SqlDbRestore] • TransactionScope • Comparing DataSets • DataDude • DataSet loader with XML file update and delete • NHibernate ORM • ActiveRecord testing

6. Why should I care? • Automated testing and regressions • Data Layer also contains logic! • Cost of changes is lowered

7. What is a Unit-Testable System • For each piece of coded logicin the system, a unit test can be written easily enough to verify it works as expected while keeping the PC-COF rules • Partial runs are possible • Configuration is not needed • Consistent pass/fail result • Order does not matter • Fast

8. Integration Vs. Unit Test • Takes longer • Needs configuration • Needs large parts or whole system • Like a car engine

9. (and learn to tell the difference) Separate Unit From Integration

10. UI Logic DB Data Layer To mock or not to mock the DB?

11. DB Why NOT mock it? • You don’t test the DB logic itself • Keys • Indexes • Integrity Rules • Security • Triggers • Etc…

12. Unit testing against MySimpleClass A simple example of a data handler

13. Rolling back DB Data • XML File (semi manual loading) • DB Restore • Transaction • COM+ • COM+ 1.5 • System.Transactions • XtUnit • MbUnit

14. Demo XML Loading

15. Resetting an identity column USE pubs GO Delete from mytable GO DBCC CHECKIDENT ('mytable', RESEED, 0) DBCC CHECKIDENT ('mytable', RESEED) GO

16. Allowing IDENTITY INSERT set IDENTITY_INSERT mytable on ..insert set IDENTITY_INSERT mytable off

17. XML Loading Hard to maintain : • when there is parent-child relationships • When schema changes

18. Rolling back using COM+ • Test Driven Development with Microsoft.NET • (James Newkirk) • COM+ == System.EnterpriseServices • Distributed Transactions • MyObject:ServicedComponent

19. Serviced Tests MyTestClass:ServicedComponent Transaction MySimpleClass

20. Demo: Serviced Tests

21. SomeOtherClass Serviced Tests On Code with ADO.NET Transactions MyTestClass:ServicedComponent Transaction MySimpleClass Transaction 2

22. Problems with COM+ Rollback • What if you are calling other serviced Components? • What if they require a new transaction?

23. SomeOtherClass Serviced Tests On Serviced Components MyTestClass:ServicedComponent Transaction [Transaction( Required or supported) MySimpleClass:ServicedComponent

24. Demo: Serviced Tests On Serviced Components

25. SomeOtherClass Serviced Tests On Serviced Components (RequiresNew) MyTestClass:ServicedComponent Transaction [Transaction (RequiredNew or NotSupported) MySimpleClass:ServicedComponent Transaction 2

26. COM+ 1.5 MbUnit XtUnit System.Transactions Easier Rollback Syntax(es)

27. COM+ 1.0 Vs. 1.5 • 1.5 requires • Win XP SP 2 or higher • WinServer 2003 SP1 or higher • Otherwise – you have to use 1.0

28. ServiceConfig ServiceDomain Demo: COM+ 1.5 Transactions

29. System.Transactions (.NET 2.0) Using(TransactionScope scope = new TransactionScope) { …do DB work here } • Automatic promotion of transactions • Very simple syntax • None • ADO.NET Transaction • Distributed Transaction (COM+)

30. Demo: System.Transactions (.NET 2.0)

31. Demo: XtUnit

32. XtUnit Pros • Decide per test on rollback behavior • Create your own attributes easily • Tools.osherove.com

33. MbUnit features • SqlRestore Info: • Slow • Requires exclusive DB access • Rollback • Com+ 1.5

34. XUNIT.NET • http://www.codeplex.com/xunit • [AutoRollback] • Extensible

35. Types of data layers • Table Data Gateway • Row Data Gateway • Active Record • Data Mapper • Book: “Patterns of Enterprise Application Architecture” • Martin Fowler

36. NHibernate Tests Against NHibernate Based Apps MyTestClass Transaction MyClass

37. NHibernate Tests Against ActiveRecord MyTestClass Transaction MyActiveCategoryClass

38. Demo Testing the DB directly using DataDude

39. Summary • DB Integration tests are necessary • Try combination of • DB tests • Application-integration tests • DataDude is your friend! • Use frameworks • System.Transactions • XtUnit • MbUnit

40. A song by Roy Osherove (+ Simon & Garfunkel) It’s time for violence

41. Hello DB my old friend..

42. I need to work with you again

43. That stored procedure ain’t looking wellWho wrote that trigger should go to jailAnd that index..It is slower than a snail..What the hell?

44. I guess it’s time…

45. For violence…..

46. Man, whoever wrote this codeThat bastard’s gonna hit the roadNow the customer is gonna sueinstead of red my face are turning blue

47. And it seems likethere is no way out of thisthere’s just a hissI guess it’s time..

48. For violence…

49. I’m angry and I want a nameThat DBA should be ashamed!

50. What’s that you’re saying?It’s ME to blame?