SqlCacheDependency && SqlDependency problem (Event OnChange not fired or starts infinite loop) RRS feed

  • Question

  • Hello! I have a lot of problems with SQL Data-changed Notifications.
    First of all - my configuration: Windows Server 2008 Enterprise, SQL Server 2008, VS 2010 beta (.NET 4.0 from there).
        I've tried to use SqlCacheDependency for cache-cleaning when data in tables changes (Database is simple, 2 tables: Persons (ID PK NOT NULL, Name nvarchar(255)) and Phones (ID PK NOT NULL, PersonID FK Persons.ID NOT Null, phone nvarchar(10))
        If I create SqlCacheDependency(SqlCommand) before DataTable.Load(SqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
    WebSite go into infinite loop, because objects that I've added to cache by HttpRuntime.Cache.Insert or HttpRuntime.Cache.Add methods with callback call my callback function immediately, and objects are not in cache in this stage, they removed from cache immediately.
        If I create SqlCacheDependency(SqlCommand) after DataTable.Load(SqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
    my WebSite just don't recieve messages about changes in database.
        Same problem with SqlDependency Object. In first case it going into infinite loop, in second - OnChange event never fired, even when I Update DB content from WebSite.
        I've tried change DBOwner to sa, grant all required permissions to users, called SqlDependency.Start() in Application_Start Method and
    called SqlDependency.Stop() in Application_End methods, but nothing can help me.
        Can everybody here tell me where is my problem or how can I watch for DB content changing by another way(except triggers)?
    Wednesday, February 24, 2010 10:33 AM