Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How Service Broker and .Net Integration happen?And how Queues are automatically created if record is added/modified in a table changed? Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 8:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 27, 2010 2:33 AM
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

Post #822816
Posted Saturday, November 28, 2009 2:06 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #825845
Posted Saturday, November 28, 2009 2:09 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #825846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse