Unit test insert/update/delete

Question

Hey,

I have googled this a little and didn't really find the answer I needed.

I am working on a webpage in C# with SQL Server and LINQ for a customer. I want the users to be able to send messages to each other. So what I do is that I unit test this with data that actually goes into the database.

The problem is that I now depend on having at least 2 users who I know the ID of. Furthermore I have to clean up after my self. This leads to rather large unit tests that test alot in one test.

Lets say I would like to update a user. That would mean that I would have to ceate the user, update it, and then delete it. This a lot of assertions in one unit test and if it fails with updating i have to manually delete it.

If I would do it any other way, without saving the data to DB, I would not for sure be able to know that the data was present in the database after updating etc.

What is the proper way to do this without having a test that tests a lot of functionality in one test?

Answer 1

Have a fixtures file (in XML, SQL, YAML, whatever) that you can load into the local test database with just a command.

E.g. a fixtures file for two users who need to message each other may look like (this is mine):

Member:

  Member_WibWobble:
    username:       Wibble_Wobble
    email_address:  michael+dev_wibwob@[removed].com
    password:       pw
    is_super_admin: true
    last_login:     "2010-01-06 12:12:57"
    Country:        country_AU
    UploadImage:
      type:         <?php echo UploadImage::TYPE_MEMBER_AVATAR."\n"; ?>
      upload:       "http://localhost/[removed]/images/wibwobble.jpg"

  Member_BunnyHugs:
    username:       BunnyHugs
    email_address:  michael+dev_bunny@[removed].com
    password:       pw
    is_super_admin: true
    last_login:     "2009-12-01 14:11:11"
    Country:        country_UK
    UploadImage:
      type:         <?php echo UploadImage::TYPE_MEMBER_AVATAR."\n"; ?>
      upload:       "http://localhost/[removed]/images/bunnyhugs.jpg"

PrivateMessage:

  PrivateMessage_1:
    subject:          "Yo"
    body:         |
      hi

      <b>escape this html please</b>

      bye
    is_read:          false
    Sender:           Member_WibWobble
    Recipient:        Member_BunnyHugs

Answer 2

Do all the test inside of a System.Transactions.TransactionScope block, and simply do not call Scope.Complete() ... All changes will be rolled back when you exit, and other users will not be able to see the temporary data you create in the database, so the test process will only affect the test machine..

You can enter a new user, read the database to verify that it was entered correctly, and whatever else you need to check, within the block of the TransactionScope...

Have the single unit test method impersonate the two different users, all within the one transaction ...

e.g. code

   using (var scop = new System.Transactions.TransactionScope())
   {
       // all your test code and Asserts that access the database, 
       // writes and reads, from any class, ...
       // to commit at the very end of this block,
       // you would call
       // scop.Complete();  // ..... but don't and all will be rolled back
   }

Answer 3

Unit testing databases can be a PITA due to the very nature of them (persisted storage).

To do this right, you should always start with a blank schema. Then have your testing code load fill in the data values that have to be there. This can be as simple as just executing a sql script which contains the schema and default data.

Once that is done, then start running your tests. Tests should encompass all of the crud operations your app will normally do.

If any failure occurs, it's not that big a deal because, again, you should start fresh every time any how.

Also, you want to keep that data around in case of a testing failure so that you can inspect the database state at the time the failure occurred. So, this is both a good and bad thing.

Along these lines, the db tests should be run on it's own test database instance. This way you don't have to worry about transient issues like someone changing a db structure underneath you as the tests are progressing.

Ultimately, database testing is a project in and of itself.