/Kilimanjaro_Trunk/DialogPool/Scripts/2 - Dialog pool setup.sql

--------------------------------------------------------------------

-- Script for dialog pool sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

--------------------------------------------------------------------



--------------------------------------------------------------------------

-- Dialog pool setup.

--------------------------------------------------------------------------



USE SsbDemoDb;

GO



--------------------------------------------------------------------------

-- The dialog pool table.

-- Obtain a conversation handle using from service, to service, and contract.

-- Also indicates age and usage of dialog for auditing purposes.

--------------------------------------------------------------------------



IF EXISTS (SELECT name FROM sys.tables WHERE name = 'DialogPool')

      DROP TABLE DialogPool;

GO



CREATE TABLE DialogPool (

      FromService SYSNAME NOT NULL,

      ToService SYSNAME NOT NULL,

      OnContract SYSNAME NOT NULL,

      Handle UNIQUEIDENTIFIER NOT NULL,

      OwnerSPID INT NOT NULL,

      CreationTime DATETIME NOT NULL,

      SendCount BIGINT NOT NULL,

      UNIQUE (Handle));

GO



--------------------------------------------------------------------------

-- Get dialog procedure.

-- Reuse a free dialog in the pool or create a new one in case

-- no free dialogs exist.

-- Input is from service, to service, and contract.

-- Output is dialog handle and count of message previously sent on dialog.

--------------------------------------------------------------------------



IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_get_dialog')

      DROP PROC usp_get_dialog;

GO



CREATE PROCEDURE usp_get_dialog (

      @fromService SYSNAME,

      @toService SYSNAME,

      @onContract SYSNAME,

      @dialogHandle UNIQUEIDENTIFIER OUTPUT,

      @sendCount BIGINT OUTPUT)

AS

BEGIN

      SET NOCOUNT ON;



      DECLARE @dialog TABLE

      (

          FromService SYSNAME NOT NULL,

          ToService SYSNAME NOT NULL,

          OnContract SYSNAME NOT NULL,

          Handle UNIQUEIDENTIFIER NOT NULL,

          OwnerSPID INT NOT NULL,

          CreationTime DATETIME NOT NULL,

          SendCount BIGINT NOT NULL

      );



      -- Try to claim an unused dialog in DialogPool

      -- READPAST option avoids blocking on locked dialogs.

      BEGIN TRANSACTION;

      DELETE @dialog;

      UPDATE TOP(1) DialogPool WITH(READPAST)

             SET OwnerSPID = @@SPID

             OUTPUT INSERTED.* INTO @dialog

             WHERE FromService = @fromService

                   AND ToService = @toService

                   AND OnContract = @OnContract

                   AND OwnerSPID = -1;

      IF @@ROWCOUNT > 0

      BEGIN

           SET @dialogHandle = (SELECT Handle FROM @dialog);

           SET @sendCount = (SELECT SendCount FROM @dialog);

      END

      ELSE

      BEGIN

           -- No free dialogs: need to create a new one

           BEGIN DIALOG CONVERSATION @dialogHandle

                 FROM SERVICE @fromService

                 TO SERVICE @toService

                 ON CONTRACT @onContract

                 WITH ENCRYPTION = OFF;



           INSERT INTO DialogPool

                  (FromService, ToService, OnContract, Handle, OwnerSPID,

                      CreationTime, SendCount)

                  VALUES

                  (@fromService, @toService, @onContract, @dialogHandle, @@SPID,

                      GETDATE(), 0);

          SET @sendCount = 0;

      END

      COMMIT;

END;

GO



--------------------------------------------------------------------------

-- Free dialog procedure.

-- Return the dialog to the pool.

-- Inputs are dialog handle and updated send count.

--------------------------------------------------------------------------



IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_free_dialog')

      DROP PROC usp_free_dialog;

GO



CREATE PROCEDURE usp_free_dialog (

      @dialogHandle UNIQUEIDENTIFIER,

      @sendCount BIGINT)

AS

BEGIN

      SET NOCOUNT ON;



      DECLARE @rowcount INT;

      DECLARE @string VARCHAR(50);



      BEGIN TRANSACTION;



      -- Release dialog by setting OwnerSPID to -1.

      UPDATE [DialogPool] SET OwnerSPID = -1, SendCount = @sendCount WHERE Handle = @dialogHandle;

      SELECT @rowcount = @@ROWCOUNT;

      IF @rowcount = 0

      BEGIN

           SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));

           RAISERROR('usp_free_dialog: dialog %s not found in dialog pool', 16, 1, @string) WITH LOG;

      END

      ELSE IF @rowcount > 1

      BEGIN

           SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));

           RAISERROR('usp_free_dialog: duplicate dialog %s found in dialog pool', 16, 1, @string) WITH LOG;

      END



      COMMIT;

END;

GO



--------------------------------------------------------------------------

-- Delete dialog procedure.

-- Delete the dialog from the pool. This does not end the dialog.

-- Input is dialog handle.

--------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_delete_dialog')

      DROP PROC usp_delete_dialog;

GO



CREATE PROCEDURE usp_delete_dialog (

      @dialogHandle UNIQUEIDENTIFIER)

AS

BEGIN

      SET NOCOUNT ON;



      BEGIN TRANSACTION;

      DELETE [DialogPool] WHERE Handle = @dialogHandle;

      COMMIT;

END;

GO