Native integration

Component: SQL Server Transport
NuGet Package NServiceBus.SqlServer (3.x)
Target NServiceBus Version: 6.x

Introduction

This sample demonstrates how to send messages to an endpoint running on the SqlServer Transport directly from the database using T-SQL statements. It also shows how to do that using ADO.NET.

Prerequisites

An instance of SQL Server Express is installed and accessible as .\SqlExpress.

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. Press any key to exit text when the app is ready.
  3. Hit 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 do Edit top X rows.
  12. 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(connectionString);
endpointConfiguration.UseSerialization<JsonSerializer>();

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

Serialization

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 :
    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',
                   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.

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()
        .ConfigureAwait(false);

    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()
            .ConfigureAwait(false);
    }
}

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)
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 where 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

And 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 in order 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.

Related Articles


Last modified