Wikipedia

Benutzer:MovGP0/Service Broker/.NET Integration

< Benutzer:MovGP0
Programmierung
T-SQL / SQL Server
T-SQL Fundamantals

Select • Programming • Pitfalls • Query from Multiple Tables • Groups and Summaries • Advanced Select • Aggregation and Windowing • Insert, Update, Delete • Strings • Date and Time • Numbers • Transactions, Locking, Blocking, Deadlock • Tables • Views • Large Tables and Databases • Indexes • Stored Procedures • User-Defined Functions and Types • Triggers • Error Handling • Query Performance and Tuning • Hints • Index Tuning and Statistics • XML • Files, Filegroups, Integrity • Backup • Recovery • Principals and Users • Securables, Permissions, Auditing • Objects and Dependencies

SQL Server
Performance Tuning

Memory Performance Analysis • Disk Performance Analysis • CPU Performance Analysis • Baseline Creation • Query Performance Metrics • Query Performance Analysis • Index Architecture and Behavior • Index Analysis • Database Engine Tuning Advisor • Key Lookups • Statistics, Data Distributuon, and Cardinality • Index Fragmentation • Execution Plan Generation • Execution Plan Cache Behavior • Parameter Sniffing • Query Recompilation • Query Design Analysis • Reduce Query Resource Use • Blocking and Blocked Processes • Causes and Solutions for Deadlocks • Row-By-Row Processing • Memory-Optimized OLTP Tables and Procedures • Database Performance Testing • Database Workload Optimization • SQL Server Optimization Checklist

Service Broker

Message • Contract • Queue • Service • .NET Integration • Route • Security

 

Datatypes • Unit Testing • Dependency Injection • Partitioning •

Service Broker .NET Integration

Bearbeiten

Create Endpoint

Bearbeiten 
CREATE MESSAGE TYPE [https://company.com/messages/customer/created]
  VALIDATION = WELL_FORMED_XML

CREATE CONTRACT [https://company.com/contracts/customer]
(
    [https://company.com/messages/customer/created] SENT BY INITIATOR
)

CREATE QUEUE [dbo].[CustomerQueue]

CREATE SERVICE [https://company.com/services/publisher]
ON QUEUE [dbo].[CustomerQueue]
(
    ADD CONTRACT [https://company.com/contracts/customer]
)

CREATE SERVICE [https://company.com/services/subscriber]
ON QUEUE [dbo].[CustomerQueue]
(
    ADD CONTRACT [https://company.com/contracts/customer]
)

Send Message

Bearbeiten 
CREATE PROCEDURE [dbo].[Send_Customer_Inserted]
(
    @id uniqueidentifier,
    @firstname nvarchar(255),
    @lastname nvarchar(255)
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Convert data to XML
    DECLARE @tempCustomers TABLE
    (
        Id uniqueidentifier,
        FirstName nvarchar(255),
        LastName nvarchar(255)
    );

    INSERT INTO @tempCustomers (ID, FirstName, LastName)
    VALUES ( @id, @firstname, @lastName );
    
    DECLARE @message XML =
    (
        SELECT
            ID AS "@id", -- @prefix renders property as XML attribute rather than XML element
            FirstName AS "@firstName",
            LastName AS "@lastName"
        FROM @tempCustomers
        FOR XML PATH('customer'), ROOT('customers')
    );

    -- Create Service Broker Dialog
    DECLARE @handle uniqueidentifier;

    BEGIN TRANSACTION
      BEGIN DIALOG @handle
      FROM SERVICE [https://company.com/services/publisher]
      TO SERVICE 'https://company.com/services/subscriber'
      ON CONTRACT [https://company.com/contracts/customer]
      WITH ENCRYPTION = OFF;

      -- Send Message
      SEND ON CONVERSATION @handle MESSAGE TYPE [https://company.com/messages/customer/created]
      (
          @message
      );

    COMMIT TRANSACTION;
END

Usage: 

DECLARE @id UNIQUEIDENTIFIER = 'bfe76cc2-19c0-4e54-9f8a-abfb3ca6cd94';
DECLARE @firstName NVARCHAR(255) = N'Max';
DECLARE @lastName NVARCHAR(255) = N'Müller';

EXEC [dbo].[Send_Customer_Inserted] @id @firstName @lastName;

Send Message as Bulk

Bearbeiten

Create a new data type: 

CREATE TYPE PersonType AS TABLE
(
    ID UNIQUEIDENTIFIER,
    FirstName NVARCHAR(255).
    LastName NVARCHAR(255)
)
GO

Stored Procedure: 

CREATE PROCEDURE [dbo].[Send_Customer_Inserted2]
(
    @customers PersonType
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Convert data to XML
    DECLARE @message XML =
    (
        SELECT
            ID AS "@id", -- @prefix renders property as XML attribute rather than XML element
            FirstName AS "@firstName",
            LastName AS "@lastName"
        FROM @customers
        FOR XML PATH('customer'), ROOT('customers')
    );

    -- Create Service Broker Dialog
    DECLARE @handle uniqueidentifier;

    BEGIN TRANSACTION
      BEGIN DIALOG @handle
      FROM SERVICE [https://company.com/services/publisher]
      TO SERVICE 'https://company.com/services/subscriber'
      ON CONTRACT [https://company.com/contracts/customer]
      WITH ENCRYPTION = OFF;

      -- Send Message
      SEND ON CONVERSATION @handle MESSAGE TYPE [https://company.com/messages/customer/created]
      (
          @message
      );

    COMMIT TRANSACTION;
END

Usage: 

DECLARE @customers PersonType;

INSERT INTO @customers (ID, FirstName, LastName)
VALUES
    ('933b1ae9-71c7-465c-b63f-662e8af39d77', N'Anton', N'Mustermann')
    ('a4785dfb-ebde-4fa0-8fcf-38ee7d6c9428', N'Berta', N'Muster')
    ('f3196242-614f-4de8-984e-10a0be06f330', N'Charly', N'Müller');

EXECUTE [dbo].[Send_Customer_Inserted2] @customers;

Trigger Example

Bearbeiten 
CREATE TRIGGER [dbo].[TR_Customer_Update]
ON [Customer]
AFTER INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
    SET NOCOUNT ON;
    
	DECLARE @customers PersonType;
	
        INSERT INTO @customers (ID, FirstName, LastName)
	SELECT i.Id, i.FirstName, i.LastName
	FROM [inserted] AS i
	
	INSERT INTO @customers (ID, FirstName, LastName)
	SELECT i.Id, i.FirstName, i.LastName
	FROM [updated] AS i
	
	EXEC [dbo].[Send_Customer_Inserted2] @customers;
END

Receive Message

Bearbeiten 
CREATE PROCEDURE [dbo].[Receive_Customer_Inserted]
(
  @message XML OUT
)
AS
BEGIN
     DECLARE @conversation_handle UNIQUEIDENTIFIER;
     DECLARE @message_body XML;
     DECLARE @message_type_name VARCHAR(512);

    BEGIN TRANSACTION;
      RECEIVE TOP (1)
        @conversation_handle = [conversation_handle],
        @message_body = [message_body],
        @message_type_name = [message_type_name]
      FROM [dbo].[CustomerQueue];

      SET @message = CONVERT(XML, @message_body);
    COMMIT TRANSACTION;
END

Get Data in .NET

Bearbeiten 
public sealed class Customer
{
   public Guid Id { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }

   public static explicit Customer(XElement element)
   {
       return new Customer
       {
           Id = (Guid)element.Attribute("id"),
           FirstName = (string)element.Attribute("firstName"),
           LastName = (string)element.Attribute("lastName")
       }
   }
}

using Dapper;
using System.Xml.Linq;

namespace MyNamespace
{
    public static class MyDbContextExtensions
    {
        public static async Task<List<Customer>> GetInsertedCustomers(this MyDbContext context)
        {
            var connection = context.Database.GetDbConnection();

            var query = @"DECLARE @message xml;" +
                        @"EXEC [dbo].[Receive_Customer_Inserted] @message OUT;" +
                        @"SELECT @message;";

            var result = await connection.QuerySingleOrDefaultAsync<XElement>(query, commandType: CommandType.Text);

            if (result == null)
                return new List<Customer>();

            var customersXElement = XElement.Parse(result);
            var customers = customersXElement
                  .Elements()
                  .Select(x => (Customer)x)
                  .ToList();

            return customers;
        }
    }
}

References

Bearbeiten
Abgerufen von „https://de.wikipedia.org/w/index.php?title=Benutzer:MovGP0/Service_Broker/.NET_Integration&oldid=211182875“