none
Service Broker and .Net RRS feed

  • Question

  • Hi,

    I've been trying to read up about Service Broker in SQL Server 2005 - but all the introductions to it don't seem very useful
     so i though i'd post here and see if i'm correct.

    How does the Broker Service interatc with .net?? Can i get my application to wait for messages from SQL Server (New Database created for instance) and then act upon them??

    Or is not meant for this? I can't seem to find anything on the web with this kind of information...

    Thanks
    Dale
    Wednesday, February 3, 2010 4:31 PM

Answers

  • Yes, you can used Service Broker from .Net, however, there is no .Net library, namespace or class model explicitly for using Service Broker from code.  Consequently, it has to be used the same way as all other SQL facilities were (usually) used before LINQ:  You either execute the T-SQL Service Broker commands yourself directly, or you write stored procedures, and then execute those stored procedures from .Net.

    So, if you want your app code to wait for new messages on an SB Queue, you just execute a RECEIVE command and wait for it to complete.

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

    Please! Remember to Vote all helpful replies as Helpful
    Wednesday, February 3, 2010 10:38 PM

All replies

  • Yes, you can used Service Broker from .Net, however, there is no .Net library, namespace or class model explicitly for using Service Broker from code.  Consequently, it has to be used the same way as all other SQL facilities were (usually) used before LINQ:  You either execute the T-SQL Service Broker commands yourself directly, or you write stored procedures, and then execute those stored procedures from .Net.

    So, if you want your app code to wait for new messages on an SB Queue, you just execute a RECEIVE command and wait for it to complete.

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

    Please! Remember to Vote all helpful replies as Helpful
    Wednesday, February 3, 2010 10:38 PM
  • Right, so from within a .net application i can use the WaitFor Reeceive TSQL query to make the application wait for the next message - thanks, i'll give this a try today.
    Thursday, February 4, 2010 9:17 AM
  • The sample code provided from the excellent books "Pro SQL Service Broker 2005/2008" includes a C# wrapper library for common Service Broker TSQL statements that will make life much easier to interact with the broker from SQLCLR code.

    http://apress.com/book/search?searchterm=service+broker&act=search

    No disrespect to the older broker books from Roger but these books two are an absolute must read for developers interested in the SSB imho.
    Monday, February 8, 2010 4:42 PM
  • Hi Roule,

    I have the 2005 version of this book but not the 2008 one and, AFAIK, he's using the "ServiceBrokerInterface" wrapper library sample from the SQL Server Service Broker samples (http://msftsbprodsamples.codeplex.com/ )

    Cheers,
    Bob Beauchemin
    SQLskills
    Monday, February 8, 2010 9:39 PM
  • Hi Young,

    I have a requirement to read the OutgoingTargetQueue and get the data from there and show that data on one winform. So for that i will write windows application. Do u have any sample or any recourse where i can get that how to read the data from Service Broker queue in .net code. Please suggest or help me out in this.

     

    --Jai

     


    Jai P Sharma
    Tuesday, September 7, 2010 8:18 PM
  • Hi Jai,
     
    There's a SQL Server Engine Sample http://msftsbprodsamples.codeplex.com/ that illustrates how to do this, with a library that encapsulates reading and writing service broker in .NET. In short, you just use the SqlConnection/SqlCommand, etc, as normal and issue the correct T-SQL Service Broker-related commands (like SEND, RECEIVE, etc) using SqlCommand. The sample library encapsulates this in a set of convenience classes.
     
    Cheers, Bob Beauchemin, SQLskills
    Tuesday, September 7, 2010 9:29 PM
  • You may also get some ideas from the sample of external activated application .
    Wednesday, September 8, 2010 5:20 PM
    Moderator