unit testing with rollback on database

I'm just starting to understand the importance of Unit Testing in a c# environment. Now, I'm wondering how do i implement a black-box unit test that does Inserts,Deletes and updates on a database and then cleaning up the data after a successful test.

How do you actually do a process that ro开发者_如何学Cllbacks data inserted/updated/deleted? do you simply reset the index and remove the inserted rows? or restore the original state of the table by creating a script?

please guide me, I appreciate it. thanks!

What we do here in our development cycle. we always have that unit testing and load testing in our mind when ever we are developing application. So we make a column in our every datadase's table with userId or else. Then when we run Load Test or Unit test we insert UserId -1 in that every column, pointing that it is a load test data and -2 in case of unit Test Data. then we have pre Define Jobs at data base end that will clean that data after some time.

As long as your test is concise, and i presume it must be - for testing your DAL, why not just do the insert / update / deletes in a transaction that is rolled back once your test is complete.

Another option is to just use specific Update / Delete scripts in your test cleanup methods to roll back the exact changes that you updated / inserted to their pre-test values.

I think deleting the rows in the CleanUp method should be good choice.

By this you will always be testing your deleting rows code.

I was doing a research recently and found this thread as well. Here are my findings, which might be of some help for the future readers:

• Make tests responsible for restoring the data they change. Sth like undo for the command. Tests usually know what data changes are expected, so are able to revert those in theory. This will surely involve additional work and could introduce some noise, unless it's automated, e.g you might try to keep track of the data created/updated in the test somehow generally;
• Wrap each test in transaction and revert it afterwards. Pretty much as the one above, but easier to implement with things like TransactionScope. Might be not suitable if app creates own transactions as transactions aren't composable in general and if app doesn't work with TransactionScope (there are issues with Entity Framework for example);
• Assert in some smart way on data relevant to test only. Then you won't need to cleanup anything unless there is too much data. E.g you might make your app aware of tests and set specific value to a test-only column added to every table. I've never tried that in practice;
• Create and initialize fresh database from scratch for every test;
• Use database backups to restore database to the point you need;
• Use database snapshots for the restore;
• Execute scripts to delete all the data and insert it again.

I personally use the latter and even ended up implementing a Reseed library, which does all the work for me.

Test frameworks usually do allow execute some logic before and after each test/test fixture run, which will mostlikely be needed for the ideas above. E.g for NUnit this implemented with use of OneTimeSetUp, OneTimeTearDown, FixtureSetUp, FixtureTearDown, SetUp, TearDown attributes.

One option is to use a Mock database in place of a real database. Here's a link that describes it.