Connection string in unit test project to reference database in app_data folder

Question

I am looking to reference my database file in my unit test project. This is an ASP.NET MVC app.

Please note: I know I should not be accessing the database in my unit tests but this is for a quick fix on one test that I need to have pass just now.

After the next milestone I will be mocking the database access methods etc.

So here is my connection string in my mvc app web config and the unit test ap.config files

<add name="DBConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB.MDF;Integrated Security=True;User Instance=True"
  providerName="System.Data.SqlClient" />

When I run the test I get an error:

Test method
ED.Tests.Controllers.CandidateControllerTest.PersonalDetailsStepPostShouldRedisplayIfNoSurnameSupplied 
threw exception:  System.Data.SqlClient.SqlException: 
An attempt to attach an auto-named database for file C:\Users\Desktop\ED\TestResults\LAPTOP-D 2009-07-22 18_16_20\Out\DB.MDF failed. 
A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

It seems to me the connection string is wrong but I'm not sure how to set the path properly. I have tried adding \..\.. and the directory names etc.

Answer 1

MSTest will run your unit test assembly in a completely different folder on every test run. The idea is that each run is a completely isolated case from previous and subsequent runs. It's actually kind of a pain to tell it to copy data files along with the rest of your application. You need to right-click on your solution (not your project), choose add, create a new test run configuration. Then you need to edit the test run configuration and specify which files will be copied to the test execution folder. There should be a sibling directory to your solution directory called TestResults which contains the folders used for each test run.

Answer 2

you can simply reference localdb like this:

<add name="DefaultConnection" 
connectionString="Server=(localdb)\v11.0;Database=WebPortalDb" providerName="System.Data.SqlClient"/>

where WebPortalDb is your database name.

Answer 3

Comment to Craig's answer: It shouldn't be a pain to deploy extra data files for unit test execution. You can use DeploymentItemAttribute (Microsoft.VisualStudio.TestTools.UnitTesting) at class or method levels to specify which files need to be copied prior to running those tests.