doing database write after the response

Question

I have a web service that receives requests from users and returns some json. I need to save the json string in the database so for the moment, the write query occurs before the response is sent back.

Is there a way to send the response first and then do the write query, after the response left the web service?

Thanks.

Answer 1

There's a couple of different options here - they all have tradeoffs, though, and would be pretty esoteric. You don't mention why you want to do this, so I'm guessing performance. If that's the case, I think you're barking up the wrong tree - a simple write is almost certainly not your performance problem.

So, off the top of my head:

1. Queuing, as Ragesh mentions, would be a nice approach. This gets you similar semantics of a transaction, while off loading the write. You still have to write to the queue, though, which may be about the same overhead as writing to the DB.
2. You could spawn a new thread (using either the ThreadPool or System.Threading.Thread - there's some debates about which is preferable in ASP.NET) to handle the write. This can generally work, but you may have issues with unhandled exceptions, app domain restarts, etc.
3. You could store the JSON data into a static or Application variable, then use a Timer to periodically write them to the DB. This will be multithreaded code, so you will need to synchronize read/writes to the collection.
4. Similar to #3, store the JSON data into Cache and use the invalidation callback to write to the DB.
5. Lots of variations on store somewhere (memory, disk, flat DB table, etc.), process later (ASP.NET, scheduled task, Windows Service, Sql Agent, etc.).

@frenchie says: a response starts by reading the json string from the db and ends with writing it back. In other words, if the user sends a request, the json string that's going to be read must be the one that was written in the previous response.

That complicates things, since inherent in async work is not knowing when something is done. If you require the async portion (writing back to the DB) to be done before handling the next request, you'll have to execute a wait to make sure it actually completed. In order to do that, you'll need to keep server side state on the client - not exactly a best practice as far as services go (though, it sounds like you're already doing that with these JSON request/response pairs).

Given the complications, I would make sure that you've done your profiling and determined it is indeed a performance problem.

Answer 2

You should look at using message queues like MSMQ, ActiveMQ or RabbitMQ to do this. When you receive your request, you'll put the relevant data in to the queue, and send your response to the client. At the other end of the queue, you'll have some process that reads from the queue and inserts data in to your database.

Answer 3

You can do schedule a query work like

ThreadPool.QueueUserWorkItem(state => 
      this.AsynchronousExecuteReference());

 // and run
 static void AsynchronousExecuteReference()
 {
    // run here your sql update
 }

One other example using Thread inside an class and you can pass parameters to it.

public class RunThreadProcess
{
    // Some parametres
    public int cProductID;

    // my thread
    private Thread t = null;

    // start it
    public Thread Start()
    {
        t = new Thread(new ThreadStart(this.work));
        t.IsBackground = true;
        t.SetApartmentState(ApartmentState.MTA);
        t.Start();

        return t;
    }

    // actually work
    private void work()
    {
        // do thread work
        all parametres are available here
    }
}

And here is how I run it

  var OneAction = new RunThreadProcess();
    OneAction.cProductID = 100;
    OneAction.Start();

Do not worry about memory, CG knows that this process is used until the thread ends, so I have check it and CG not delete it and wait the thread to ends.

Answer 4

this is missing the point of a request/response. unless you want to get into async commands like a service bus, but that's pub/sub, not request/response. the point of request/response is to do the work on the server after receiving the request and before sending the response. even if the work is sending an async message to a service bus.

Answer 5

You could try moving your web service URL to an ASPX page where the lifecycles come in to play.

In the code-behind, call your routine that does the main portion of the work in Page_Load or Page_Prerender (or whenever is appropriate prior to the response being sent) and then do your DB work in the Page_Unload event which occurs after the response has been sent (http://msdn.microsoft.com/en-us/library/ie/ms178472.aspx).