How to write Tests for Data Access Layer in .Net?

views:

273

answers:

+7 Q:

How to write Tests for Data Access Layer in .Net?

Is there a framework to write unit test for Data Access layer? There are several issues in this.
1. How to populate your database?
2. How do we make sure that one test is not modifying values in db that can fail another test?

Is there a good framework available which can take care of above issues?

Update: I came across nDBUnit which can take care of infrastructure issues with testing data access layer.

http://code.google.com/p/ndbunit/wiki/QuickStartGuide

+3 A:

True unit tests wouldn't access the db. That said, mbUnit has a rollback attribute that can be used for tests that do access the db.

brian 2010-02-22 00:39:09

Have to disagree with this. Unit-testing data access classes against anything that *isn't* an actual database is just empty ritual. A "true" unit test would use the database creation/upgrade scripts that you surely keep under version control, construct a sandbox database for the tests, and tear it down at the end.

Aaronaught 2010-02-22 00:45:13

Not sure where you got your definition, but unit tests should not make any file accesses, db accesses (in memory representation is the way to go), etc as it can slow them down and make developers less likely to run them on a regular basis.

brian 2010-02-22 00:48:46

@user210118: What if the data access code internally calls a SP and for performance reasons there are some code written in SP. I would like to check if the SP is doing what it is supposed to do.

Amitabh 2010-02-22 00:52:34

@user210118: there is more than one definition of "unit test", and none of those definitions come from God.

John Saunders 2010-02-22 00:53:18

It's all semantics, unit or integration test, it amounts to the same thing.

Paul Creasey 2010-02-22 00:59:37

@Paul: ya thats what i mean. Call it integration test but then what is the best way to test Data Access Layer?

Amitabh 2010-02-22 01:01:35

@user210118 You can write unit tests that test your repository. You can call them "Database Unit Test" or "Repository Unit Test". I think what you are referring to is Mocking. If you are testing some different behavior and that has a dependency on some data access code then you should always mock out the data access code so your unit test can run independent of the data access.

azamsharp 2010-02-22 02:53:14

@Paul: yes it is semantics, but integration tests and units tests differ wildly (or at least should) in use. Units tests should be run every time a change is made and should test only some small unit of the code. Integration tests are used for testing the aggregation of pieces of code. These are obviously run less often and can therefore run a little longer without being burdensome on the developer.

brian 2010-02-22 16:04:40

@John: There is definitely more than one definition of unit test, but from a practical point of view, if you have to run tests (call them whatever you like) that have x number of minutes of overhead and are generally slow running, you as a developer are far less likely to run them every time a change is made.

brian 2010-02-22 16:07:04

@azamsharp: I wasn't necessarily referring to mocking, although that is one approach to removing the dependency on the db.

brian 2010-02-22 16:17:10

@user: I have no idea why you're assuming these tests will be slow, or why you assume they're not run every time any significant change is made. I use continuous integration with TFS, and all unit tests run on every build, which happens on every check-in.

John Saunders 2010-02-22 16:28:36

+3 A:

When you are testing your repositories which will access the database you must always make sure that database is cleared after the unit test is complete. You can perform this by running the test under the transaction OR you can use MbUnit Rollback attribute to rollback the changes automatically.

azamsharp 2010-02-22 00:44:39

But this will still not solve the issue of initial data population. I am looking for a framework that can make these things easier so that every one in the team can concentrate on writing Tests.

Amitabh 2010-02-22 00:59:34

Your test database should never be initially populated! It should be a blank database with nothing in it. Can you elaborate on initial data population?

azamsharp 2010-02-22 01:37:41

Many applications have only 1 or a very limited number of production installations. Starting from an empty database will likely only occur once in their lifetime, and that may have been years ago. Testing should be done with data similar to what will occur in production. If a production db will never be blank again, what do you gain by testing that situation?

ScottS 2010-02-24 18:47:07

yes, many business scenario depends on existing data in database. For true integration test of data access layer we need to consider production like data in database.

Amitabh 2010-02-27 17:41:49

@Amitabh, I thought you were talking about the unit tests. Yes for integration testing there must be existing data in the database. For populating the database you can run the scripts or use Migrater.NET to migrate the database to a particular version and after test migrate it back to old version.

azamsharp 2010-02-27 18:55:13

+1 A:

Don't. Unit tests don't access the db. That's what integration or functional tests are for.

I'd have a set of tests that validate that the code that manages the db communication is doing the right thing, and then touch it as infrequently as possible.

The reason to not do it in 'unit tests' is that by doing so, you run the risk of diluting the meaning of the phrase 'unit test', turning them into a grab bag of tests, which will typically make them slower and prevent developers from running them as frequently as they should.

kyoryu 2010-02-22 00:45:08

What if _I_ worry about how fast my unit tests are, and you don't?

John Saunders 2010-02-22 00:54:16

What good is unit-testing a *data access* class if the test doesn't actually access any data? If we're quibbling over definitions then fine, don't "unit test" your DAL at all, "integration test" it. The net result is the same thing. The only thing that *doesn't* make sense is mocking the database for low-level DAL tests; that's worse than useless, it shows you a green light when the class could in fact be totally broken.

Aaronaught 2010-02-22 01:04:10

@Aaronaught: Totally agreed. I'm a big proponent of 'don't mock what you don't own.' If unit tests define expected behavior, it's hard to define the behavior of code you don't own.

kyoryu 2010-02-22 01:26:58

@John Saunders: Not quite sure if that's supposed to mean 'what if some of the people care about unit test speed, and others don't', or if it's supposed to mean 'STFU and don't tell me how fast my unit tests should be.' I can respond to either, just want to make sure I respond appropriately :)

kyoryu 2010-02-22 01:28:26

@kyoryu: I mean that, the next time I'm working for you, you can tell me how fast my unit tests have to be. In the meantime, I and my management will determine that, and not some arbitrary doctrine that says to never have unit tests access databases, files, web services, etc.

John Saunders 2010-02-22 01:54:27

@John: Fair 'nuff. And I didn't suggest *not* testing such code. However, I strongly believe that part of the value of 'unit tests' is having a set of tests that do not have false failures and run in a trivial amount of time. If you get rid of these types of definitions, you may as well just call everything 'tests', as the divisions become somewhat meaningless.

kyoryu 2010-02-22 01:58:56

@kyoryu: why would my tests have false failures? Of course they don't, or they'd be worthless. They also run as fast as they can - but not faster.

John Saunders 2010-02-22 02:24:00

@John: My experience has been that tests that deal with the actual state of the system tend to have intermittent failures due to factors outside the control of the tests, race conditions, etc. Network-based tests, for instance, will fail if there is a general network failure happening while the test is being run. This doesn't mean that those tests are useless, but it does mean that failures in them must be examined and verified to ensure they represent an actual failure.

kyoryu 2010-02-22 02:33:33

@kyoryu: you're telling us more about yourself and your work experience than you are about unit tests. Yes, if the network fails, that's a problem. Maybe make the network not fail. Yes, if your test doesn't adequately consider preconditions, it won't always work. "So, don't _do_ that!"

John Saunders 2010-02-22 02:55:19

+13 A:

I guess I'll chime in, since I really dislike some of the answers so far.

Whether you call it a "unit test" or "integration test" or a "supercalifragilisticexpialidocious test" does not matter one bit in this instance; there's only one valid test for data access components and that it is to test it on actual data. Not production data obviously, but a reasonable facsimile thereof.

The very first thing you need to do is get the database itself under source control. Sadly, there is no framework for this; Microsoft goes part of the way in some versions of VSTS but the end result is still somewhat lacking. At least in today's world, you're going to have to do a lot of this work yourself. But do it, seriously - you won't regret it when a major update gets botched and you need to roll back the DB.

What you put under source control should be everything necessary to generate the newest schema, usually a baseline script, plus "configuration data" scripts (i.e. the contents of enumeration tables), and upgrade scripts to reflect recent schema changes. This gives you almost everything you need to perform "live" testing on a temporary database; your test only needs to download those scripts from source control and run them on a test server and/or a different database instance, usually using SQL Management Objects to run said scripts (SMO can handle GO statements and the like; a regular SqlConnection cannot).

Various tools can help you with the generation of test content in the test database. Probably the most popular is Red Gate's SQL Data Generator. These tools can also generate scripts to create the data, which is what you'll be using in your tests. Or, if you so choose, you can scrub the data from your production database and use SQL Server Management Studio to script whatever data you choose to keep for testing. Either way, keep your test data scripts in source control, same as the schema scripts, and when you need to test your DAL, download these scripts after firing up a DB instance and use them to populate the data.

I wish there were a single framework that would do all of this for you, but with the right collection of tools, libraries, and good development practices, you can make this into a much less painful process.

Aaronaught 2010-02-22 01:20:29

Agreed with this entirely.

kyoryu 2010-02-22 01:27:36

I've used Microsoft's SQL Server Express for test decks; we check the .mdf files into source control. (SQL Server will recreate .ldf files on the fly.) This is not fun, and you end up learning way more about RANU and User Instances than you ever want to know. But it works.

TrueWill 2010-02-22 01:44:24

@TrueWill: Hard core, hope those test databases are small, otherwise that'll chew up a lot of disk space on the SC server! It's definitely an option to consider if it fits with your infrastructure - for us, even if we pared our 1.5 TB database down to 1.5 GB for testing it'd still be a little ridiculous to check into source. ;)

Aaronaught 2010-02-22 01:51:40

Agreed. My gripe with calling them unit tests is what appears to be kyoryu's; as soon as we start calling them unit tests, we run the risk of them getting lumped together. Unit tests and integration tests serve different purposes and will generally need to meet different criteria.

brian 2010-02-22 16:14:01

Tests are tests. They all need to be run when a dependency changes. *My* gripe is with the practice of vigorously designing and running exactly those tests that are least likely to fail (or expose deficiencies) and treating any slower/more complicated test as a second-class citizen.

Aaronaught 2010-02-22 16:33:12

@Aaronaught - Our primary test deck .mdf is 5.5 MB. A table that has millions of rows in production might have a dozen in the test deck; we don't use this for load testing.

TrueWill 2010-02-22 19:41:29

ansaurus

tags:

views:

answers:

How to write Tests for Data Access Layer in .Net?

related questions