HOW TO: Unsubscribe From A Query Notification

After having problems with SqlDependency logging an infinite number of errors I've rewritten my code to use SqlNotificationRequest instead. Now I have a different kind of cleanup problem and am looking for help.

My code registers to be notified when table data is changed. When the event triggers, it re-registers. It works fine. The problem comes when my subscribing program ends: it orphans the last subscription. When the program re-starts it subscribes as before, but now there are now two subscriptions (or three, or however many times my program is shut down). As an Admin I can run KILL QUERY NOTIFICATION SUBSCRIPTION ALL; I can even hard code this into my app as it is the only one (for now) using this instance of SS. But there must be a better way.

When I subscribe (.NET) how do I get the subscription id? Then I could just KILL that single id when my code shuts down. Specifically what code should I include in StopListener()? Thank you.



public void StartListener()

{


// starts a new thread whose sole purpose is to sleep until waked

Debug.WriteLine( "RequestNotification: StartListener" );

if( null == _listener )

{

_listener = new Thread( Listen );

_listener.Name = _threadName;

_listener.IsBackground = true; // essential!

_listener.Start();

}

}



public void StopListener()

{


Debug.WriteLine( "RequestNotification: StopListener" );

// not necessary, but good practice

_listener.Abort();

_listener = null;

Debug.WriteLine( "RequestNotification: Listener aborted..." );

}



private void Listen()

{


// infinite loop on this thread: keep listening even after

// receiving notification

while( true )

{


// connects to SqlServer queue and sleeps until a notification msg arrives

using( SqlConnection conn = new SqlConnection( _connect ) )

{


conn.Open();

Debug.WriteLine( "RequestNotification: Listen connection opened..." );

using( SqlCommand cmd = conn.CreateCommand() )

{


cmd.CommandText = "WAITFOR ( RECEIVE * FROM " + _queue + ");";

cmd.CommandTimeout = 0; // _notificationTimeout + 100;

try

{


// the thread is blocked until the waitfor event occurs.

SqlDataReader reader = cmd.ExecuteReader();

// the waitfor has triggered

while( reader.Read() )

{

// messageText = System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString();

}

// and now we need to hand things back to the user's passed

// delegate, ensure we're back on the UI thread as well

//object[] args = { this, EventArgs.Empty };

Debug.WriteLine( "RequestNotification: Notification received." );

SqlNotificationEventArgs e = new SqlNotificationEventArgs( SqlNotificationType.Change, SqlNotificationInfo.AlreadyChanged, SqlNotificationSource.Database );

_delegate( this, e );

}

catch( SqlException )

{


// if the queue name doesn't match one in the db an exception will be thrown

throw;

}
}

}

}

}