/Katmai_October2009_Refresh3/DialogPool/Scripts/2 - Dialog pool setup.sql
SQL | 173 lines | 109 code | 21 blank | 43 comment | 0 complexity | 2c1f75fd46ad2f5ee8067e780f461cd0 MD5 | raw file
- --------------------------------------------------------------------
- -- 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