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.
nmiller61Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Probably, this can help you give basic information about the SQLDependency and some basic examples. Try using the sample application, modify a little bit and then you can be comfortable incorporating in your application.
https://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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 :)
0
nmiller61Author Commented:
Thank you for the information. One other question. Could I select from a View in SQLDependency?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
Nope, must be a base table....

See the section "Supported SELECT Statements" in https://msdn.microsoft.com/en-us/library/ms181122(v=sql.105).aspx

  • The statement must not reference a view.
0
nmiller61Author Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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.
0
nmiller61Author Commented:
Thank you I do have another question on SQLDependency but I will create another thread on that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.