Run stored procedure in a background Web Api

Question

In my Web Api application I need to do something like in this topic: Best way to run a background task in ASP.Net web app and also get feedback? In the application user could upload an excel file and then import its data to tables in the Database. It all works fine, but the import process can take a long time (about 20 minutes, if the excel have a lot of rows) and when process is started the page is blocked and users have to wait all this time. I need this import process run in a background.

I have a controller with this POST method:

[HttpPost]
public async Task<IHttpActionResult> ImportFile(int procId, int fileId)
{
    string importErrorMessage = String.Empty;

    // Get excel file and read it
    string path = FilePath(fileId);
    DataTable table = GetTable(path);

    // Add record for start process in table Logs
    using (var transaction = db.Database.BeginTransaction()))
    {
        try
        {
            db.uspAddLog(fileId, "Process Started", "The process is started");
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            importErrorMessage = e.Message;
            return BadRequest(importErrorMessage);
        }
    }

    //Using myHelper start a store procedure, which import data from excel file
    //The procedure also add record in table Logs when it is finished
    using (myHelper helper = new myHelper())
    helper.StartImport(procId, fileId, table, ref importErrorMessage);

        if (!String.IsNullOrEmpty(importErrorMessage))
        return BadRequest(importErrorMessage);

    return Ok(true);
}

I also have a GET method, which returns information about file and its process

[HttpGet]
[ResponseType(typeof(FileDTO))]
public IQueryable<FileDTO> GetFiles(int procId)
{
    return db.LeadProcessControl.Where(a => a.ProcessID == procId)
                                .Project().To<FileDTO>();
}

It returns JSON like this:

{
  FileName = name.xlsx
  FileID = 23
  ProcessID = 11
  Status = Started
}

This method is for GRID

File name | Status | Button to run import | Button to delete file

This Status is from table Logs and in FileDTO it placed the last value, for example if I upload file the status will be "File uploaded" when I run Import status will be "Started" and when it finished status will be "Complete". But now the page is locked when the import process is running, so the status always will be "Complete".

So I need to run procedure in background and GET method should return new Status if it has been changed. Any suggestions?

Is this running in something like IIS? The problem with any long running task on a web server is it may decide to recycle part way through based on memory usage. If you are talking 20 minutes I would send this of to some other (non web) app process, maybe via a queue or service bus. The task can then record its progress somewhere the web app can retrieve, or better send back messages on the progress. — Mant101, Aug 05 '15 at 10:45
Check its recycling settings, if you recycle on hitting a memory limit or some other setting that could happen when your upload is running it is vulnerable. If you are sure it will never recycle during an upload you can kick off a task to do the work, and have it up the status in some place you can retrieve in GetFiles (DB, Application variable, static property etc.). Try to keep all the file/db access in the task async to minimize the impact. — Mant101, Aug 05 '15 at 14:31

score 1 · Accepted Answer · answered Aug 06 '15 at 03:09

Adding async to a method don't make your method call asynchronous. It just indicate that thread that is handling the current request can be reused for processing other requests while waiting for some network/disk IO. When a client call this method it will only get response once method is complete. In other word async is completely sever side thing and nothing to do with the client call. You need to start your long running process in a separate thread as shown below. But best practice is not to use web app for such a long running processing instead do long processing in a separate windows service.

[HttpPost]
    public async Task<IHttpActionResult> ImportFile(int procId, int fileId)
{
string importErrorMessage = String.Empty;

// Get excel file and read it
string path = FilePath(fileId);
DataTable table = GetTable(path);

// Add record for start process in table Logs
using (var transaction = db.Database.BeginTransaction()))
{
    try
    {
        db.uspAddLog(fileId, "Process Started", "The process is started");
        transaction.Commit();
    }
    catch (Exception e)
    {
        transaction.Rollback();
        importErrorMessage = e.Message;
        return BadRequest(importErrorMessage);
    }
}

         //Start long running process in new thread
         Task.Factory.StartNew(()=>{

         using (myHelper helper = new myHelper())
        {
           helper.StartImport(procId, fileId, table, ref importErrorMessage);

          //** As this code is running background thread you cannot return anything here. You just need to store status in database. 

         //if (!String.IsNullOrEmpty(importErrorMessage))
         //return BadRequest(importErrorMessage);
       }

        });

//You always return ok to indicate background process started
return Ok(true);
}

I see that it is better to not use web app for long processes, but I need that for some reasons) Thanks for help, I will use this. And what you can suggest for how can I check the `Status` and return the last value from `Logs`? — Alex Dubovik, Aug 06 '15 at 07:45
You can use an application variable to store the status and last log, then you create a new web api method to return the status. The client of your api will be required to pool this status method regularly. — Deepak Bhatia, Aug 07 '15 at 02:21

Run stored procedure in a background Web Api

1 Answers1