SQL Clone
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
Mr or Mrs. 500
Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)

Group: General Forum Members
Points: 536 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
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43252 Visits: 9518
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
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43252 Visits: 9518
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