asked on

SQLDependency Question

I currently have an MVC Web Application that is being used. I am in needed of when certain tables getting updated that those changes get pushed out to a 3rd party via a web service call. In a prior post it seems one of the best ways to handle this is by means of SQLDependency. Now I have been looking at examples online. My question now is how do I best implement SQLDependency. If I added in a class in my current MVC Web Application will it continually run? Or do I need to create another application? I am using Visual Studio 2017 and I tried creating a console application with .NET Core and seems it cannot use the assemblies for SQLDependency. Any help on implementing this in a way that it is constantly checking would be great.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Found this in github : https://github.com/dotnet/corefx/issues/17833

Might need to drop back a version ? Didnt read all that well for 2017, they reckon it was going to be fixed in 2.1, but reading the release notes
https://github.com/dotnet/core/blob/master/release-notes/2.1/2.1.0-preview1-known-issues.md

If you install .NET Core 2.1 Preview 1 on a machine with Visual Studio 2017 version 15.5.6 or lower, then ASP.NET Core 2.0 is not available in dropdown when creating new web applications

Apply VS update 15.5.7 to make ASP.NET Core 2.0 show up in the web project creation dialog again

Also, just be very aware that there are a lot of restrictions on the query you can use with notifications : https://msdn.microsoft.com/en-us/library/ms181122(v=sql.105).aspx

Anyway, hope that helps a bit :)

nmiller61

ASKER

Thank you for the information. One other question. Could I select from a View in SQLDependency?

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmiller61

ASKER

Thank you again for the information. I was looking at the below URL and I was trying the ASP.NET implementation. Am I understand it correctly that the ASP.NET implementation is not just looking for changes with the query? Instead it is triggered by the Insert to go and check the database again?

https://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach

I understand the concept of SQLDependency but I am having a hard time understand how to implement it in a way that it is just running on my server 24/7 and if there is a change to my table that I have the SQLDependency connected too that it will then do some action. I feel like I am still seeing that the application has to be triggered to see if a change is made.

Raja Jegan R

>> ASP.NET implementation is not just looking for changes with the query?
Its not with changes in the Query, its triggered for any change in the result set of the query..

>> just running on my server 24/7 and if there is a change to my table that I have the SQLDependency connected too that it will then do some action.
Yes, if there is a change to data in your table and can be identified by your query(with your WHERE clauses or selected COLUMN names) then the response will be triggered.

>> I feel like I am still seeing that the application has to be triggered to see if a change is made.
Your query will be configured one time and if the result set changes, then SQLDependency will trigger a notification and will do the operation you have configured out.

nmiller61

ASKER

Thank you I do have another question on SQLDependency but I will create another thread on that.