locked
Sql Notification Services and SqlCacheDependency .. nothing happens! RRS feed

  • Question

  • Hi,
    I'm not sure if this is the right forum..
    I have an ASP.NET 2.0 web page and I want that when something change on the DB (SQL SERVER 2005)the content of the page changes.
    I'm writing in this forum because I don't have ASP.NEt error but actually nothing happens on the table when I insert new record in the DB.

    This is what I have done:
    ALTER DATABASE MyDB SET ENABLE BROKER
    I haven't run the GRANT SUBSCRIBE because the table has been created by me (dbo user)

    Then in the global.asax I called the two method SqlDependency.Start and SqlDependency.Stop

    Then I started to build my simple example made by a datagridview populated by a simple SELECT.
    This is the code:
    Protected Sub Page_Load
    Dim contracts As DataTable = CType(Cache.Get("Contracts"), DataTable)
    If contracts Is Nothing Then
    Dim connstring As String = "Data Source=mysqlserverinstance;Initial Catalog=mytable;Integrated Security=True;Context Connection=False"
    Dim contracts As DataTable = New DataTable()
    Dim connection As SqlConnection = New SqlConnection(connstring)
    Using (connection)
    Dim command As SqlCommand = New SqlCommand("SELECT field1, field2 FROM dbo.mytable", connection)

    Dim dependency As System.Web.Caching.SqlCacheDependency = New System.Web.Caching.SqlCacheDependency(command)
    Dim adapter As SqlDataAdapter = New SqlDataAdapter()
    adapter.SelectCommand = command
    Dim dataset As DataSet = New DataSet()
    adapter.Fill(dataset)
    contracts = dataset.Tables(0)
    Cache.Insert(
    "Contracts", contracts, dependency)
    End Using

    End If

    GridView1.DataSource = contracts.DefaultView
    GridView1.DataBind()

    End Function

    As I said when I run the application, the datagrid displays properly the content of the table but if I change something on the DB nothing happens while I was expecting that the page change somehow..
    But I don't know whether SQL SErver 2005 in not properly conffigured or something is missing in the page itself!

    Thankx for any help.

    Marina B.

    Monday, February 26, 2007 10:09 AM

Answers

  • Hi ...

    I think that I misunderstood the meaning itself of Sql Notification Services and SqlCacheDependency.

    I was expecting the the service was also able to raise an event on the page to inform that something has been changed on the cached query, but that is not the way it meant to work.

    Sql Notification Services updates properly the cached query on the memory of sql server and the only thing that it does on the the web server is to clean the cache.

    How the asp.net page checks that the query has been changed has nothing to do with Sql Notification Services ...

    You are right ... in this case I have to address  my problems/doubts/questions to an another forum!

    Thank you very much!

     

     

     

    Friday, March 16, 2007 7:47 AM

All replies

  • Hi Marina -

    You'll likely have more success in receiving an answer in another post. This one is dedicated to SQL Server Notification Services.

    Cheers!

    Joe

    Thursday, March 15, 2007 5:52 PM
  • Hi ...

    I think that I misunderstood the meaning itself of Sql Notification Services and SqlCacheDependency.

    I was expecting the the service was also able to raise an event on the page to inform that something has been changed on the cached query, but that is not the way it meant to work.

    Sql Notification Services updates properly the cached query on the memory of sql server and the only thing that it does on the the web server is to clean the cache.

    How the asp.net page checks that the query has been changed has nothing to do with Sql Notification Services ...

    You are right ... in this case I have to address  my problems/doubts/questions to an another forum!

    Thank you very much!

     

     

     

    Friday, March 16, 2007 7:47 AM