SQL Transport Native Integration

This document describes how to consume messages from and send messages to non-NServiceBus endpoints via SQL Server in integration scenarios.

Native Send

The native send helper methods

A send involves the following actions:

Create and serialize headers.
Write a message body directly to SQL Server Transport.

In C#

public static void SendMessage(string connectionString, string queue, string messageBody, Dictionary<string, string> headers)
{
    var insertSql = $@"
    insert into [{queue}] (
        Id,
        Recoverable,
        Headers,
        Body)
    values (
        @Id,
        @Recoverable,
        @Headers,
        @Body)";
    var serializedHeaders = Newtonsoft.Json.JsonConvert.SerializeObject(headers);
    var bytes = Encoding.UTF8.GetBytes(messageBody);
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(insertSql, connection))
        {
            var parameters = command.Parameters;
            parameters.Add("Id", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
            parameters.Add("Headers", SqlDbType.VarChar).Value = serializedHeaders;
            parameters.Add("Body", SqlDbType.VarBinary).Value = bytes;
            parameters.Add("Recoverable", SqlDbType.Bit).Value = true;
            command.ExecuteNonQuery();
        }
    }
}

In this example, the value MyNamespace.MyMessage represents the .NET type of the message. See the headers documentation for more information on the EnclosedMessageTypes header.

In PowerShell

Set-StrictMode -Version 2.0

Add-Type -AssemblyName System.Data

Function SendMessage
{
    param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string] $ConnectionString,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string] $Queue,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string] $MessageBody,

        [Parameter(Mandatory=$true)]
        [ValidateNotNull()]
        [HashTable] $Headers
    )

    $UTF8 = [System.Text.Encoding]::UTF8

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
    $sqlConnection.Open()

    $sqlCommand = $sqlConnection.CreateCommand()
    $sqlCommand.CommandText =
        "insert into [$Queue] (Id, Recoverable, Headers, Body) values (@Id, @Recoverable, @Headers, @Body)"
    $parameters = $sqlCommand.Parameters
    $parameters.Add("Id", [System.Data.SqlDbType]::UniqueIdentifier).Value = [System.Guid]::NewGuid()
    $serializedHeaders = ConvertTo-Json $Headers
    $parameters.Add("Headers", [System.Data.SqlDbType]::NVarChar).Value = $serializedHeaders
    $parameters.Add("Body", [System.Data.SqlDbType]::VarBinary).Value = $UTF8.GetBytes($MessageBody)
    $parameters.Add("Recoverable", [System.Data.SqlDbType]::Bit).Value = 1
    $sqlCommand.ExecuteNonQuery()

    $sqlConnection.Close()
}

Using the native send helper methods

SendMessage(
    connectionString: @"Data Source=.\SqlExpress;Database=samples;Integrated Security=True",
    queue: "Samples.SqlServer.NativeIntegration",
    messageBody: "{Property:'PropertyValue'}",
    headers: new Dictionary<string, string>
    {
        {"NServiceBus.EnclosedMessageTypes", "MessageTypeToSend"}
    }
);