SqlDependency.Start() fails due to seeming cross domain issue - can't find assembly log4net when attempting to serialize exception message

[Dan Bailiff]

I've started using the SQL Server Service Broker services to detect changes in a SQL table. The .NET Framework has the SqlDependency class to integrate. I have a working console application where this works beautifully. However, when I introduced this class to my supporting classes for use in a feature, the connection to SQL Server appears to fail for some unknown reason. "Unknown" is because the exception is due to a serialization error in the binary formatter which I suspect is attempting to call log4net. 

The thing is, all of our feature test projects use log4net just fine and have no problems referencing the library. Our features also access SQL to setup test data as well and this has worked fine as well. I don't really understand what this exception is telling me since it SHOULD work but doesn't and the exception seems unrelated to SqlDependency or log4net. I would appreciate any help in tracking down the root cause of this issue, and whether or not is my issue or something with the SpecFlow runner.

System.Runtime.Serialization.SerializationException : Unable to find assembly 'log4net, Version=2.0.8.0, Culture=neutral, PublicKeyToken=669e0ddf0bb1aa2a'.

Server stack trace: 

   at System.Runtime.Serialization.Formatters.Binary.BinaryAssemblyInfo.GetAssembly()

   at System.Runtime.Serialization.Formatters.Binary.ObjectReader.GetType(BinaryAssemblyInfo assemblyInfo, String name)

   at System.Runtime.Serialization.Formatters.Binary.ObjectMap..ctor(String objectName, String[] memberNames, BinaryTypeEnum[] binaryTypeEnumA, Object[] typeInformationA, Int32[] memberAssemIds, ObjectReader objectReader, Int32 objectId, BinaryAssemblyInfo assemblyInfo, SizedArray assemIdToAssemblyTable)

   at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWithMapTyped(BinaryObjectWithMapTyped record)

   at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.Run()

   at System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(HeaderHandler handler, __BinaryParser serParser, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)

   at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(Stream serializationStream, HeaderHandler handler, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)

   at System.Runtime.Remoting.Channels.CrossAppDomainSerializer.DeserializeObject(MemoryStream stm)

   at System.Runtime.Remoting.Messaging.SmuggledMethodCallMessage.FixupForNewAppDomain()

   at System.Runtime.Remoting.Channels.CrossAppDomainSink.DoDispatch(Byte[] reqStmBuff, SmuggledMethodCallMessage smuggledMcm, SmuggledMethodReturnMessage& smuggledMrm)

   at System.Runtime.Remoting.Channels.CrossAppDomainSink.DoTransitionDispatchCallback(Object[] args)

Exception rethrown at [0]: 

   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

   at System._AppDomain.CreateInstance(String assemblyName, String typeName)

   at System.Data.SqlClient.SqlDependency.CreateProcessDispatcher(_AppDomain masterDomain)

   at System.Data.SqlClient.SqlDependency.ObtainProcessDispatcher()

   at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)

   at MyCompanyNamespace.Framework.Utilities.Async.SqlWaiter.UntilSqlCommandResultChanges(SqlCommand sqlCommand, CancellationToken cancellationToken) in C:\git\cayan-main\TEF\Framework\CommonLibraries\MW.TEF.Framework.Utilities\Async\SqlWaiter.cs:line 30

   at MyCompanyNamespace.ComponentTest.StepDefinitions.SqlWaiterSteps.ThenTheContactsTableIsUpdatedBeforeATimeoutOfSeconds(Int32 timeoutSeconds) in C:\git\cayan-main\TEF\Gateway\ComponentTests\StepDefinitions\SqlWaiterSteps.cs:line 35

   at TechTalk.SpecFlow.Bindings.BindingInvoker.InvokeBinding(IBinding binding, IContextManager contextManager, Object[] arguments, ITestTracer testTracer, TimeSpan& duration)

   at TechTalk.SpecFlow.Infrastructure.TestExecutionEngine.ExecuteStepMatch(BindingMatch match, Object[] arguments)

   at TechTalk.SpecFlow.Infrastructure.TestExecutionEngine.ExecuteStep(StepInstance stepInstance)

   at TechTalk.SpecFlow.Infrastructure.TestExecutionEngine.OnAfterLastStep()

   at MyCompanyNamespace.ComponentTest.MyWebApp.Features.Web.WaitersFeature.ScenarioCleanup()

   at MyCompanyNamespace.ComponentTest.MyWebApp.Features.Web.WaitersFeature.VerifyThatASQLTableHasBeenUpdatedUsingAsyncMonitoring()

Correlation Id: a84869d4-d660-4e75-a8fc-39fc1f10a49f

When I insert a new row into the Contacts table asynchronously after a 3 second delay

-> done: SqlWaiterSteps.InsertContactTableRowAfterDelay(3) (0.0s)

Then The Contacts table is updated before a timeout of 5 seconds

public SqlWaiterSteps()

            : base()

        {

            _connectionString = ConfigurationManager.ConnectionStrings["mydatabase"].ConnectionString;

        }

        [Then(@"The Contacts table is updated before a timeout of (.*) seconds")]

        public void ThenTheContactsTableIsUpdatedBeforeATimeoutOfSeconds(int timeoutSeconds)

        {

            string sqlCommandText = @"SELECT [dbo].[Contacts_T].Last_Name_VC FROM [dbo].[Contacts_T] WHERE [dbo].[Contacts_T]Last_Name_VC = 'Test'";

            SqlWaiter sqlWaiter = new SqlWaiter(_connectionString);

            SqlCommand sqlCommand = new SqlCommand(sqlCommandText) { Notification = null };

            Task sqlWaiterTask = sqlWaiter.UntilSqlCommandResultChanges(sqlCommand, CancellationToken.None)

                .TimeoutAfter(new TimeSpan(0, 0, timeoutSeconds));

            try

            {

                Task.WaitAll(sqlWaiterTask);

            }

            catch (Exception)

            {

                // log the exception?

            }

            finally

            {

                Assert.That(sqlWaiterTask.IsFaulted, Is.False, "The Contacts table was not updated before the timeout condition");

            }

        }

public class SqlWaiter

    {

        private readonly string _connectionString;

        private OnChangeEventHandler _onChangeEventHandler;

        public SqlWaiter(string connectionString)

        {

            _connectionString = connectionString;

        }

        /// <summary>

        /// Returns a Task that signals when the submitted SqlCommand object has a result that is different.

        /// </summary>

        /// <param name="sqlCommand">The SqlCommand to check. It is run once during initialization to establish a baseline response value.</param>

        /// <param name="cancellationToken">Used to cancel the Task returned by the method.</param>

        /// <returns></returns>

        public Task UntilSqlCommandResultChanges(SqlCommand sqlCommand, CancellationToken cancellationToken)

        {

            var tcs = new TaskCompletionSource<object>();       // Used to create and return a Task 

            // reset the SqlDependency mechanism for the current operation

            SqlDependency.Stop(_connectionString);

            SqlDependency.Start(_connectionString);     // <<<--- here is where the exception occurs

            var sqlDependency = new SqlDependency(sqlCommand, _connectionString, 30);

            using (var connection = new SqlConnection(_connectionString))

            {

                // Define the event handler for when SqlDependency detects a change

                _onChangeEventHandler = (sender, sqlNotificationEventArgs) =>

                {

                    tcs.TrySetResult(true);     // signal that the task is complete

                    sqlDependency.OnChange -= _onChangeEventHandler;

                    SqlDependency.Stop(_connectionString);

                };

                sqlDependency.OnChange += _onChangeEventHandler;

                // We need to run the query once to establish a baseline for detecting changes.

                // Future calls to the query will trigger the event handler.

                connection.Open();

                sqlCommand.Connection = connection;

                sqlCommand.ExecuteReader();

            }

            // Define what happens when the cancellation token is activated

            cancellationToken.Register(() =>

            {

                tcs.TrySetCanceled();   // signal that the current Task is cancelled

                sqlDependency.OnChange -= _onChangeEventHandler;

                SqlDependency.Stop(_connectionString);

            });

            // return the task to the caller - the result is set in either the event completion or the cancellation token event

            return tcs.Task;

        }

    }