SqlDependency issue with the asp.net

问题:

I am trying get the changed values from the sqlserver using the server_broker feature.

my code is like below

 protected void Page_Load(object sender, EventArgs e)
{
    GetData2();

}
private void GetData2()
{
    List<Masa> lst = new List<Masa>();
    using (SqlConnection con = Baglan.Sql)
    {
        string sql = "SELECT [Id],[Ad],[Durum] FROM [dbo].[Masa]";
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            con.Open();
            SqlDependency dependency = new SqlDependency(cmd);
            dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);

            using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    Masa alt = new Masa
                    {
                        Ad = reader["Ad"].ToString(),
                        Id = reader["Id"].ToString(),
                        Durum = reader["Durum"].ToString()
                    };

                    lst.Add(alt);
                }

                gridMasa.GetStore().DataSource = lst;
                gridMasa.GetStore().DataBind();
            }


        }
    }


}

public void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e)
{   GetData2();
    SqlDependency dependency = sender as SqlDependency;

    dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);

}

when I check the the code via breakpoint which I make some changes on database,It can reach the method called dependency_OnDataChangedDelegate ,but I have no seen changes on my datagrid. where am I doing wrong??

my page source is as below:

 <ext:ResourceManager runat="server" ID="mymanager"></ext:ResourceManager>
<ext:GridPanel runat="server"  ID="gridMasa" Title="MASALAR" Height="580" Layout="FitLayout" Flex="1"
                        IDMode="Static">

                        <Store>
                            <ext:Store ID="strMasa" runat="server">

                                <Model>
                                    <ext:Model ID="Model1" runat="server">
                                        <Fields>
                                            <ext:ModelField Name="Id" />
                                            <ext:ModelField Name="Ad" />
                                            <ext:ModelField Name="Durum" />
                                        </Fields>
                                    </ext:Model>
                                </Model>
                                <Listeners>
                                    <Exception Handler="Ext.Msg.alert('Products - Load failed', operation.getError());" />
                                </Listeners>
                            </ext:Store>
                        </Store>

                        <ColumnModel ID="ColumnMxodel1" Flex="1" runat="server">
                            <Columns>

                                <ext:RowNumbererColumn ID="Column5" runat="server" Text="Id" 
                                    >
                                </ext:RowNumbererColumn>

                                <ext:Column ID="KisxiId" runat="server" Hidden="true" Text="Id" DataIndex="Id" 
                                    Flex="1">
                                </ext:Column>
                                <ext:Column ID="Coluxmn2w" runat="server" Text="Masa Adı" Flex="1" Align="Center" DataIndex="Ad">

                                </ext:Column>
                                   <ext:Column ID="Column8" runat="server" Text="Durum" Flex="1" Align="Center" DataIndex="Durum">

                                </ext:Column>
                                      <ext:Column ID="Column11" Hidden="true" runat="server" Text="Durum" Flex="1" Align="Center" DataIndex="Durum">

                                </ext:Column>

                            </Columns>
                        </ColumnModel>
                        <SelectionModel>
                            <ext:RowSelectionModel ID="rowSelectioxnModel2" ClientIDMode="Static" runat="server">
                                <SelectedRows>
                                    <ext:SelectedRow RowIndex="0"></ext:SelectedRow>
                                </SelectedRows>


                            </ext:RowSelectionModel>
                        </SelectionModel>

                    </ext:GridPanel>

source code is as below:

Ext.net.ResourceMgr.init({id:"mymanager",aspForm:"form1"}); Ext.onReady(function(){Ext.create("Ext.grid.Panel",{store:{model:Ext.define("App.Model1", {extend: "Ext.data.Model", fields:[{name:"Id"},{name:"Ad"},{name:"Durum"}] }),storeId:"strMasa",autoLoad:true,proxy:{data:[{"Id":"8","Ad":"44448989","Durum":"2"},{"Id":"9","Ad":"MASA 55i","Durum":"1"},{"Id":"12","Ad":"MASA 3","Durum":"1"},{"Id":"44","Ad":"MASA 4","Durum":"1"},{"Id":"45","Ad":"MASA 5","Durum":"1"},{"Id":"46","Ad":"MASA 6","Durum":"-1"},{"Id":"47","Ad":"MASA 7","Durum":"-1"},{"Id":"48","Ad":"MASA 8","Durum":"-1"},{"Id":"49","Ad":"MASA 9","Durum":"1"},{"Id":"51","Ad":"MASA 10","Durum":"2"},{"Id":"52","Ad":"MASA 11","Durum":"-1"},{"Id":"53","Ad":"MASA 12","Durum":"-1"},{"Id":"54","Ad":"MASA 13","Durum":"-1"},{"Id":"55","Ad":"MASA 14","Durum":"-1"},{"Id":"56","Ad":"MASA 15","Durum":"-1"},{"Id":"57","Ad":"MASA 166","Durum":"-1"}], type: 'memory'},listeners:{exception:{fn:function(proxy,response,operation){Ext.Msg.alert('Products - Load failed', operation.getError());}}}},id:"gridMasa",height:580,renderTo:"App.gridMasa_Container",flex:1,layout:"fit",title:"MASALAR",columns:{id:"ColumnMxodel1",flex:1,items:[{id:"Column5",xtype:"rownumberer",text:"Id"},{id:"KisxiId",hidden:true,flex:1,dataIndex:"Id",text:"Id"},{id:"Coluxmn2w",flex:1,align:"center",dataIndex:"Ad",text:"Masa Adı"},{id:"Column8",flex:1,align:"center",dataIndex:"Durum",text:"Durum"},{id:"Column11",hidden:true,flex:1,align:"center",dataIndex:"Durum",text:"Durum"}]},selModel:window.App.rowSelectioxnModel2=Ext.create("Ext.selection.RowModel",{proxyId:"rowSelectioxnModel2",selType:"rowmodel",selectedData:[{rowIndex:0}]})});});

update

this is how enable database(sql server express 2012) which triger every changes happened database.

alter database [Your database name here] set enable_broker with rollback immediate 
select name, is_broker_enabled from sys.databases 

回答1:

All the code you've shown is ASP.Net side code. The grid is shown on the client, is an HTML element. you need to notify the client of the change. HTTP refresh is not a trivial issue, you have to either:

• use a timer of the client and periodically poll for changes
• use WebSockets
• use Server-Sent Events (Content-Type: text/event-stream) but this is not supported by IE

Polling works but it can get taxing on the server, specially with large number of clients. There are countless examples of ASP.Net backed polling using Ajax.

WebSockets requires Windows 8/windows Server 2012 and IIS 8, see Support for WebSockets Protocol

You should probably also look into SignalR, which is an ASP.Net library specifically developer for pushing updates to the client.

As you can see, my answer does not even touch the topic of Query Notifications. Getting the notification from the DB to the ASP.Net middle tier is only one part of the equation, and SqlDependency is indeed the right answer. But you're completely missing the second part, the pushing of notification from mid-tier to the browser. You should only notify the browser that the update occurred and the client should refresh. Let the refresh load the data using the usual Page_load event. Use SqlCacheDependency to server the page, this will automatically cache results and refresh the cache on any update.

回答2:

For an ASP.NET application you have to use the SqlCacheDependency class in name space System.Web.Caching; so you don't need the OnChange handler in this scenario.

This article explains how to implement SqlCacheDependency for an asp.net client:

Query Notification using SqlDependency and SqlCacheDependency

However, you have mentioned you are using SQL Server Express 2012 and according to page Features Supported by the Editions of SQL Server 2012 and similar case in older SQl Server Express edition Using Service Broker with Sql Server Express 2008, since the SQL Server 2012 Express does not support the broker service, you will be unable to perform Query Notification in SQL Server 2012 Express.

Edit: this paragraph is incorrect (reckon due to the MSDN page on which is based being misleading). Express editions support Service Broker and Query Notifications just fine, on all versions since SQL Server 2005. The 'Client Only' comment is better explained on the Express SQL Server Express Features page:

SQL Server Express supports Service Broker, but direct communication between two SQL Server Express servers is not supported.

this restrictions does not affect Query Notifications.

These references are now merely informational Detecting Changes with SqlDependency , Creating a Query Notification , SqlDependency in an ASP.NET Application.