2

For the help, here is my environment:

  • Visual Studio 2010 and c#
  • Sql Server 2008 R2 with Sql Management studio
  • NUnit

I'm in the process of creating a lot of integration tests that will interact with a database. Each test must have a database in a very specific state. I don't want to insert data with an ORM or directly with Sql Management studio.

What I want is a couple of sql scripts with a structure same like VMware snapshots.

For example:

--> [Main script with system tables like tables mapped to enum].sql

-- --> [A couple of rows in some tables].sql

-- -- --> [A couple of other rows in other tables].sql

-- --> [A couple of rows for another test].sql

-- -- --> [A couple of other rows for handle others cases].sql

With this type of structure, I will be able to rollback to a specific version of snapshot to set the database in a specific state and than create another branch of snapshots in the structure.

Before each tests, I clean all tables in the database and then execute a specific sql script.

Is it a good way to do integration tests with database?

Does exist tool to do this type of sql script structure?

Do you a a list of bests practices?

If it's not a good idea what I want to do, what is the best architecture, methodology to do this type of tests?

Thank you very much.

Improve this question
1
  • Why don't you want to insert data using an ORM? Commented Apr 20, 2012 at 19:57

2 Answers 2

Reset to default
3

You say that you don't want to use an ORM, but I choose to disregard that in my answer. Sorry :)

How to populate

I prefer inserting data into the database using the same operations that a user would. That is, if I need an order for a product in the database, I create the product using the admin interface, let a user create an account using the public interface and have that user place an order.

This doesn't have to be done by clicking buttons in the actual UI, but preferably using an underlying abstraction of the UI.

Benefits:

  • The database will have the same state as when executed for real, not the state you think it will have
  • If you need to make changes, e.g. to the schema, you only have to change the production code
  • More features are covered by the tests
  • The database is populated only with data needed for this perticular use case
  • The test are easier to read (it's not easy to read insert scripts)

Drawbacks:

  • It's slower
  • Does't work well if you need a lot of data (which is rare)

I've only needed a large amount of data for tests once. For that I created my own declarative domain specific language with which I could specify the necessary data in a (verbatim) string inside my test. Then I wrote a tool that parsed the data and inserted it into the database.

Clean the database

There are several ways to clean the database: re-create the database; delete all data; execute the test in a transaction and make a rollback... My faviourite, though, is not to clean the database. For every test I create a new user and a new product (using the example from above). I don't have to clean the database and still the tests won't interfere with each other. Also, I will have the data around after the test if I need to inspect it.

Hope it's somewhat helpful even though it's not really an answer to your question.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In fact, this is not the answer that I'm waiting for; it is better! The point that you say that mention the advantage of handling schema changes is a problem to me. I will have to change my mind but this will be for a better world. My code design is not appropriate but also, I will have to change that. Thank you very much. It's for answer like this that I lobe Stackoverflow.
1

We use FitNesse coupled with dbFit for this kind of database testing, we're also using SQL 2008 R2. I'm not sure what the structure of VMWare snapshots look like but FinNesse allows you to build up a hierarchical set of tests (with a wiki like structure and syntax).

The main benefits from my own point of view are:

  • dbFit will implicity run in a transaction and automatically rollback at the end of each test.
  • The hierarchical structure enables you to add setup and teardown scripts that can apply across multiple tests.
  • You can create a suite of individual tests that execute all the tests sequentially.
  • Integration (to some degree) with our continuous integration server, TeamCity.
Improve this answer

1 Comment

The main point of my question is not with which tool will I execute my tests but with which tool will I create my sql scripts that will be used by my tests. Thank you for the information about dbFit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.