Wait for a change in the database to return the result in web api controller

Navigation
1

I have programmed some Web Api controllers to feed a mobile application with data ...

In one of the controls I must wait for a column in a table for a specific ID to change to return a response (in this case a JSON)

I was trying to use SqlDependency but I can not make it work ... In the examples I've seen, a triger is activated when the database changes, but what I really need is to wait for it to change, just as I do not achieve anything. I could make a "Select Status" call every few seconds, but I know it's not the right way ...

I need something like this: 

int RequestStatus = await CheckRequestStatusChangeOnDB(RequestID);

//Esperar hasta que CheckRequestStatusChangeOnDB detecte que hubo un cambio y devuelva un valor

if (RequestStatus == 1)
                {//hacer algo}
if (RequestStatus == 2)
                {//hacer algo}
if (RequestStatus == 0)
                {//no hubo cambio en la tabla (después de cierto tiempo sin respuesta)}
    
asked by ricardo_escovar 18.10.2016 в 00:26
source

2 answers

0

This answer I put in another thread, but I think it works perfectly for you too.

As you are using SQL Server I recommend reading about SQL Server Query Notification link

It also reviews Apache ActiveMQ is free and can help you in this case.

    
answered by 19.10.2016 в 18:11
0

The simplest solution is to use Ajax Polling , which consists in making a request to your api from time to time indefinitely until you get the expected result.

Imagine that you have an application for transactions that must be verified by a third party before marking them as completed.

  • A customer makes a transaction and it is marked as pending because it must be verified by a third party.

    Transaction ID: 1a2s3d4f
    Transaction Status: Pending

  • Your app makes a request (Poll) to the transaction ( GET transactions/1a2s3d4f ) and you get the transaction is pending.

    Transaction ID: 1a2s3d4f
    Transaction Status: Pending

  • Your app makes another request (Poll) to the transaction ( GET transactions/1a2s3d4f ) and you get that the transaction is still pending.

    Transaction ID: 1a2s3d4f
    Transaction Status: Pending

  • A background job is executed that verifies if the transaction was approved by the third party and modifies the status to completed.

  • Your app makes the same request ( GET transactions/1a2s3d4f ) and now you receive that the transaction has been completed.

    Transaction ID: 1a2s3d4f
    Transaction Status: Completed

    • answered by 19.10.2016 в 19:02
    How to disable the scroll in a TvertScrollBox component in Delphi? Change Nav Drawer icon on android side