ASp.NET app not receiving SQLDependancy notification

M

MattC

I have the following code.

I get an initial Subscribe event but then I get no notification in the app.

I call SqlDependency.Start(DatabaseProviderHelper.ConnectionString); on the
web app application_Start event

I started my web app and after receiving Starting the dependancy I ran a
query against the table in the Management Studio to update the table. I saw
that AspNet_SqlCacheTablesForChangeNotification's changeId for that table
was incremented. Also the Service Broker information is set up. Yet I
dont' receive any notification when the table is changed.

I've also run the following hot fix.
http://support.microsoft.com/kb/913364/EN-US/

Any thoughts??

public static void SetUserListDependancy()
{
SQLServerDatabaseAccessor dba = null;
IDataReader dr = null;

activeUsers.Clear(); //hashtable

try
{
dba =
(SQLServerDatabaseAccessor)DatabaseProviderHelper.GetDatabaseAccessor();
dba.SetStoredProc("procWebAppUsersGetActiveUsers");

dba.Command.Notification = null;
SqlDependency dependancy = new SqlDependency(dba.Command);
dependancy.OnChange += new OnChangeEventHandler(OnChange);
dba.OpenConnection();
dr = dba.GetDataReader();

while (dr.Read())
{
activeUsers.Add(dr["WindowsUserName"], dr["UserID"]);
}
}
catch (Exception e)
{
//do something here
}
finally
{
if (dr != null)
dr.Close();
if (dba != null)
dba.CloseConnection();
}

}

static void OnChange(object sender, SqlNotificationEventArgs e)
{
if(e.Type == SqlNotificationType.Change)
{
SqlDependency dependency = sender as SqlDependency;

// Notices are only a one shot deal
// so remove the existing one so a new
// one can be added

dependency.OnChange -= OnChange;

//fill the list again
SetUserListDependancy();
}
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,796
Messages
2,569,645
Members
45,367
Latest member
Monarch

Latest Threads

Top