If I'm having Data Access Layer (nHibernate) for example a class called UserProvider and a Business Logic class UserBl, should I both test their methods SaveUser or GetUserById, or any other public method in DA layer which is called from BL layer. Is this a redundancy or a common practice to do?
Is it common to unit test DA layer, or that belongs to Integration test domain? Is it better to have test database, or create database data during test?
Any help is appreciated.
It is a good practice to write unit test for every layer, even the DAL.
I don't think running tests on the real db is a good idea, you might ruin important data. We used to set up a copy of the db for tests with just enough data in it to run tests on. In our test project we had a special web.config file with test settings, like a ConnectionString to our test db.
There's no right answer to this, it really depends. Some people (e.g Roy Osherove) say you should only test code which has conditional logic (IF statements etc), which may or may not include your DAL. Some people (often those doing TDD) will say you should test everything, including the DAL, and aim for 100% code coverage.
Personally I only test it if it has logic in, so end up with some DAL methods tested and some not. Most of the time you just end up checking that your BL calls your DAL, which has some merit but I don't find necessary. I think it makes more sense to have integration tests which cover the app end-to-end, including the database, which covers things like GetUserById.
Either way, and you probably know this already, but make sure your unit tests don't touch an actual database. (No problem doing this, but that's an integration test not a unit test, as it takes a lot longer and involves complex setup, and should be run separately).
In my experience it was helpful to test each layer on its own. Integrating it and test again. Integration test normally does not test all aspects. Sometimes if the Data Access Layer (I don't know nHibernate) is generated code or sort of generic code it looks like overkill. But I have seen it more than once that systematic testing pays off.
Is it redundancy? In my opinion it is not.
Is it common practice? Hard to tell. I would say no. I have seen it in some projects but not in all projects I worked in. Was often dependend on time/resources and mentality of the team / individiual developer.
Is it better to have test database, or create database data during test? This is quite a different question. Cannot be answered easily. Depends on your project. Create a new one is good but sometimes throws up unreal bugs (although bugs). It is depending on your project (product development or a proprietary development). Usually in an proprietary on site development a database gets migrated into from somewhere. So a second test is definitely needed with the migrated data. But this is rather at a system test level.
Unit testing the DAL is worth it as mentioned if there is logic in there, for example if using the same StoredProc for insert & update its worth knowing that an insert works, a subsequent call updates the previous and a select returns it and not a list. In your case SaveUser method probably inserts first time around and subsequently updates, its nice to know that this is whats being done at unit test stage.
If you're using a framework like iBatis or Hibernate where you can implement typehandlers its worth confirming that the handlers handle values in a way that's acceptable to your underlying DB.
As for testing against an actual DB if you use a framework like Spring you can avail of the supported database unit test classes with auto rollback of transactions so you run your tests and the DB is unaffected afterwards. See here for information. Others probably offer similiar support.