Integration Testing with SQL LocalDb on your build server

September 18, 2018 / Uncategorized / 4 Comments

I recently needed to verify some LINQ code would work correctly against an EF IQueryable data source.  Instead of setting up a full SQL database server for testing only, I setup a LocalDB which is simply a .mdf file that serves as your SQL database.  I believe the SQL Server LocalDB Engine is installed with Visual Studio now and its really easy to use locally.  Here are some general tutorials that I used:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-2016-express-localdb?view=sql-server-2017

https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/working-with-sql?view=aspnetcore-2.1&tabs=aspnetcore2x

CI Builds

This all worked great for local integration testing but failed when our CI build in Azure Pipelines.  The failure occurred during our Testing step on the LocalDB-based integration tests:

Error Message:
System.Data.SqlClient.SqlException : Directory lookup for the file “D:\MyLocalSourceFolder\acme\Acme.Tests\Core\MyIntegrationTests\Contacts.mdf” failed with the operating system error 3(The system cannot find the path specified.).
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Obviously the test is looking for the mdf file using my local source path and that won’t work.

After searching around and troubleshooting it for a while I found my best solution was to check-in the LocalDb files (mdf and ldf), copy the files to output and change my connection string to use the current execution path.

image

image

I used the AppDomain.CurrentDomain.BaseDirectory static path as my base path and then the file path relative to the project root folder.


protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
     optionsBuilder.UseSqlServer($”Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=Contacts;AttachDbFilename={AppDomain.CurrentDomain.BaseDirectory}Core\\IntegrationTests\\Contacts.mdf;Integrated Security=True”);
}

I hope this post was helpful to you.  If so, please leave a comment below and share this post on Twitter so we can help others as well.

Cheers!

4 thoughts on “Integration Testing with SQL LocalDb on your build server”

  1. arun prasad

    thanks helped me a lot.

    This is the code I used.

    var Path = _env.WebRootPath;

    var MyPath = Path + “\\Tag.mdf”;

    services.AddDbContext(options =>
    options.UseSqlServer(“Server=(LocalDB)\\MSSQLLocalDB;Initial Catalog=Tag;AttachDbFilename=” + MyPath + “;Integrated Security=True”));

    Reply
      1. Jalpa

        Thanks for sharing this. I tried it but when I run the pipeline, I get the message “LocalDB not supported for this platform”. I am trying to create the pipeline with .Net core framework.

        Reply

Leave a Reply