How can I check in c# if Broker Service is enabled in SQL?

Question

I want to check if Broker Service is running using code and depending on the status, start sqldependency or not. How can I do that?

Answer 1

You can do a simple query:

SELECT is_broker_enabled FROM sys.databases WHERE Name = 'mydatabasename'

Alternatively you can just start the SqlDependency and trap the error you get if it hasn't been enabled, but the first method is simpler and better:

  try {
      SqlDependency.Start();
  } catch (InvalidOperationException ex) {
      // If broker hasn't been enabled, you'll get the following exception:
      //
      // The SQL Server Service Broker for the current database is not enabled, and
      // as a result query notifications are not supported.  Please enable the Service
      // Broker for this database if you wish to use notifications.
  }