Best way to tell a row changed in SQL Server or C# to trigger a webs ervice call.

I am trying to find a best practice for my situation. I have an MVC C# web application with a SQL Server database. I need to push new records or send updated information to a third party application if data in a number of fields acrossed 4 tables have changed. I have seen in the option of CheckSum in SQL to handle this, or a GUID field. What is the best practice? Should I have a have an table in the middle to tell if the CheskSum value or GUID value changed to call the web service?

Any guidance would be great.

Thanks.
nmiller61Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Jim HornMicrosoft SQL Server Data DudeCommented:
Recommend looking at adding an UPDATE TRIGGER to the table that executes whenever a row is updated, and performs whatever T-SQL you wish.
Scott PletcherSenior DBACommented:
I recommend looking into change tracking.  That will allow you to specify a time frame and see if any changes have occurred during that time.  It's almost no overhead, and you don't have to write any code yourself.  It doesn't track what changed, but it will tell you which rows changed from any time period to any time period (so you could check from the last time you checked to the current time, i.e., new changes only).
Dustin SaundersDirector of OperationsCommented:
Jim makes a good suggestion with triggers-- in that scenario you can have a trigger update a change tracking table and then when the time comes to sync, you can process the changes that way.  If there are multiple sources that can write to those databases that might be the way to go so that nothing gets missed.

Alternatively, if you want to avoid using SQL triggers (which many do since it can lead to accidental loops or performance problems if done improperly), you could modify your code during the data change process to also write to the change tracking table (or if you're using stored procedures, you can modify those to do the same).
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Scott PletcherSenior DBACommented:
To be clear, "change tracking" is provided by SQL Server itself.  You simply activate it and go.  To avoid confusion, I wouldn't call your own table "change tracking" tables.
Olaf DoschkeSoftware DeveloperCommented:
push new records or send updated information to a third party application
That's a bit puzzling to me. The typical use of databases, of course, is clients pulling data on demand, not the server doing any push notifications.

Push notifications may be handled with triggers, but likely would need C# code to connect to clients and push in new data. It's a bit questionable, if that's the right route to take, that would also depend on how many clients will get push notifications. Mechanisms of a client refreshing its display of data without user acting are often instead solved by pulling new data from each active client at a low frequency like once every minute.

Going for this you'd need CLR integration into the SQL Server, see https://msdn.microsoft.com/en-us/library/ms131045(v=sql.120).aspx
Microsoft mentions you may decide the CLR stored procs run server side or client side, depending on how to load balance the CPU load. Of course, that also depends on whether the client executing the CLR stored procs can reach the third party app of other clients itself. Even if the server does that, a connection each client may have to the database is only as two-way as an HTTP connection is, the client is requesting and just expecting responses to requests, it's not a web socket connection allowing push notifications initiated from the server side or other clients side. When your software could do that, you may opt for forking the database update and third-party application notifications at the state of saving data, so this doesn't even become server-side logic, it's application business logic to inform other clients of data changes.

Not knowing more about the third-party application and what type of push notifications it will need is making it entirely guesswork. If I were you, I'd offer the vendor of that third party application a way to determine changes itself, for which you'd provide change information. Change Tracking, as mentioned by Scott, would be one way to quickly enable a third party app to query what changed since a specified DateTime. What's helpful about that is it can be activated per table. Other ways of tracking SQL Server offers are Chang Data Capture and Temporal Tables (not to be mistaken with temporary tables... https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-2016-temporal-tables/)

Anything you offer that way then needs to be queried by the third party application to see whether there are changes of interest to it, e.g., changes of records it currently displays and thus wants to refresh. That's detail knowledge only each third-party application client has, which is why I'd opt for that way.

If there are simple rules and a clear outset about which new data should be pushed to clients, you could solve that with an extra server-side process, maybe a scheduled task running every five minutes, maybe even a service, which turns change info of SQL Server to push notifications towards third-party application clients. Of course, it has to know the third party clients and open up connections to them. The advantage is, this additional process might make use of any programming language and framework best for the type of push notifications it needs to send.

Bye, Olaf.
nmiller61Author Commented:
I agree Olaf, preferably I would rather the third party application to call a web service on my side and get the data it needs. But that is already out of the question I have to push data to them.

I would say that everything could be handled in my web application so that when a change is made it pushes the data, but there are several transactions that occur when importing data directly to the database itself.

The way I was thinking of handling everything in concept was to have a scheduled task that ran every 30 minutes and any new records or updates it would then call the web services and push the changes out. I just need to make sure my web services know the changes that need pushed out.

Thank you all for the responses.
Olaf DoschkeSoftware DeveloperCommented:
OK, that overall rather sounds like the third party application just has its own database and you're implementing the part of database synchronization exporting changed data. If there is a specific database of the third-party application the most straightforward way would be data replication, wouldn't it? SQL Server offers many options not only in regard to replication to other MSSQL Servers and instances. Linked servers would be one way to let the SQL Server know and act on this other database.

Bye, Olaf.
Dustin SaundersDirector of OperationsCommented:
The way I was thinking of handling everything in concept was to have a scheduled task that ran every 30 minutes and any new records or updates it would then call the web services and push the changes out. I just need to make sure my web services know the changes that need pushed out.

That depends on your 'up to date' requirements.

You can watch a SQL database for changes in C# and fire a method on result using SqlDependency https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency

If I were creating this myself to say push updates to a 3rd party API, I would create a table for the changes to sync and then a column to flag whether or not those have been processed yet.

C# service fires up, looks at the changes and any unprocessed are queue and 'pushed'.  Then after the change is verified on the other end, flag the change as completed.  Then the service begins to watch for the SqlDependency activation to push out other changes the same way when the service is running.

Question-- can the data be updated on the other end?  Who should win in a conflict when the local data is pushed but the data on the 3rd party side is newer?

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
nmiller61Author Commented:
The data on the other end will fill out fields that are not already sent over. It will not modify the data that is passed over from my application. This has been very helpful. Thank you all for the great feedback. I will investigate the thoughts out there. Right now I really like the idea of using database triggers and then having that C# service. It seems clean, simple, and easy to track.
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
SQL

From novice to tech pro — start learning today.