How to get service broker dialog handle back when using SqlCommand / C#?

Question

When using SQL Server's Service Broker, you have to BEGIN DIALOG to start a dialog. The BEGIN DIALOG query puts the dialog handle in a variable, when executing the query via SSMS, etc.

I'm trying to use .NET's SqlCommand to execute a BEGIN DIALOG query, though. I'd like that SqlCommand to return the dialog handle, I think. I could then use that handle in subsequent queries.

I see no way to get the BEGIN DIALOG query to instead return the handle as the query result.

How can I execute the BEGIN DIALOG query, via .NET's SqlCommand, in such a way that I can get that dialog handle?

Answer 1

You can use output parameter with your SqlCommand. Example:

using (var cmd = new SqlCommand(@"BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE ... TO SERVICE ... ON CONTRACT ...", connection)) {
    var handleParam = new SqlParameter("dialog_handle", SqlDbType.UniqueIdentifier) {
        Direction = ParameterDirection.Output
    };
    cmd.Parameters.Add(handleParam);
    cmd.ExecuteNonQuery();
    var dialogHandle = (Guid)handleParam.Value;                            
}