NullReference

When integrating with legacy databases you’re often required detect changes by polling. This method have some quirks that can be problematic at times. The first issue is that you need to figure out when rows has been inserted or updated. If your legacy db has timestamp columns for inserts and updates this is quite straight forward, just remember when the time you lasts checked for changes and get all changes that has occurred since then. If no such columns exists you’re in trouble and the pain of writing manual code for keeping track of all that awaits you. The other problem is that it’s hard to figure out which fields that was updated. This might not be a big issue but if you need to make decisions based on that info you need maintain a copy of the data in order to figure out what has changed. This is almost always the case if you want to move a way from CRUD style events like CustomerUpdated and the likes to a more business aligned model with events like CustomerActived, CustomerMoved etc etc.

We faced all the problems above and decided to tackle the problem from another angle. As a nice coincidence I had noticed the Joseph Daigle had created a ITransport implementation for NServiceBus that work with SQL Server Service Broker (SSSB). I had to patch the transport, to support xml serialization in order to be able to send messages direct from within SQL Server. With this in place we could add triggers to the relevant tables to add messages to the SSSB queue.

We added the following stored procedure to help with creating xml in the correct format for the NServiceBus xml parser and finally submitting the message to the queue.

For instructions on how to setup SSSB and create the necessary Services and queues please read more here.

Receiving messages from SSSB

The next step was to enable our regular NServiceBus endpoint to receive messages from SSSB. Our main transport is Msmq so we have to start up the SSSB Transport and forwards all incoming messages to our regular transport using SendLocal. This is easily done by implementing IWantToRunAtStartup causing the NServicebus Host to kick off a separate thread to handle this. Next we subscribe to the TransportMessageReceived event and send the incoming messages to our Msmq transport for regular message processing. This combined with the fact that SSSB supports distributed transactions gives us a nice and robust bridge between SSSB and Msmq.

The code is displayed below.

The image below outlines the moving parts of this solution

In closing

This is an example of using the SSSB transport to do event based interop with SQL Server. You can of course use the transport for pure SSSB backed solutions where you use SSSB queues as your main transport for messages between your regular NServiceBus endpoint. This removes the dependency on MSMQ but will make your SQL Server a single point of failure and a potential bottleneck.

Pick your poison!

We had a discussion today on how a test can tell you that something is off with your design. If a feature is hard to test you should listen carefully because your tests might be telling you something. The piece of code we where discussing is setting up return values for queries against our persistent store.

Stub(x=>x.Query(Arg<Func<Customer,bool>>.Is.Anything)) .Return(new[] { customer });

As you can see we have to specify the input to our query method using some hairy nested generics. Our test was just trying to return a specific customer when the repository was queried so having go through all the generic mumbo jumbo just felt wrong. This lead us into changing our old Query method

IEnumerable<T> Query<T>(Func<T, bool> criteria);

To use the query object pattern

IQueryResult<T> Query<T>(IQuery<T> query);

With this is place we can encapsulate our queries in separate classes implementing IQuery and there by adhering to the Single Responsibility Principle. As an added bonus our queries can now be reused instead of copy and pasted everywhere.

And the test?

This change in design allowed our test to focus on the right thing. That is:  “given the query GetCustomerByCrmIdOrOrgNr return this active customer”.

Stub<IRepository>(x => x.Query(Arg<GetCustomerByCrmIdOrOrgNr>.Is.Anything))
.Return(new List<Customer>{ activeCustomer }.ToLinqResult());

This did also allow us to refactor the test a bit further:

QueryFor<GetCustomerByCrmIdOrOrgNr>()
.Return(activeCustomer.AsLinqResult());

Ah, that’s more like it!

Final note:

We’re packaging our IRepository implementation into a public project that soon will be published on our github account.