SQL Server transport native integration sample

This sample demonstrates how to send messages to an endpoint running on the SqlServer Transport directly from the database using T-SQL statements. This enables integrating event-driven messaging endpoints within a legacy SQL application where most application logic is embedded within stored procedures, even allowing messages to be sent as a result of database triggers.

The sample also shows how to natively send messages using ADO.NET, such as from a utility application or batch job.

Prerequisites

Ensure an instance of SQL Server (Version 2016 or above for custom saga finders sample, or Version 2012 or above for other samples) is installed and accessible on localhost and port 1433. A Docker image can be used to accomplish this by running docker run --name mssql -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:latest in a terminal.

Alternatively, change the connection string to point to different SQL Server instance.

At startup each endpoint will create its required SQL assets including databases, tables, and schemas.

The database created by this sample is NsbSamplesSqlNativeIntegration.

Running the sample

1. Start the Receiver project.
2. In the Receiver's console notice Press [Enter] to send a message, or [CTRL+C] to exit. when the app is ready.
3. Hit the enter key.
4. A message will be sent using ADO.NET and be received by the app.
5. Open SQL Server Management Studio and go to the NsbSamplesSqlNativeIntegration database.
6. Open the Scripts.sql included in the sample.
7. Run the SendFromTSQL statement.
8. Notice how the app shows that a new message has been processed.
9. Create the Orders table using the CreateLegacyTable statement.
10. Create the insert trigger using the CreateTrigger statement.
11. Right-click the table just created and Edit top X rows.
12. Manually insert a record into the Orders table by adding a new OrderValue entry (this simulates an entry from a legacy system).
13. 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 to 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");
endpointConfiguration.UseTransport(new SqlServerTransport(connectionString)
{
    TransportTransactionMode = TransportTransactionMode.SendsAtomicWithReceive
});
endpointConfiguration.UseSerialization<NewtonsoftJsonSerializer>()
    .Settings(new JsonSerializerSettings
    {
        TypeNameHandling = TypeNameHandling.Auto,
        SerializationBinder = new SkipAssemblyNameForMessageTypesBinder([typeof(PlaceOrder), typeof(LegacyOrderDetected)])
    });

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

Serialization

In this sample Json.NET 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 :
    IMessage
{
    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, Receiver',
                       OrderId: 'Order from ADO.net 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.

See the message type detection documentation for more details.

Sending the message

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

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(ct);

    using (var command = new SqlCommand(insertSql, connection))
    {
        var parameters = command.Parameters;
        parameters.Add("Id", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
        parameters.Add("Headers", SqlDbType.NVarChar).Value = "";
        var body = Encoding.UTF8.GetBytes(message);
        parameters.Add("Body", SqlDbType.VarBinary).Value = body;
        parameters.Add("Recoverable", SqlDbType.Bit).Value = true;

        await command.ExecuteNonQueryAsync(ct);
    }
}

Armed with this, it is 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. 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)
values (convert(uniqueidentifier, hashbytes('MD5','MyUniqueId')),
    'true',
    '',
    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 that triggers are useful.

Create a fictive Orders table using:

-- Create a "legacy" Orders table
create table Orders(
  Id int identity(1,1) not null,
  OrderValue money not null
)
go

Create an on inserted trigger that will send a LegacyOrderDetected message for each new order that is added to the table:

-- Create a trigger to push a message out for each new order
create trigger OrderAcceptedTrigger
  ON Orders
  after insert
as
begin
  set nocount on;

  insert into [Samples.SqlServer.NativeIntegration]
			  (Id, Recoverable, Headers, Body)
  select convert(uniqueidentifier, hashbytes('MD5',convert(varchar(255),i.Id))) as Id,
  '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
end
go

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

Add a few orders to the table and 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.