ASP.net core 2. 0 get error when use transaction scope : The requested operation cannot be completed because the connection has been broken

Question

I have created an API to add multiple items. I use transaction scope because I don't have to insert all items if found a problem in any item. so I have used TransactionScope so doing dispose of the transaction if found any validation failed in an item. I use a loop of 100 items to avoid database time out, but in the loop, I got error like

"The requested operation cannot be completed because the connection has been broken".

please let me know any solution tom fulfill my requirements

I send XML to the database of each 100 items in a loop. code is a per below

using (TransactionScope transactionScope = new TransactionScope())
{
        try
        {
          for (int i = 0; i < objPricebook.ItembookMasterObject.Length; i = i + 100)
          {
             List<ItembookMasterObject> items = objPricebook.ItembookMasterObject.Skip(i).Take(100).ToList();
             string ItemXML = CreateXML(items); // CreateXML is function i have created to convert data in to XML
             if (ItemXML != "")
                 {
                   DS = obj.AddItemBook(ItemXML, objGuid.ToString());
                    if (DS.Tables.Count > 0)
                      {
                        if (DS.Tables[0].Rows.Count > 0)// this means any validation is failed. 
                          {
                            transactionScope.Dispose();
                            var id = obj.AddPriceBookLogError(DS.Tables[0]);
                             return Json(new { status = "Failed", message = "There is some problem with Item json });
                           }
                        }
                   }
            }



       }
      catch (Exception ex)
      {
      transactionScope.Dispose();
      return Json(new { message = "Item Json has some invalid imput", exceptionMessage = ex.Message, errorCode = "009" });
      }

     transactionScope.Complete();
     transactionScope.Dispose();
     return Json(new { status = "Success", message = "Price book is successfully added" });
}

remove `transactionScope.Dispose();` in for loop..better write in `finally` block only once and remove from all places. you are already using `using` so ne need to dispose explicitly — Vivek Nuna, Jun 09 '20 at 10:56
@viveknuna I have to dispose of the transaction if the count is greater then zero because it indicates validation error which I return from the database. so no need to loop again if found any error. problem is when it goes to loop for second time , it throws error as i mentioned — Abhijit Pandya, Jun 09 '20 at 11:01
@viveknuna okay thanks for the suggestion but it will not solve my problem because that case I don't have faced. do u have any idea to resolve the problem? — Abhijit Pandya, Jun 09 '20 at 11:06

score 0 · Answer 1 · answered Jul 07 '22 at 16:00

Your current implementation is overkill. using blocks always call Dispose() when execution leaves the block, regardless of how that happens. As such, you don't need to call Dispose() directly.

using (TransactionScope transactionScope = new TransactionScope())
{
    try
    {
        for (int i = 0; i < objPricebook.ItembookMasterObject.Length; i = i + 100)
        {
            List<ItembookMasterObject> items = objPricebook.ItembookMasterObject.Skip(i).Take(100).ToList();
            string ItemXML = CreateXML(items); // CreateXML is function i have created to convert data in to XML
            if (ItemXML != "")
            {
                DS = obj.AddItemBook(ItemXML, objGuid.ToString());
                if (DS.Tables.Count > 0)
                {
                    if (DS.Tables[0].Rows.Count > 0)// this means any validation is failed. 
                    {
                        var id = obj.AddPriceBookLogError(DS.Tables[0]);
                        return Json(new { status = "Failed", message = "There is some problem with Item json" });
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        return Json(new { message = "Item Json has some invalid imput", exceptionMessage = ex.Message, errorCode = "009" });
    }

    transactionScope.Complete();
    return Json(new { status = "Success", message = "Price book is successfully added" });
}

ASP.net core 2. 0 get error when use transaction scope : The requested operation cannot be completed because the connection has been broken

1 Answers1