title: Integration testing database, am I doing it right?
body: <p>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. </p>
  
  <p>I found the idea of integration testing that wraps your test's database manipulating logic in a <code>TransactionScope</code> object that rolls back the changes when done. </p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <pre><code>    [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
  
          }
  
      }
  </code></pre>
  
  <p>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?</p>
  
  <p>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! </p>
  
  <p>/edit The NDbUnit test that DOES affect/change my database...</p>
  
  <pre><code>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);
  }
  </code></pre>