SqlDependency problem with asp.net

Question

I am trying to get changed values ​​from sqlserver using server_broker function.

my code looks 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 code through a breakpoint that I make some changes to the database, it can reach a method called dependency_OnDataChangedDelegate, but I have not seen the changes in my datagrid. where am i doing wrong?

my page source is as follows:

  <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> 

The source code is as follows:

Ext.net.ResourceMgr.init ({ID: "mymanager", aspForm: "Form1"}); Ext.onReady (function () {Ext.create ("Ext.grid.Panel", {store: {Model: Ext.define ("App.Model1", {expand: "Ext.data.Model", fields: [ {name: "Id"}, {name: "Ad"}, {name: "Solid"}]}), StoreID: "strMasa", AutoLoad: true, proxy: {data: [{"Identifier": "8 "," Ad ":" 44448989 "," durum ":" 2 "}, {" Identifier ":" 9 "," Ad ":" Masa 55i "" durum ":" 1 "}, {" Id ": "12", "Ad": "Masa 3", "durum": "1"}, {"Id": "44", "Ad": "MAYU 4", "durum": "1"}, { "Id": "45", "Ad": "MAYU 5", "durum": "1"}, {"Id": "46", "Ad": "MAYU 6", "durum": "- 1 "}, {" Id ":" 47 "," Announcement ":" Masa 7 "," durum ":" - 1 "}, {" Identifier ":" 48 "," Ad ":" MAYA 8 ", "durum": "- 1"}, {"Id": "49", "Announcement": "Masa 9", "durum": "1"}, {"Id": "51", "Ad": MAYUA 10, Solid: "2"}, {"Identifier": "52", "Announcement": "MAC 11", "durum": "- 1"}, {"Id": "53", "Announcement": "MACA 12" , "durum": "- 1"}, {"Identifier": "54", "Ad": "MACA 13", "durum": "- 1"}, {"Id": "55", "Announcement ":" MACA 14 "," durum ":" - 1 "}, {" Identifier ":" 56 "," Ad ":" MACA 15 "," Durum ":" - 1 "}, {" Id ": "57", "Ad": "MASA 166", "Durum": "- 1"}], enter: '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"}, {identifier: "KisxiId", hidden : etc vda, flex: 1, dataIndex: "Id", text: "Id"}, {identifier: "Coluxmn2w", flex: 1, align: "center", dataIndex: "Ad", text: "Masa Adı"}, {identifier: "Column8", flex: 1, align: "center", dataIndex: "durum", text: "durum"}, {identifier: "Column11", hidden: true, flex: 1, alignment: "center" , dataIndex: "durum", text: "durum"}]}, selModel: window.App.rowSelectioxnModel2 = Ext.create ("Ext.selection.RowModel", {proxyId: "rowSelectioxnModel2", selType: "rowmodel", selected : [{RowIndex: 0}]})});});

Update

this is how to enable the database (sql server express 2012), in which all changes come from the database as a trigger.

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

Answer 1

All the code you provided is third-party ASP.Net code. The grid is displayed on the client, is an HTML element. You need to notify the customer of the change. Updating HTTP is not a trivial issue, you should either:

• use a client timer and periodically check for changes.
• use websockets
• use Server-Sent Events ( Content-Type: text/event-stream ), but this is not supported by IE

The survey works, but it can receive taxation on the server, especially with a large number of clients. There are many examples of polling ASP.Net using Ajax.

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

You probably should also take a look at SignalR , which is the developer of the ASP.Net library specifically to push updates to the client.

As you can see, my answer does not even touch on the subject of Request Notification . Getting notification from the database to the middle tier of ASP.Net is only one part of the equation, and SqlDependency is really the right answer. But you completely missed the second part by pushing a notification from the middle tier to the browser. You only need to notify the browser that the update has occurred, and the client should update. Let the update load data using the regular Page_load event. Use SqlCacheDependency for the server page, this automatically caches the results and updates the cache on any update.

Answer 2

For an ASP.NET application, you must use the SqlCacheDependency class in the System.Web.Caching ; therefore, you do not need an OnChange handler in this scenario.

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

Request notification using SqlDependency and SqlCacheDependency

However, you mentioned that you are using SQL Server Express 2012 and according to the Features Supported by SQL Server 2012 Editions page and a similar case in the earlier version of SQl Server Express Using Service Broker with Sql Server Express 2008 , because SQL Server 2012 Express does not support the brokerage service , you You will be unable to complete the query query in SQL Server 2012 Express.

Edit: This paragraph is incorrect (assume that the MSDN page on which it is based is misleading). Express versions support Service Broker and Query Notifications are just fine in all versions with SQL Server 2005. The "Client Only" comment is better explained in Express SQL Server Express Features :

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

these restrictions do not affect request notifications.

These links are now simply informative. Detecting changes using SqlDependency , Creating query notification , SqlDependency in an ASP.NET application .