Visual Studio Online Unit Test and localDb

Question

I have a solution that is built on Visual Studio Online. All the unit tests in this solution require a database, I tried to get the build use a test's dedicated localdb (by adding a light mdf file in the test project and use a localdb connection string) but it fail with this error (everything work fine on my desktop) :

System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=29460; handshake=161; ---> System.ComponentModel.Win32Exception: The wait operation timed out.

EDIT

The connection string is

<add name="TestDb" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=AeroBase;Integrated Security=true;AttachDBFilename=|DataDirectory|\App_Data\AeroBase.mdf" providerName="System.Data.SqlClient" /> 

I use EF6 code first, repository and unit of work patterns to access it. Here is the DbContext :

public class AeroDataContext : DbContext, IDbContext
{
private Guid DataContextId;
private string _name;
public string Name { get { return _name; } }

public AeroDataContext(string cnxStringName, string cnxString)
    : base(cnxString)
{
    this.Database.Log = delegate(String name)
                        {
                           // Debug.WriteLine(name);
                        };
    _name = cnxStringName;
    this.Configuration.LazyLoadingEnabled = false;
    DataContextId = Guid.NewGuid();
    Debug.WriteLine("AeroDbCreation Id = " + DataContextId.ToString());


}
}

The DbContext is instanciated using a unitOfWorkScope :

    public class UnitOfWorkFactory : IUnitOfWorkFactory
{

    private string _cnxStringName;
    private string _cnxString;

    public UnitOfWorkFactory(string cnxStringName, string cnxString)
    {
        _cnxString = cnxString;
        _cnxStringName = cnxStringName;
    }

    public IUnitOfWorkScope GetUnitOfWorkScope(bool disposeAtEndOfContext = true)
    {

        return new UnitOfWorkScope(new AeroDataContext(_cnxStringName, _cnxString), disposeAtEndOfContext);
    }
}

which allows me to do things like this in the test (and in the app)

       [TestMethod]
    public void DB_Get_LFFE_Airport_By_ICAOInclude_SubType()
    {
        //structuremap container built in the TestInitilized method
        IUnitOfWorkFactory _uowf = container.GetInstance<IUnitOfWorkFactory>();
        using (IUnitOfWorkScope uows = _uowf.GetUnitOfWorkScope(true))
        {

            IAirportRepository repo = uows.GetRepository<IAirportRepository>();
            Airport ar = repo.SearchByICAO("LFFE").FirstOrDefault();
            AirportValidator.LFFE(ar);
           
        }
    }

Is this scenario even possible? is there any other way to do that?

Answer 1

This very likely has to do with LocalDb not being initialized on the VSO build server that gets spun up to run the build.

According to https://github.com/ritterim/automation-sql, LocalDb may be installed, but not initialized.

LocalDB installed but can't Connect

You may have LocalDB installed, but never initialized the instance on your machine. Run this command via command prompt.

LocalDB SQL EXPRESS 2014

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" create "v12.0" 12.0 -s LocalDB SQL Express 2012

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" create "v11.0" 11.0 -s Verify that the command worked by using SQL Server Management Studio to connect to the instance.

My solution:

Every time VSO launches a build, it creates a brand new virtual machine from a template to run the build. In my case, I wanted to create a small LocalDb database dynamically in order to run unit tests against.

I'm using the RimDev.Automation.Sql nuget package (http://www.nuget.org/packages/RimDev.Automation.Sql/) to allow my tests to programmatically create the database.

I also wanted to integrate with Unity and Entity Framework 6 so that it mimics my database access in production as closely as possible. To do this, I created a based class that all of my tests inherit from. One thing that I wanted is for all of my tests to share the same database, so I made the LocalDb creation static.

In addition, the initialization runs a set of scripts in the Resources folder in order to pre-populate the database with stock data if you so wish. You'll need to make sure that the sql scripts are marked to copy to the output folder (see the properties) so that the files will be in the proper path when the unit test is running.

If you want to create a new database per test class or even per test, this could easily be changed.

using System;
using System.Data.Entity;
using System.IO;
using Microsoft.Practices.Unity;
using Playground.Model;
using RimDev.Automation.Sql;

namespace Playground.UnitTests
{
    public abstract class TestBaseClass
    {
        // For now, these are static.  We may want to change them at some point
        // to be per class so we create separate databases for each class run, but
        // for now, let's not do that for speed sake.
        private static readonly IUnityContainer _container = new UnityContainer();
        private static bool _isRegistered = false;
        private static readonly object _syncRoot = new object();
        private static LocalDb LocalDb = new LocalDb(databaseName: "PlaygroundTestDb", databasePrefix: "pg", version: "v12.0");
        private static bool _isInitialized = false;

        protected TestBaseClass()
        {
            RegisterComponents(_container);
            InitializeData();
            _container.BuildUp(GetType(), this);
        }

        private void InitializeData()
        {
            lock (_syncRoot)
            {
                if (!_isInitialized)
                {
                    var dbContext = _container.Resolve<PlaygroundEntities>();
                    Database.SetInitializer(
                        new MigrateDatabaseToLatestVersion<PlaygroundEntities, Playground.Model.Migrations.Configuration>());

                    // Make sure database exists.
                    dbContext.Database.Initialize(true);

                    foreach (
                        var f in Directory.GetFiles(Path.Combine(Environment.CurrentDirectory, "Resources"), "*.sql"))
                    {
                        dbContext.Database.ExecuteSqlCommand(File.ReadAllText(f));
                    }
                }
                _isInitialized = true;
            }
        }

        private void RegisterComponents(IUnityContainer container)
        {
            lock (_syncRoot)
            {
                if (!_isRegistered)
                {
                    // WARNING!  Most methods in the unity container are not thread safe.  See http://unity.codeplex.com/discussions/27496
                    // We may need to expose protected methods to register certain types.  For now, assume all of the
                    // tests use the same injected objects.  If a test REALLY needs to a different dependency, the test can
                    // manually create it as well.
                    container.RegisterType<PlaygroundEntities, PlaygroundEntitiesTest>(new TransientLifetimeManager(),
                        new InjectionConstructor(new object[] {LocalDb.ConnectionString}));
                }
                _isRegistered = true;
            }
        }
    }
}

Here is a sample test:

using System.Linq;
using Microsoft.Practices.Unity;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Playground.Model;

namespace Playground.UnitTests
{
    [TestClass]
    public class UnitTest1 : TestBaseClass
    {
        [Dependency]
        public PlaygroundEntities Db { get; set; }

        private static bool _initialized = false;

        [TestInitialize]
        public void TestInitialize()
        {
            if (!_initialized)
            {
                Db.Playgrounds.Add(new Playground.Model.Playground() {Name = "Dave's playground", Location = "SomeTown"});
                Db.SaveChanges();
                _initialized = true;
            }
        }

        [TestMethod]
        public void TestMethod1()
        {
            var p = Db.Playgrounds.FirstOrDefault(pg => pg.Name == "Dave's playground");
            Assert.IsNotNull(p);
        }

        [TestMethod]
        public void TestMethod2()
        {
            var p = Db.Playgrounds.FirstOrDefault(pg => pg.Location == "SomeTown");
            Assert.IsNotNull(p);
        }
    }
}

Lastly, in my test project, I have the test entities object.

using Playground.Model;

namespace Playground.UnitTests
{
    public class PlaygroundEntitiesTest : PlaygroundEntities
    {
        private PlaygroundEntitiesTest()
        {
        }

        public PlaygroundEntitiesTest(string connectionString) : base(connectionString)
        {
        }
    }
}

In my models project, I have my entity and my context.

Playground.cs

using System;

namespace Playground.Model
{
    public class Playground
    {
        public Guid Id { get; set; }

        public string Name { get; set; }

        public string Location { get; set; }
    }
}

PlaygroundEntities.cs

using System.Data.Entity;

namespace Playground.Model
{
    public class PlaygroundEntities : DbContext
    {
        public PlaygroundEntities() : base("PlaygroundConnectionString")
        {
        }

        public PlaygroundEntities(string connectionString) : base(connectionString)
        {
        }

        public virtual DbSet<Playground> Playgrounds { get; set; }
    }
}

Lastly, I set up a post-build step in the unit test project to execute the command to initialize LocalDb as follows:

The full command is

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" create "v12.0" 12.0 -s

Then, it was as simple as pushing to Visual Studio Online and launching my build.