SQLDependency thread

Question

i am in the process implementing SQLdepenency i would like to know in case of Dependency Handler exeuctues will it spun a different thred from main Process ? What will happen when the event handler triggers? Do i need to worry about any multithreds issues?

public void CreateSqlDependency()
{
    try
    {
        using (SqlConnection connection = (SqlConnection)DBFactory.GetDBFactoryConnection(Constants.SQL_PROVIDER_NAME))
        {
            SqlCommand command = (SqlCommand)DBFactory.GetCommand(Constants.SQL_PROVIDER_NAME);
            command.CommandText = watchQuery;
            command.CommandType = CommandType.Text;
            SqlDependency dependency = new SqlDependency(command);
            //Create the callback object 
            dependency.OnChange += new OnChangeEventHandler(this.QueueChangeNotificationHandler); 
            SqlDependency.Start(connectionString);

            DataTable dataTable = DBFactory.ExecuteSPReDT(command);
        }

    }
    catch (SqlException sqlExp)
    {
        throw sqlExp;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

public void QueueChangeNotificationHandler(object caller, SqlNotificationEventArgs e)
{
    if(e.Info == SqlNotificationInfo.Insert)
        Fire();
}

Answer 1

SqlDependency documentation on MSDN mention about possibility that the OnChange event may be generated on a different thread from the thread that initiated command execution.

You should read Detecting Changes with SqlDependency (ADO.NET) article from MSDN which explain similar scenario.

Answer 2

It will spawn a new worker thread to wait for the dependency notifications--but that's what you want (otherwise your main program loop would be held up waiting for something that may never happen!).

The example code at this page shows to how avoid issues where the worker thread that gets the dependency/query notifications doesn't have the right to update the UI; their method passes the task to the UI thread so it will succeed (see step #12).