Native integration

Component: SQL Server Transport | Nuget: NServiceBus.SqlServer (Version: 3.x)
Target NServiceBus Version: 6.x
  1. Ensure a SQL Server Express is running and accessible as .\SQLEXPRESS.
  2. Create a database samples
  3. Start the Receiver project.
  4. In the Receiver's console notice Press enter to send a message. Press any key to exit text when the app is ready.
  5. Hit enter key.
  6. A message will be sent using ADO.NET and be received by the app.
  7. Open SQL Server Management Studio and go to the samples database.
  8. Open the Scripts.sql included in the sample.
  9. Run the SendFromTSQL statement.
  10. Notice how the app shows that a new message has been processed.
  11. Create the Orders table using the CreateLegacyTable statement.
  12. Create the insert trigger using the CreateTrigger statement.
  13. Right click the table just created and do Edit top X rows.
  14. Notice that a message is received by the app for each "order" created.

Code walk-through

The first thing when doing native integration with the SQL Server transport is figure out where to insert the "message". The database and server names can easily be found by looking at the connection string and the table name is, by default, the same as the endpoint's name. The endpoint configuration is as follows:

var endpointConfiguration = new EndpointConfiguration("Samples.SqlServer.NativeIntegration");
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.ConnectionString(@"Data Source=.\SqlExpress;Database=samples;Integrated Security=True");

The table would be Samples.SqlServer.NativeIntegration in the database samples on server .\SQLEXPRESS (localhost).


In this sample JSON will be used to serialize the messages but XML would have worked equally well. To configure the endpoint call .UseSerialization<JsonSerializer>() as shown above.

Now the endpoint can understand JSON payloads a message contract can be defined. In NServiceBus messages are C# classes.

public class PlaceOrder :
    public string OrderId { get; set; }

The final step is to tell the serializer what C# class the JSON payload belongs to. This is done using the Json.NET $type attribute. The message body will then look as follows:

var message = @"{
                   $type: 'PlaceOrder',
                   OrderId: 'Order from sender'

With this in place the endpoint can now parse the incoming JSON payload to a strongly typed message and invoke the correct message handlers.

Sending the message

Sending a message to the endpoint using ADO.NET is done via:

var connectionString = @"Data Source=.\SqlExpress;Database=samples;Integrated Security=True";
var insertSql = "INSERT INTO [Samples.SqlServer.NativeIntegration] (Id, Recoverable, Headers, Body) VALUES (@Id, @Recoverable, @Headers, @Body)";
using (var connection = new SqlConnection(connectionString))
    await connection.OpenAsync()

    using (var command = new SqlCommand(insertSql, connection))
        command.CommandType = CommandType.Text;

        var parameters = command.Parameters;
        parameters.Add("Id", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
        parameters.Add("Headers", SqlDbType.VarChar).Value = "";
        parameters.Add("Body", SqlDbType.VarBinary).Value = Encoding.UTF8.GetBytes(message);
        parameters.Add("Recoverable", SqlDbType.Bit).Value = true;

        await command.ExecuteNonQueryAsync()

Armed with this it will be possible to send messages from any app in the organization that supports ADO.NET.

Sending from within the database

Sometimes it is necessary to integrate with old legacy apps where performing sends straight from within the database itself might be a better approach. Just execute the following SQL statement and notice how the message is consumed by the NServiceBus endpoint.

-- TSql that can be pasted into Sql Server Query analyzer to send straight from the DB
INSERT INTO [Samples.SqlServer.NativeIntegration] (Id , Recoverable, Headers, Body)
    CONVERT(varbinary(255), '{ $type: "PlaceOrder",OrderId: "Order from TSQL sender"}'))

Using triggers to emit messages

Sometimes legacy systems are not allowed to be modified. It is in this scenario where triggers are useful.

Create a fictive Orders table using

-- Create a "legacy" Orders table
  Id int IDENTITY(1,1) NOT NULL,
  OrderValue money NOT NULL

And create an on inserted trigger that will send a LegacyOrderDetected message for each new order that is added to the table. Here's the trigger:

-- Create a trigger to push a message out for each new order
CREATE TRIGGER OrderAcceptedTrigger
  ON Orders

  INSERT INTO [Samples.SqlServer.NativeIntegration] (Id, Recoverable, Headers ,Body)
  'true' as Recoverable,
  '' as Headers,
  CONVERT(varbinary(255), '{ $type: "LegacyOrderDetected",OrderId: "Hello from legacy Order ' + CONVERT(VARCHAR(255),i.Id) + '"}') as Body
  FROM inserted i

Notice how a unique message ID is generated by hashing the identity column. NServiceBus requires each message to have a unique ID in order to safely perform retries.

Add a few orders to the table and notice how the app receives the messages. In a real life scenario the trigger would likely do a Publish to push an OrderAccepted event out on the queue.

Related Articles

  • MSMQ Transport
    MSMQ is the primary durable communications technology for Microsoft but does not dynamically detect network interfaces.
  • NHibernate Persistence
    NHibernate-based persistence for NServiceBus.
  • SQL Server Transport
    High-level description of NServiceBus SQL Server Transport.

Last modified