none
Update fails for large # of records.. Works for small # of records... RRS feed

  • Question


  • I am using Linq to SQL and I'm running into an odd issue.
    I have a secton of code that should do afew things:

    1)   Create a new JobID through a stored proc.
    2)   Find records of a specific type and assign them the jobid
    3)   Commit updates

    If I set the # of rows to be updated to a small number (1-100) it always works.
    If I set the # of rows to be updated to a large number (1000's) it always fails with this error:

    1 of 9944 updates failed. at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at
    System.Data.Linq.DataContext.SubmitChanges() at
    JobManagement_ScheduleJob.ScheduleSelectedRecords_Click(Object sender, EventArgs e) in
    ScheduleJob.aspx.cs:line 232


    This was the message I got when trying to update 10,000 records...


    Can anyone suggest a starting place to help figure this out?


     protected void ScheduleSelectedRecords_Click(object sender, EventArgs e) 
        { 
            if (this.ScheduleSelectedRecords.Enabled == true
            { 
                if (Int32.TryParse(Request.QueryString["CustomerID"], out CustomerID) == false
                { 
                    Response.Redirect(String.Format("~/Error.aspx?Error={0}""Invalid CustomerID Format")); 
                } 
                if (Int32.TryParse(Request.QueryString["JobTypeID"], out JobTypeID) == false
                { 
                    Response.Redirect(String.Format("~/Error.aspx?Error={0}""Invalid JobTypeID Format")); 
                } 
     
                using (PersonalizationDataContext persoDB = new PersonalizationDataContext()) 
                { 
     
                    int piecesInJob = 0; 
                    string ErrorMessage = String.Empty; 
                    int? JobID = 0; 
                    if (persoDB.usp_InsertJob(CustomerID, JobTypeID, ref ErrorMessage, ref JobID) == 0) 
                    { 
                        Response.Redirect(String.Format("~/Error.aspx?Error={0}", ErrorMessage)); 
                    } 
                    else 
                    { 
                        // JobID created 
                        //  
                        // Tag records 
                        try 
                        { 
                            using (TransactionScope ts = new TransactionScope()) 
                            { 
                                foreach (GridViewRow Row in UnscheduledRecordsView.Rows) 
                                { 
                                        bool converted; // Used for tryparse values 
     
                                        // Try to convert the scheduled records value, else set to 0 
                                        int ScheduledRecordsValue; 
                                        converted = Int32.TryParse(((TextBox)Row.Cells[7].Controls[1]).Text, out ScheduledRecordsValue); 
                                        if (!converted) 
                                        { 
                                            ScheduledRecordsValue = 0; 
                                        } 
     
                                        int KitID = Int32.Parse(UnscheduledRecordsView.DataKeys[Row.RowIndex].Values["KitID"].ToString()); 
                                        int Kit_PersoProduct_ID = Int32.Parse(UnscheduledRecordsView.DataKeys[Row.RowIndex].Values["Kit_PersoProduct_ID"].ToString()); 
                                        int CardID = Int32.Parse(UnscheduledRecordsView.DataKeys[Row.RowIndex].Values["CardID"].ToString()); 
                                        int LetterID = Int32.Parse(UnscheduledRecordsView.DataKeys[Row.RowIndex].Values["LetterheadID"].ToString()); 
                                        int EnvelopeID = Int32.Parse(UnscheduledRecordsView.DataKeys[Row.RowIndex].Values["EnvelopeID"].ToString()); 
     
                                        var RecordsQuery = (from dr in persoDB.DataRecords 
                                                            where (dr.Kit_PersoProduct_ID == Kit_PersoProduct_ID) && 
                                                            (dr.CardID == CardID) && 
                                                            (dr.LetterID == LetterID) && 
                                                            (dr.EnvelopeID == EnvelopeID) && 
                                                            (dr.JobID == null
                                                            select dr).Take(ScheduledRecordsValue); 
     
     
                                        foreach (DataRecord record in RecordsQuery) 
                                        { 
                                            record.JobID = JobID; 
                                        } 
     
                                        int recordsSelected = RecordsQuery.Count(); 
                                        piecesInJob += recordsSelected; 
     
                                        // Update inventory 
     
                                        // inventoryID 0 is reserved for "N/A" or not used in this kit 
                                        if (CardID != 0) 
                                        { 
                                            persoDB.Inventories.Where(inv => inv.InventoryID == CardID).First().ProjectedQuantity -= recordsSelected; 
                                        } 
                                        if (LetterID != 0) 
                                        { 
                                            persoDB.Inventories.Where(inv => inv.InventoryID == LetterID).First().ProjectedQuantity -= recordsSelected; 
                                        } 
                                        if (EnvelopeID != 0) 
                                        { 
                                            persoDB.Inventories.Where(inv => inv.InventoryID == EnvelopeID).First().ProjectedQuantity -= recordsSelected; 
                                        } 
     
                                        var KitInventoryIDs = from ids in persoDB.Kit_Inventories 
                                                              where ids.KitID == KitID 
                                                              select ids.InventoryID; 
     
                                        foreach (int? ID in KitInventoryIDs) 
                                        { 
                                            if (ID != 0) 
                                            { 
                                                persoDB.Inventories.Where(inv => inv.InventoryID == ID).First().ProjectedQuantity -= recordsSelected; 
                                            } 
                                        } 
                                } 
                                 
                                Job PersoJob = persoDB.Jobs.Where(job => job.JobID == JobID).First(); 
     
                                PersoJob.JobStatusCodeID = 
                                    persoDB.JobStatusCodes.Where(jobStatus => jobStatus.Description == "READY FOR ADDRESS VALIDATION").First().JobStatusCodeID; 
     
                                PersoJob.PieceCount = piecesInJob; 
     
                                // Changes are good, commit. 
                                persoDB.SubmitChanges(); 
                                ts.Complete(); 
                            } 
                        } 
                        catch (Exception ex) 
                        { 
                // was starting to look here for answers 
                            foreach (ObjectChangeConflict occ in persoDB.ChangeConflicts) 
                            { 
                                MetaTable metatable = persoDB.Mapping.GetTable(occ.Object.GetType()); 
                                DataRecord entityInConflict = (DataRecord)occ.Object; 
                            } 
     
                            Response.Redirect(String.Format("../Error.aspx?Error={0}""ATTN : ERROR CREATING JOB    " + ex.Message.Replace('\n', ' ') + ex.StackTrace.Replace('\n', ' '))); 
                        } 
     
                        Response.Redirect(String.Format("JobDetails.aspx?JobID={0}", JobID), false); 
     
                        // Set Status to Ready for address processing 
                        // Display success message 
                    } 
                } 
            } 
        } 

    Friday, December 26, 2008 5:32 PM

Answers

  • Found my issue...

    The default timeout for a transaction was kicking in and killing the transaction.

    I changed the timeout to (no timeout) and it completed.

    using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero)) 

    • Marked as answer by John Fly Friday, December 26, 2008 7:02 PM
    Friday, December 26, 2008 7:02 PM