Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Service Broker and .Net Integration happen?And how Queues are automatically created if record...


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

Author
Message
deepakkn
deepakkn
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 238
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
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-3   Make 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 @UserID as int
   Select @UserID = 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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search