I've put up some tests using VisualStudio integrated test enviroment, they simultate what my web application would do by calling BLL methods (they only ones the UI layer should know and interact with)...
So if their behaviour is correct - I get my tests passed - why should I write tests for lower layers such as DAL/Stored Procedures as many literature suggest me to do?
Thanks everyone, Peter.
Speaking from an NUnit background
Testing the SPs/data access is not as easy to do. You have to ensure you have a "clean" database each time your tests run, with expected data in there at the start to guarantee you get the intended results out. So you either need to restore a db from a clean backup each time, or ensure your tests setup the database with the data it needs before the tests start.
Unit Tests should ideally run as fast as possible, and is one of the reasons why the DAL is often mocked when unit testing - to remove the costly process of database interaction. The thing you have to be careful of, is if you do write "unit tests" for SPs/DAL then the impact on test execution time could rocket which in my experience can lead to people not running the tests as they take too long. Espeically in a Continuous Integration environment/build environment, the longer the tests take to run, the longer it tasks to get a build prepared.
My personal opinion is that I like to use a combination of mocking and actual db access in my unit tests, as I like to know that my code will run as expected all the way through to and from the db, to catch silly mistakes like SPs missing/errors (bordering between unit/integration/functionality tests). But where it's not so important, I use mocks.
End-to-End testing is good and makes sure that your application is working for certain scenarios.
Misko Hevery put a good blog post on the test categorization where he splits unit-test,integration test and end-to-end testing.
Unit-Testing Unit testing checks that the logic in that function method is doing the correct thing and that error handling is done correctly. These tests should ideally run in milliseconds not seconds. If a function needs to interact with something, like the DAL, you should mock that interface of the DAL since true interaction would take a long time to run. These offer the best Return on Investment
Integration Testing This level of testing checks that interaction between Business Logic layers do exactly what they should be doing. This is where your unit-test would interact with an interface,like the DAL, and check that the 'wiring' is correct. There should be a few of these but not too many as that would impact your build time
End-to-End Testing These are good as they check that everything hangs together from the UI all the way down to the DAL. These test a lot more of the 'wiring' and check that what your user can do won't kill your application. These can also include your FitNesse and Web Tests, like Selenium, are passing.
Unit Testing offers the best return on investment and will catch a lot more costly bugs than the other areas but its good to have a good mix of the lot.