.net sql-server vb.net service-broker sqldependency

SQL Dependency in Web Forms - Detecting changes

I have not used SQL Dependency in practice before, but it is my understanding that DependencyOnChange Event should trigger when I run the SQL update statement below.

To clarify some specifics:

is_broker_enabled = 1
SQL User is db_owner
SqlDependency_Register runs when the page loads as expected.
Breakpoints on DependencyOnChange are not hit when MyCol is updated.
I am aware SignalR will be needed to send data to client but am still expecting code inside DependencyOnChange to be hit when debugging.

Any advice or guidance at the point would be greatly appreciated as I have exhausted all the research options I can find.

SQL

UPDATE [dbo].[MyTable] SET [MyCol] ='test' WHERE [id] = 1`

VB.NET

Private Sub Page_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
  If Not IsPostBack Then
    SqlDependency_Register()
  End If
End Sub

Protected Sub Page_Unload(sender As Object, e As EventArgs) Handles Me.Unload
  SqlDependency.Stop(ConfigurationManager.ConnectionStrings("CON").ConnectionString)
End Sub

Public Sub SqlDependency_Register()
  Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("CON").ConnectionString)
    Using cmd As New SqlCommand("SELECT [MyCol] FROM [dbo].[MyTable]", con)
      Dim dependency As New SqlDependency(cmd)
      AddHandler dependency.OnChange, AddressOf DependencyOnChange
      SqlDependency.Start(ConfigurationManager.ConnectionStrings("CON").ConnectionString)
      con.Open()
      cmd.ExecuteReader()
    End Using
  End Using
End Sub

Private Sub DependencyOnChange(sender As Object, e As SqlNotificationEventArgs)

  If e.Info = SqlNotificationInfo.Update Then
    Console.WriteLine(e.Info)
  End If

  SqlDependency_Register()
End Sub

GOAL

To show real time updates to data on the page when a data value changes in sql (e.g via another user). I understand sending data from server to client via Signalr but not how to get the .Net application to detect the change in SQL.

Perhaps there is a better approach to this than SQLDependency?

Solution

Well, keep in mind that the code behind for a given page goes OUT of scope, and is deleted, and disposed and removed from memory RIGHT AFTER the page is sent to the client side.

You can as noted consider SignalR, but you can't persist and assume that the code behind for a given web page exists. It ONLY exists during the post-back. As soon as the page is sent to the client side, then that code, your variables, and everything is removed from memory. A web server is not like desktop software in that each user has a persisting set of code and variables.

In web land, the term is 'state-less'. That means you can think of a web page (and its code) like calling a sub routine. As so as you exit that subroutine, then all of the code, and variables in that subroutine are gone, and go out of scope. Your web page code works the same way - it ONLY exists during the short post back. As soon as code behind is done running and the page is sent back to the client side, then all of the code behind NOW goes OUT of scope, is removed from memory, and does not persist.

So, the simplest solution is to ensure that the database has a row version value. This value is changed when that row of data is updated.

Hence, a simple and workable approach is to have some client-side code call a web method, and return a sum() of the row version values. If any update, delete, or insert has occurred, then that value will change.

However, as noted, you can't have SQL server events call your page code, since as I noted, the page code (the page class) does not exist in memory and it is removed from memory RIGHT after the page is sent to the client side.

So, I suppose you could start a separate process, and that separate process could track if the page been changed, and then of course using SignalR, then that could work. But you CAN NOT use the code behind for that given web page to watch for such database events, since the code behind is not in memory nor running anymore.

Unless you want to adopt some complex SignalR code and keep a process running server side?

Then I suggest a simple web method on that given page, and have the client-side check for a change, say every 1 or 2 seconds.

So, here is a working example:

Markup:

        <asp:GridView ID="GVHotels" runat="server" AutoGenerateColumns="False"
            DataKeyNames="ID" CssClass="table table-hover"
            Width="50%">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="HotelName" HeaderText="Hotel" />
                <asp:BoundField DataField="Description" HeaderText="Descriptioin" />
            </Columns>
        </asp:GridView>

        <asp:HiddenField ID="MyChanged" runat="server" Value="0" ClientIDMode="Static" />
        <asp:Button ID="cmdReload" runat="server" Text="Reload grid"
            OnClick="cmdReload_Click" />

    </div>

    <script>

        var MyTimer
        $(window).on("load", function () {
            MyTimer = setInterval(checkchanged, 2000)
        });

        function checkchanged() {
            $.ajax({
                url: "Hotels.aspx/GetChanged",
                data: {},
                dataType: "json",
                type: "POST",
                contentType: "application/json; charset=utf-8",
                success: function (data) {
                    if (data.d != $('#MyChanged').val()) {
                        $('#cmdReload').click()
                    }
                }
            });
        }

    </script>

And code behind:

Imports System.Web.Services

And hence:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadData()      ' first time grid load
    End If

End Sub

Sub LoadData()

    Dim rstData As DataTable
    Dim strSQL As String =
        "SELECT *, cast(ts as bigint) as checksum FROM tblHotelsA 
        ORDER BY HotelName"

    rstData = MyRst(strSQL)
    GVHotels.DataSource = rstData
    GVHotels.DataBind()

    Dim checksum As ULong = 0
    For Each dr As DataRow In rstData.Rows
        checksum += dr("checksum")
    Next

    MyChanged.Value = checksum

End Sub


Protected Sub cmdReload_Click(sender As Object, e As EventArgs)

    LoadData()

End Sub

<WebMethod>
Public Shared Function GetChanged() As String

    Dim rstData As DataTable
    Dim strSQL As String =
        "SELECT sum(cast(ts as bigint)) as checksum FROM tblHotelsA"

    rstData = MyRst(strSQL)
    Dim Result As ULong = rstData.Rows(0)(0)

    Return Result.ToString

End Function

So, the result is thus this:

So, when the data changes, (update, delete, or edits), then the GridView will re-load. I used a jQuery .click() event to click a button on the page to refresh, and of course the button could (should) be hidden like:

        <asp:Button ID="cmdReload" runat="server" Text="Reload grid"
            OnClick="cmdReload_Click"
            style="display:none"
            />

So, I think the above is far fewer moving parts then adopting SignalR, and doing a push out to the browser. And even if you do adopt SignalR, you still cannot have the code behind for the given page subscribe to some "event" from SQL server, since as noted, the page class (code behind) does not exist and goes out of context the instant the page has been sent to the client side.

You note that I added a web method to the web page, but it is a "static" method, and thus MUST be tagged as "Shared" (in VB.NET), or in C# tagged as static, since an instance of the page class (code behind) is not created when that web method is called.