SQL Cache Dependency

Question

I've implemented SQL Cache dependency in ASP.NET application. It's working the way it should, but I'm stuck with a strange issue. Here's the scenario.

- I have two tables in the scenario - tblEmployees, tblChats

- EmployeeID of tblEmployees is foreign key in tblChats.

- I enabled SQL dependency on tblChats and it was successful.

- I have a method ListChats which fetches chat messages from cache; if it's empty, then from tblChats.

- I opened my application, I added a chat record in tblChats with EmployeeID = 1. The cache got cleared.

- I executed the method ListChats(), it gets all the chat messages and puts them in the cache.

- I ran an update statement on tblChats and the cache gets cleared again, which is obvious.

- But if I run an update staetment on tblEmployee with EmployeeID = 1 it clears the cache as well. Strangely, it doesn't clear the cache if I run update statement with some other EmployeeID.

Am I missing any setting? I checked tblEmployee there are no triggers on it. Also in table AspNet_SqlCacheTablesForChangeNotification, there's only one record with tablename = tblChats.

I'd appreciate any help with this issue.

Answer 1

Hi JwalantS,

Thank you for your question. 

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Jwalants,

I went ahead and tried this with a c# console application that I had and I dont see the cache being cleared. However I am not sure I have recreated the tables and maybe the code to match your situtation. I used the code below. Is it possible for you to post more details about your database schema and asp.net code?

Bill -- Microsoft CTS

--Database
create table tblEmployees (EmployeeID int not null, Name varchar(256), Dept varchar(256))
go
create table tblChats (ChatID int not null, EmployeeID int not null, ChatMsg varchar(4000))
go

alter table tblEmployees add constraint PK_tblEmployee_EmployeeID primary key clustered (EmployeeID)
go
alter table tblChats add constraint PK_tblChats_ChatId primary key clustered (ChatID)
go
alter table tblChats add constraint FK_EmployeeID foreign key (EmployeeID) references tblEmployees(EmployeeID)
go

insert into tblEmployees (EmployeeID, Name, Dept) values (1, 'Ted', 'Accounting')
insert into tblEmployees (EmployeeID, Name, Dept) values (2, 'Jane', 'Recieving')
insert into tblEmployees (EmployeeID, Name, Dept) values (3, 'John', 'Human Resources')
go

insert into tblChats (ChatID, EmployeeID, ChatMsg) values (1, 1, 'Important chat from Ted 1')
insert into tblChats (ChatID, EmployeeID, ChatMsg) values (2, 1, 'Important chat from Ted 1')
insert into tblChats (ChatID, EmployeeID, ChatMsg) values (3, 2, 'Important chat from Jane 1')
go

select * from tblEmployees e inner join tblChats c on e.EmployeeID = c.EmployeeID
go

select * from sys.foreign_keys
go
select * from sys.objects where object_id in (select referenced_object_id from sys.foreign_keys)
go

 

--Client
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace SqlDep
{
    class Program
    {
        private static string mStarterConnectionString = @"Data Source=(local);Database=SqlDependencyTest;Persist Security Info=false; Integrated Security=false;User Id=startUser;Password=startUser";
        private static string mSubscriberConnectionString = @"Data Source=(local);Database=SqlDependencyTest;Persist Security Info=false; Integrated Security=false;User Id=subscribeUser;Password=subscribeUser";

        static void Main(string[] args)
        {

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

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

            // Waiting...
            Console.WriteLine("At this point, you should start the Sql Server ");
            Console.WriteLine("Management Studio and make ");
            Console.WriteLine("some changes to the tblEmployee table that you'll find");
            Console.WriteLine(" in the SqlDependencyTest ");
            Console.WriteLine("database. Every time a change happens in this ");
            Console.WriteLine("table, this program should be ");
            Console.WriteLine("notified.\n");
            Console.WriteLine("Press enter to quit this program.");
            Console.ReadLine();

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

        public static void RegisterForChanges()
        {
            // Connecting to the database using our subscriber connection string
            // and waiting for changes...
            SqlConnection oConnection
                                = new SqlConnection(mSubscriberConnectionString);
            oConnection.Open();
            try
            {
                SqlCommand oCommand = new SqlCommand(
                  "SELECT ChatID, EmployeeID, ChatMsg from dbo.tblChats",
                  oConnection);
                SqlDependency oDependency = new SqlDependency(oCommand);
                oDependency.OnChange += new OnChangeEventHandler(OnNotificationChange);
                SqlDataReader objReader = oCommand.ExecuteReader();
                try
                {
                    while (objReader.Read())
                    {
                        // Doing something here...
                    }
                }
                finally
                {
                    objReader.Close();
                }
            }
            finally
            {
                oConnection.Close();
            }
        }

        public static void OnNotificationChange(object caller,
                                                SqlNotificationEventArgs e)
        {
            Console.WriteLine(e.Info.ToString() + ": " + e.Type.ToString());

            RegisterForChanges();
        }

    }
}