# SQL Server transport native integration sample


This sample demonstrates how to send messages to an endpoint running on the [SqlServer Transport](/transports/sql/index.md) 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](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-overview), 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:

<!-- snippet: EndpointConfiguration -->

```cs
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)])
    });
```

<!-- endsnippet -->

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

### Serialization

In this sample [Json.NET](/nservicebus/serialization/newtonsoft.md) will be used to serialize the messages but [XML](/nservicebus/serialization/xml.md) 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.

<!-- snippet: MessageContract -->

```cs
public record PlaceOrder : IMessage
{
    public required string OrderId { get; set; }
}
```

<!-- endsnippet -->

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:

<!-- snippet: MessagePayload -->

```cs
var message = @"{
                       $type: 'PlaceOrder, Receiver',
                       OrderId: 'Order from ADO.net sender'
                    }";
```

<!-- endsnippet -->

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](/nservicebus/messaging/message-type-detection.md) for more details.

### Sending the message

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

<!-- snippet: SendingUsingAdoNet -->

```cs
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);
```

<!-- endsnippet -->

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.

<!-- snippet: SendFromTSQL -->

```sql
-- 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"}'))
```

<!-- endsnippet -->

### 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:

<!-- snippet: CreateLegacyTable -->

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

<!-- endsnippet -->

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

<!-- snippet: CreateTrigger -->

```sql
-- 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
```

<!-- endsnippet -->

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.