Integration testing database, am I doing it right?
asp.net-mvc-4, c#, integration-testing, unit-testing
Solution
I do not want to use mock methods / objects because the queries can be complicated and creating test objects for that is too much of an effort.
This is the right strategy. Most "interesting" bugs tend to happen at the "boundary" between client code and the (real) database.
How can I start with an empty database?
Purge the database programmatically before each test. You can automate that by putting the purging code in a method marked with [TestInitialize] attribute. If your database happens to use ON DELETE CASCADE, deleting all data might be as simple as deleting few "top" tables.
Alternatively, just write your tests to be resilient in case there is already some data in the database. For example, each test would generate its own test data and use the specific IDs of the generated data only. This allows you better performance as you don't need to run any extra purging code.
And most importantly, how do I properly test methods on an actual database without ruining my old data?
Forget about it. Never run such tests on anything but a development database that you can throw-away as needed. Sooner or later you will commit something you did not intend to, or hold some lock longer than acceptable in production (e.g. by hitting a breakpoint in the debugger), or modify the schema in an incompatible manner, or just hammer it with load tests that would otherwise affect the productivity of real users...
Problem
I want to test methods in my MVC4 application that rely on and work with a database. I do not want to use mock methods / objects because the queries can be complicated and creating test objects for that is too much of an effort. I found the idea of integration testing that wraps your test's database manipulating logic in a `TransactionScope` object that rolls back the changes when done. Unfortunately, this does not start with an empty database at first and it also makes the primary keys count on (i.e., when there are already a few items in the database with Primary keys 1 and 2 then after I run the test it counts on with 4), I do not want this. This is an "integration test" I came up with just to test if products are actually added (an example, I want to create more difficult test that check the methods once I have the infrastructure right). ``` [TestMethod] public void ProductTest() { // Arrange using (new TransactionScope()) { myContext db = new myContext(); Product testProduct = new Product { ProductId = 999999, CategoryId = 3, ShopId = 2, Price = 1.00M, Name = "Test Product", Visible = true }; // Act db.Products.Add(testProduct); db.SaveChanges(); // Assert Assert.AreEqual(1, db.Products.ToList().Count()); // Fails since there are already items in database } } ``` This raises a lot of questions, here's a selection: How can I start with an empty database? Should I attach another database to the project with its own context and connection string? And most importantly, how do I properly test methods on an actual database without ruining my old data? I have been busy all day trying to figure out how to unit/integration test my database logic. I hope some experienced developers here can provide some help! /edit The NDbUnit test that DOES affect/change my database... ``` public class IntegrationTests { [TestMethod] public void Test() { string connectionString = "Data Source=(LocalDb)\\v11.0;Initial Catalog=Database_Nieuw; Integrated Security=false;"; //The above is the only connectionstring that works... And is the "real" local database //This is not used on Jenkins but I can perhaps attach it??? NDbUnit.Core.INDbUnitTest mySqlDatabase = new NDbUnit.Core.SqlClient.SqlDbUnitTest(connectionString); mySqlDatabase.ReadXmlSchema(@"..\..\NDbUnitTestDatabase\NDbUnitTestDatabase.xsd"); mySqlDatabase.ReadXml(@"..\..\NDbUnitTestDatabase\DatabaseSeeding.xml"); // The data mySqlDatabase.PerformDbOperation(NDbUnit.Core.DbOperationFlag.CleanInsertIdentity); } ```