Integration test with SQL express DB Entity Framework

There are many ways to do integration testing against database, most people will used moq or Effort on Entity Framework (EF). But there were limitation to what they could do. You need to learn how to use moq, and although there were many post on how to use moq. I haven't been able to mock DbSet Set method in EF6 which I used, which is indication that you need to learn how to use moq properly.

Effort on the other hand, provide an in-memory database from schema created by entity framework. This is nice, however it does have limitation. Effort can only be used to simulate object that goes through entity framework pipeline, specific SQL operation like stored procedure, function, or trigger won't work.

What about SQL Express DB, this is a real database engine and you could easily init those DB with data, and re-init before doing the test again. This also serve as end-to-end testing that will ensure your application will work against database.

Some people do this test using SQL Server also, but using SQL Server required you to set tear up and tear down script running before and after test. Using SQL Express you can always start your test with fresh DB with condition you setup will be exactly the same every time you run it.

This post will showed you how to do this integration testing using SQL Express. Developing tools will be vary, but I'll be using Visual Studio 2015 community edition and the project that I'll be testing on is SharePoint 2013 full trusted code (Visual web part, and other "separation of concern" projects).

Step you need to setup this test:

1. Create full SQL script from your DB
• Open up your database with SQL Server Management Studio
• Right click on it > Tasks > Generate Scripts...
• Click button Next on Introduction screen
• At Choose objects, choose "Select specific database objects", click button "Select All", and click button Next
• Select where you want to store this SQL file. This file will become database initialize for our integration test DB. I put this file under VS test project
• Click Next & Next again to built the script
2. Add database on your test project
• Open up Server Explorer
• Right click on Data Connections > Create New SQL Server Database
• Enter SQL express instance "(localdb)\MSSQLLocalDB" at Server Name (I used SQL Express 2016, if you used SQL Express version lower than 2014 you need different instance based on version, ex: SQL Express 2012 instance will be "(localdb)\v11".
• Enter New database name, and click button OK
• We're going to move this DB to test project, so delete that DB connection.
• Your DB will be reside under default location SQL express that is in the root of the user profile  (C:\Users\<user name>, copy that DB into your test project.
3. Create database test in that test DB
   • Connect to your newly copied DB in test project, by going to Server Explorer
   • Right click on Data Connections > Add Connection...
   • Enter "(localdb)\MSSQLLocalDB" at Server Name
   • Choose Attach a database file > select your copied DB in test project
   • Give a Logical name to make it easier
   • Open up your generated SQL DB > click on icon Connect
   • Select Local > MSSQLLocalDB
   • Under Database Name, select yours DB logical name you've entered before
   • Click Connect
   • Click on icon Execute
   • Take note/fix every error encounter in this execute script, we need that note whenever we want to recreate test DB
4. Create & execute others SQL script that will fill your test DB with Data, or other SQL objects like Linked Server. You can make that under 1 SQL file or separate it, just make sure that you know which one need to be executed before and after.
5. Each time project built or rebuilt, file which has "Copy always" attribute will be checked for versions. If the versions are the same, a checksum comparison is made and will be copied if checksum is not the same. This is how we keep the DB always new before each run test. When we added that DB file, it will have property "Copy always" in "Copy to Output Directory". In case it didn't have that, this is how we set property "Copy always"
   • Right click on SQL DB file > Properties
   • Select "Copy always" on property setting "Copy to Output Directory"
6. Built connection string to that DB in build folder
• the connection string will be something like this "Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\<DBFileName>.mdf;Initial Catalog=<DBName>;Integrated Security=True;MultipleActiveResultSets=True;App=EntityFramework"
• Notice that I use keyword |DataDirectory|, that keyword will be replaced by value set with method AppDomain.SetData("DataDirectory", objValue) the default value is application folder (build folder).

That's all you need to create this integration test with SQL express.

If you are on Test Driven Development, you can always start from creating SQL express DB and create script to be used in real environment.

ref:

• https://effort.codeplex.com/
• https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113).aspx
• https://msdn.microsoft.com/en-us/library/0c6xyb66(v=vs.100).aspx
• https://msdn.microsoft.com/en-us/library/cc716756(v=vs.110).aspx
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc31ea59-5718-49b6-9f1f-7039da425296/where-is-datadirectory-?forum=sqlce