none
Cross-row business rule checks as post-action of AdapterManager.UpdateAll RRS feed

  • Question

  • Hi all!

    I am using AdapterManager class to update SQL Server 2008 DB from DataSet. AdapterManager uses TableAdapter to perform by-row modification operations. So the following issue occures:

    I need to check cross-row checks before commit. E.g. sum of particular column for some records should be less or equal to 100 and so on. It is impossible to implement this logic via DB constraints and current DataSet update logic. These checks should be implemented on DB side.

    I tried to do re-write "native" Update All method to inject additional SqlCommand call to verify data.

    // ---- Perform updates -----------
            //
            if ((this.UpdateOrder == UpdateOrderOption.UpdateInsertDelete))
            {
              result = (result + this.UpdateUpdatedRows(dataSet, allChangedRows, allAddedRows));
              result = (result + this.UpdateInsertedRows(dataSet, allAddedRows));
            }
            else
            {
              result = (result + this.UpdateInsertedRows(dataSet, allAddedRows));
              result = (result + this.UpdateUpdatedRows(dataSet, allChangedRows, allAddedRows));
            }
            result = (result + this.UpdateDeletedRows(dataSet, allChangedRows));
            
            // !!!!!
            CheckResourceAllocation(dataSet, workTransaction);
    
            workTransaction.Commit();
            // ...
    //--------------------------------
        private void CheckResourceAllocation(RoadmapDBResourceDataSet dataSet, 
          IDbTransaction transaction)
        {
          var invalidResourceAllocationTable = new DataTable();
          var checkAllocationCommand = Connection.CreateCommand();
          checkAllocationCommand.Transaction = transaction;
          checkAllocationCommand.CommandType = CommandType.StoredProcedure;
          checkAllocationCommand.CommandText = "dbo.Resource_CheckAllocation";
          using (var reader = checkAllocationCommand.ExecuteReader())
          {
            invalidResourceAllocationTable.Load(reader);
            ApplyAllocationErrorsToDataTable(dataSet.Resource, invalidResourceAllocationTable);
          }
        }
    
    

    But I do not like this decision because:
    1. It is impossible to use by-row Update SQL procedures separately.

    2. Difficult to handle errors and update DataSet with corresponding error messages.

     

    As another option - Fully custom update logic implementation via batch SQL Server Stored Procedure which uses XML parameter with all records that should be modified. that SP should perform Merge logic and return XML result, which should be manually parsed and applied to DataSet.

     

    Any of these options are not very simple....

    Colleagues, what do you think about? It there any solution?

     

    Thanks is advance :)

    Regards,
       Vlad Yanum

    Wednesday, January 12, 2011 12:35 PM

All replies