How Service Broker and .Net Integration happen?And how Queues are automatically created if record is added/modified in a table changed?

  • I am New to SQL Server Broker Service.

    1. How can we create the Message and send it to a Queue, if a record is added or modified in a table.

    Like if we have a MemberMaster table, which have a status column. If the Status is modified, we need to send a Queue.

    2. We need to Integrate Service Broker and .NET. Can anyone help in giving the overview of the same?

    Thanks

    Deepak

  • deepakkn (11/20/2009)


    1. How can we create the Message and send it to a Queue, if a record is added or modified in a table.

    You create a trigger on the table that catches the INSERT or UPDATE and send the new/changed records to the Service attached to your Queue. Here is an example from my presentation "The Top Ten Reasons You Aren't Already Using Service Broker":

    --=====================================

    --08-3Make SEND into a Trigger

    --=====================================

    USE [SB_eDrugs]

    Print ''

    GO

    CREATE TRIGGER [dbo].[trgWebUser_SendTo_PayPusher] ON [dbo].[WebUsers]

    FOR INSERT AS

    BEGIN

    /*

    Asynchronusly Send New Users info to PayPusher database.

    test:

    INSERT INTO WebUsers(LogonName,FirstName,LastName,ZipCode,Password)

    Select 'User01','Rob','Roberts', 90120,'MyNewPwd'

    */

    SET NOCOUNT ON

    -- get the number of records inserted.

    Declare @recs Int

    Select @recs = (Select count(*) from inserted)

    IF @recs > 1

    BEGIN

    RAISERROR('[trgWebUser_SendTo_PayPusher]: cannot handle bulk Inserts!', 17, 1)

    ROLLBACK

    RETURN

    END

    --====== SEND the message

    -- Get the new User's ID

    Declare @user-id as int

    Select @user-id = UserID From inserted

    -- Start the conversation

    Declare @ConvsHdl uniqueidentifier

    BEGIN DIALOG @ConvsHdl

    FROM SERVICE InitiatorReplyService

    TO SERVICE 'PayPusherService'

    ON CONTRACT conWebUser

    -- Send it & End it

    ;SEND ON CONVERSATION @ConvsHdl

    Message Type msgNewUser

    ( @userid )

    END CONVERSATION @ConvsHdl

    --======

    END

    GO

    Of course you will also need to create the queue and any activation procs, and define the message-type, contract and service. You find working examples of all of this in my download kit of this same presentation at my blog site MovingSQL.com, which includes all of the slide and code.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • deepakkn (11/20/2009)


    2. We need to Integrate Service Broker and .NET. Can anyone help in giving the overview of the same?

    It's the same as any other SQL features, the best approach is to just write T-SQL stored procedures that do the actions and functions that you need, and then call them from your .Net code through ADO.Net or Linq.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply