nmiller61
asked on
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.
Any guidance would be great.
Thanks.
Jim Horn
Recommend looking at adding an UPDATE TRIGGER to the table that executes whenever a row is updated, and performs whatever T-SQL you wish.
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
Bye, Olaf.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.