SqlNotificationEvent notify multiple times for even single insert in database

Question

SqlNotificationEvent notify multiple times for even single insert in database.

Even for single insert/update/delete in table the event notify for several time with e.Type == SqlNotificationType.Change, can anyone help why this happens. It should notify only once for single change in table.

static class Program
{
    private static string mStarterConnectionString = "Data Source=localhost;Database=SqlDependencyTest;Persist Security Info=false;Integrated Security=false;User Id=startUser;Password=startUser";
    private static string mSubscriberConnectionString = "Data Source=localhost;Database=SqlDependencyTest;Persist Security Info=false;Integrated Security=false;User Id=subscribeUser;Password=subscribeUser";
    public const string CACHE_KEY = "APPCACHEKEY";

    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);

        // Starting the listener infrastructure...
        SqlDependency.Start(mStarterConnectionString);

        // Registering for changes... 
        RegisterForChanges();

        Application.Run(new SqlCache());

        // Quitting...
        SqlDependency.Stop(mStarterConnectionString);
    }

    public static DataTable RegisterForChanges()
    {
        DataTable dataTable = new DataTable();

        // Connecting to the database using our subscriber connection string and
        // waiting for changes...
        SqlConnection oConnection = new SqlConnection(mSubscriberConnectionString);
        oConnection.Open();
        try
        {
            using (SqlCommand oCommand = new SqlCommand("dbo.GetUsers", oConnection))
            //using (SqlCommand oCommand = new SqlCommand("SELECT ID, Name FROM dbo.Users", oConnection))
            {
                oCommand.CommandType = CommandType.StoredProcedure;
                SqlDependency oDependency = new SqlDependency(oCommand);
                oDependency.OnChange += new OnChangeEventHandler(OnNotificationChange);
                using (SqlDataAdapter adapter = new SqlDataAdapter(oCommand))
                    adapter.Fill(dataTable);
            }

            AppMain.Cache.Insert(CACHE_KEY, dataTable, null, Cache.NoAbsoluteExpiration, TimeSpan.FromSeconds(60));
        }
        finally
        {
            oConnection.Close();
        }
        return dataTable;
    }

    public static void OnNotificationChange(object caller, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
            RegisterForChanges();
    }

Damith · Answer 1 · 2012-04-18T09:29:30.110

0

You can use SqlNotificationEventArgs object's SqlNotificationInfo object and check whether it is Insert, Update or Delete and ignore the rest.

public static void OnNotificationChange(object caller, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change && (e.Info == SqlNotificationInfo.Insert || e.Info == SqlNotificationInfo.Delete || e.Info == SqlNotificationInfo.Update))
    {
        SqlDependency dependency =(SqlDependency)sender;
        dependency.OnChange -= OnNotificationChange;
        RegisterForChanges();
    }
}

edited Apr 18 '12 at 09:29

answered Apr 18 '12 at 07:38

Damith

62,401
13
102
153

public static void OnNotificationChange(object caller, SqlNotificationEventArgs e) { if (e.Type == SqlNotificationType.Change && (e.Info == SqlNotificationInfo.Insert || e.Info == SqlNotificationInfo.Delete || e.Info == SqlNotificationInfo.Update)) RegisterForChanges(); } I have done above changes, actually e.Type shows "Change" and e.Info shows "Insert", for several times when insert one row in table. Please me if any one knows this problem. – Shivu Apr 18 '12 at 08:51
@Shivu Answer Updated. Please check – Damith Apr 18 '12 at 09:36

score -1 · Answer 2 · answered Feb 28 '14 at 16:11

I had the same issue as described in the question. I was able to pinpoint that the issue resulted from creating multiple SqlCommand instances (similar to how you have using (SqlCommand oCommand = new SqlCommand("dbo.GetUsers", oConnection)) inside RegisterForChanges().

I changed the code to declare instance references to SqlCommand and SqlConnection objects, and instantiated only new SqlDependency instances in the RegisterForChanges() method.

void RegisterForChanges()
{
  _sqlCommand.Notification = null;
  _sqlConnection.Open();
  SqlDependency dependency = new SqlDependency(_sqlCommand);
  dependency.OnChange += dependency_OnChange;
  _sqlCommand.ExecuteNonQuery(); // or ExecuteReader and parse the results, etc
  _sqlConnection.Close();
}

SqlNotificationEvent notify multiple times for even single insert in database

2 Answers2