I think you’ll probably want to do some integration testing to check logic that is enforced by your database structure, for example constraints, triggers, autoincrement columns, etc. You should, however, for unit testing mock out whatever framework components that your DAL relies upon as you want (in your unit tests) to test only those components that you have coded. You don’t really need to test methods on SqlCommand or SqlConnection (for example). You should assume that the framework components that you use work and create stubs or mocks for them that return known data (good, bad, exceptions) to your methods to make sure that your methods work properly. Without mocking you are responsible for generating the data in the database and making sure that it is correct. You also leave open dependencies on the network, the database itself, etc. that may make your tests brittle.
Also, unit testing does not remove the need for other types of testing. Integration tests and acceptance tests are still valid and need to be done. They probably don’t need to be done with the same frequency as unit tests and may not need to be as extensive as your code quality improves with unit testing, but unit testing is not a magic bullet.