Databases
StackOverflow2013
Postsdbo
POSQLDependency OnChangeEvent not firing
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLDependency OnChangeEvent not firing
    text
    copied!<p>I have the following code to execute a SqlCommand against a database View:</p> <pre><code>public IEnumerable&lt;PickNote&gt; GetData() { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BTNInternalData_LiveEntities"].ConnectionString)) { connection.Open(); using (var command = new SqlCommand(@"SELECT [PICKINGROUTEID],[CUSTOMER],[SALESNAME] FROM [dbo].[PickScreenData] WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, [ACTIVATIONDATETIME])) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) or [EXPEDITIONSTATUS] = 3", connection)) { // Make sure the command object does not already have // a notification object associated with it. command.Notification = null; var dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); using (var reader = command.ExecuteReader()) { var data = reader.Cast&lt;IDataRecord&gt;(); return data.Select(x =&gt; new PickNote { pickingRouteId = x["PICKINGROUTEID"].ToString() }).ToList(); } } } } private void dependency_OnChange(object sender, SqlNotificationEventArgs e) { // do stuff } </code></pre> <p>However, the <code>dependency_OnChange</code> method only gets called at the start of my application and doesn't do so again no matter if the data in my View changes. I've debugged SqlNotificationEventArgs and the Info is <code>Invalid</code> but I'm not sure why as the command query runs fine</p> <h2>Edit</h2> <p>I've changed the query so that it queries the table directly but <code>SqlNotificationEventArgs.Info</code> still says <code>Invalid</code>. Here is the new code:</p> <pre><code>public IEnumerable&lt;PickNote&gt; GetData() { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AXLive"].ConnectionString)) { connection.Open(); using (var command = new SqlCommand(@" SELECT PICKINGROUTEID, EXPEDITIONSTATUS FROM [dbo].[WMSPICKINGROUTE] WHERE (EXPEDITIONSTATUS &lt;&gt; 20) AND (DATEADD(dd, 0, DATEDIFF(dd, 0, [ACTIVATIONDATETIME])) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) OR [EXPEDITIONSTATUS] = 3)", connection)) { // Make sure the command object does not already have // a notification object associated with it. command.Notification = null; var dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); using (var reader = command.ExecuteReader()) { var data = reader.Cast&lt;IDataRecord&gt;(); return data.Select(x =&gt; new PickNote { pickingRouteId = x["PICKINGROUTEID"].ToString() }).ToList(); } } } } </code></pre> <p><code>WMSPICKINGROUTE</code> is the table where my view was getting the data from before.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload