Q:

C# Web API Multiple Callers to Method That Writes to Database

I am hoping someone can help me to write this method better.  The method works as is until I have multiple users hitting the method simultaneously.  I started to work on using async/await, but I don't know that it will work for me.  The biggest issue is we have a staging/working table that we save to, then execute a stored procedure against the table that does some stuff and clears the table when done.  The data in the table is user specific, so we need only the data in there to be for the user executing the stored procedure.  If other data gets in there, it messes things up.  This is why I have the check to make sure the table is empty before we continue on. 
In short, how can I make this method work in a truly RESTful way and provide for scalability to many users? 
[HttpPost, Route("api/SubmitOrder")]
public IHttpActionResult SubmitOrderEntry([FromBody] OrderRequest request)
{
    var profileObjs = OrderEntities. Set<OrderStaging>();
    foreach (var s in request. Items)
    {
        OrderStaging orderObj = new OrderStaging();

        orderObj. OrderName = request. OrderName;
        orderObj. OrderDescription = request. OrderDescription;
        orderObj. OrderComments = request. Comments;
        orderObj. EnteredBy = request. EnteredBy;
        //and many more

        profileObjs. Add(orderObj);
    }

    try
    {
        bool tableEmpty = false;
        int count = 0;
        int countMax = 180;

        while (! tableEmpty && count < countMax)
        {
            count++;
            if (! OrderEntities. OrderStagings. Any())
            {
                tableEmpty = true;
            }
            else
            {
                System. Threading. Thread. Sleep(5000);
            }
        }

        if (tableEmpty)
        {
            OrderEntities. Configuration. EnsureTransactionsForFunctionsAndCommands = false;
            OrderEntities. Database. CommandTimeout = 180;
            OrderEntities. SaveChanges();

            if (request. Pending)
            {
                var result = OrderEntities. SPPENDING();
                var firstRow = result. FirstOrDefault();
                String[] returnValue = new String[] { firstRow. TransactionID, firstRow. ExternalOrderID };
                return Ok(returnValue);
            }
            else
            {
                var result = OrderEntities. SPORDER();
                var firstRow = result. FirstOrDefault();
                String[] returnValue = new String[] { firstRow. TransactionID, firstRow. ExternalOrderID };
                return Ok(returnValue);
            }
        }

        return Content(HttpStatusCode. BadRequest, "SQL Server Exception");
    }
    catch (Exception e)
    {
        Console. WriteLine(e. ToString());
        return Content(HttpStatusCode. BadRequest, "SQL Server Exception");
    }
} 

A:

You will likely want to re-think your approach here.  It sounds like you have an existing solution that involved a stored procedure that processes these staged records and probably takes a bit of time to complete.  The sproc assumes that all staging records are relevant to the task it is designed to process.  Queuing multiple requests while that sproc is running will lead to issues. 
The first thing you should consider is splitting the calls into two parts: 1.  Stage the data.  2.  Poll for a result.  Don't have an API call sit and wait for the stored procedure to complete, have it pass back a result indicating the data was staged successfully or not, and the client code can hand-off to part #2, which is poll for a result for that job.  Polling can be for an individual job ID, or it can be based on a last poll date/time and poll records modified since that date/time. ( This can allow a client to "see" jobs started by other users if applicable)
Regarding your staging & Stored Procedure, a job identifier should be added to the staging table.  Each row in the staging table is given a job ID, the job record tracks some basic details about the request including: When was it started?  Who started it?  Is it complete?  What is it's status? ( queued, started, completed, failed, etc.)  What was the failure reason? ( if applicable) The stored procedure should be kicked off with a Job ID, or query any unstarted jobs and process the staging records for that job or group of jobs.  When the stored proc completes processing a set of records, it updates the job row, and purges the staging records.  I'd consider even leaving the staging records purge to a scheduled task that runs daily after-hours to avoid unnecessary locks in the database on that table. ( Delete staging rows and/or jobs that were completed/failed each night.) 
Look to design systems to scale for multiple requests that can either be processed successfully at the same time, or that all steps of the process can identify and process in order, but not hold up queuing one request until the previous completes.