Getting Started
Architecture
NServiceBus
Persistence
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Samples

SQL Transport Native Integration

NuGet Package: NServiceBus.Transport.SqlServer (6.x)
Target Version: NServiceBus 7.x
Standard support for version 7.x of NServiceBus has expired. For more information see our Support Policy.

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"}
    }
);

Samples