Sql Notification Supported Isolation Levels for Transactions

Question

I am running multiple inserts using transactions. I am using the SqlDependency class to let the client machine know when the server has been updated.

The problem I am having is that whenever I insert using a transaction, no matter what isolation level I set for the transaction, the SqlNotificationEventArgs returns e.Info as Isolation which indicates that I have the wrong isolation level set for that transactions (I think). When I insert without using a transaction, everything runs smoothly.

My questions is, what are the supported Isolation levels, if any, for transactions when using Sql Notification?

Below is some of the code I am using for the notification:

void DataChanged(object sender, SqlNotificationEventArgs e) {
    var i = (ISynchronizeInvoke)_form;
    if (i.InvokeRequired) {
        var tempDelegate = new OnChangeEventHandler(DataChanged);
        object[] args = { sender, e };
        i.BeginInvoke(tempDelegate, args);
    } else {
        var dependency = (SqlDependency)sender;
        if (e.Type == SqlNotificationType.Change) {
            dependency.OnChange -= DataChanged;
            GetData(dependency);
        }
    }
}

And for the transaction:

public void ExecuteNonQueryData(List<string> commandTexts) {
    SqlConnection connection = null;
    var command = new SqlCommand();
    SqlTransaction transaction = null;
    try {
        connection = new SqlConnection(GetConnectionString());
        connection.Open();
        transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
        foreach (var commandText in commandTexts) {
            try {
                command.Connection = connection;
                command.CommandText = commandText;
                command.Transaction = transaction;
                command.ExecuteNonQuery();
            } catch (Exception ex) {
                Console.WriteLine(ex.Message);
            }
        }
        transaction.Commit();
    } catch (Exception ex) {
        Console.WriteLine(ex.Message);
    } finally {
        command.Dispose();
        if (transaction != null) transaction.Dispose();
        if (connection != null) {
            connection.Close();
            connection.Dispose();
        }
    }
    commandTexts.Clear();
}

Edit: I was committing the transaction in the wrong place.

Answer 1

Apparently Query Notification does not support transactions. Removing the transaction code fixed this problem.

According to Microsoft:

Transact-SQL does not provide a way to subscribe to notifications. The CLR data access classes hosted within SQL Server do not support query notifications.

This quote was found at http://msdn.microsoft.com/en-us/library/ms188669.aspx, which describes how Query Notifications work and their requirements.