Update and delete table rows using the SDK for .NET

This article includes examples using both late-bound and early-bound programming styles. More information: Early-bound and late-bound programming using the SDK for .NET

Each of the examples uses a svc variable that represents an instance of a class that implements the methods in the IOrganizationService interface. For information about the classes that support this interface see IOrganizationService Interface.

Basic update

Both of the examples below use the IOrganizationService.Update method to set column values for a table row that was previously retrieved.

Use the Entity.Id property to transfer the unique identifier value of the retrieved row to the entity instance used to perform the update operation.

var retrievedAccount = new Entity("account", new Guid("a976763a-ba1c-e811-a954-000d3af451d6"));

//Use Entity class with entity logical name
var account = new Entity("account");
account.Id = retrievedAccount.Id;
// set attribute values
// Boolean (Two option)
account["creditonhold"] = true;
// DateTime
account["lastonholdtime"] = DateTime.Now;
// Double
account["address1_latitude"] = 47.642311;
account["address1_longitude"] = -122.136841;
// Int
account["numberofemployees"] = 400;
// Money
account["revenue"] = new Money(new Decimal(2000000.00));
// Picklist (Option set)
account["accountcategorycode"] = new OptionSetValue(2); //Standard customer

//Update the account
svc.Update(account);

var retrievedAccount = new Account()
{ Id = new Guid("a976763a-ba1c-e811-a954-000d3af451d6") };

var account = new Account();
account.Id = retrievedAccount.Id;
// set attribute values
// Boolean (Two option)
account.CreditOnHold = true;
// DateTime
account.LastOnHoldTime = DateTime.Now;
// Double
account.Address1_Latitude = 47.642311;
account.Address1_Longitude = -122.136841;
// Int
account.NumberOfEmployees = 400;
// Money
account.Revenue = new Money(new Decimal(2000000.00));
// Picklist (Option set)
account.AccountCategoryCode = new OptionSetValue(2); //Standard customer

//Update the account
svc.Update(account);

Use the UpdateRequest class

Instead of using the IOrganizationService.Update method, you can use either the late-bound Entity class or the generated early-bound entity classes with the UpdateRequest class by setting the entity instance to the UpdateRequest.Target property and then using the IOrganizationService.Execute method.

var request = new UpdateRequest()
{ Target = account };
svc.Execute(request);

When to use the UpdateRequest class

You must use the UpdateRequest class if you want to pass optional parameters. There are two cases where you might need special parameters.

• When you want duplicate detection rules to be applied. More information: Detect duplicate data using the SDK for .NET
• When you're creating a table row that represents a solution component, such as a WebResource and want to associate it with a specific solution. In this case, you would include the value of the Solution.UniqueName using the SolutionUniqueName parameter. More information: Use messages with the SDK for .NET

You must also use the UpdateRequest class if you want to specify an optimistic concurrency behavior. More information: Optimistic concurrency behavior

Use the UpdateMultipleRequest class

The UpdateMultipleRequest class is the most performant way to update multiple records in a single request. More information: Bulk Operation messages

Update related entities in one operation

In a similar manner to how you can Create related table rows in one operation, you can also update related table rows.

To update related table rows, you have to retrieve a row with the related rows so that you can access the ID values. More information: Retrieve with related rows

var account = new Entity("account");
account.Id = retrievedAccount.Id;

//Define relationships
var primaryContactRelationship = new Relationship("account_primary_contact");
var AccountTasksRelationship = new Relationship("Account_Tasks");

//Update the account name
account["name"] = "New Account name";

//Update the email address for the primary contact of the account
var contact = new Entity("contact");
contact.Id = retrievedAccount.RelatedEntities[primaryContactRelationship]
.Entities.FirstOrDefault().Id;
contact["emailaddress1"] = "[email protected]";

List<Entity> primaryContacts = new List<Entity>();
primaryContacts.Add(contact);  
account.RelatedEntities.Add(primaryContactRelationship, new EntityCollection(primaryContacts));

// Find related Tasks that need to be updated
List<Entity> tasksToUpdate = retrievedAccount
.RelatedEntities[AccountTasksRelationship].Entities
.Where(t => t["subject"].Equals("Example Task")).ToList();

// A list to put the updated tasks
List<Entity> updatedTasks = new List<Entity>();

//Fill the list of updated tasks based on the tasks that need to but updated
tasksToUpdate.ForEach(t => {
var updatedTask = new Entity("task");
updatedTask.Id = t.Id;
updatedTask["subject"] = "Updated Subject";

updatedTasks.Add(updatedTask);
});

//Set the updated tasks to the collection
account.RelatedEntities.Add(AccountTasksRelationship, new EntityCollection(updatedTasks));

//Update the account and related contact and tasks
svc.Update(account);

var account = new Account();
account.Id = retrievedAccount.Id;

//Update the account
account.Name = "New Account name";

//Update the email address for the primary contact of the account
account.account_primary_contact = new Contact
{ Id = retrievedAccount.PrimaryContactId.Id, EMailAddress1 = "[email protected]" };

// Find related Tasks that need to be updated
List<Task> tasksToUpdate = retrievedAccount.Account_Tasks
    .Where(t => t.Subject.Equals("Example Task")).ToList();

// A list to put the updated tasks
List<Task> updatedTasks = new List<Task>();

//Fill the list of updated tasks based on the tasks that need to but updated
tasksToUpdate.ForEach(t =>
{
    updatedTasks
    .Add(new Task() {
        ActivityId = t.ActivityId,
        Subject = "Updated Subject"
    });

});

//Set the updated tasks to the collection
account.Account_Tasks = updatedTasks;

//Update the account and related contact and tasks
svc.Update(account);

Check for duplicate records

When updating a table row, you may change the values so that the row represents a duplicate of another row. More information: Detect duplicate data using the SDK for .NET

Update with Alternate Key

If you have an alternate key defined for a table, you can use that in place of the primary key to update a row. You can't use the early-bound class to specify the alternate key. You must use the Entity(String, KeyAttributeCollection) constructor to specify the alternate key.

If you want to use early bound types, you can convert the Entity to an early bound class using the Entity.ToEntity<T> method.

The following example shows how to update an Account using an alternate key defined for the accountnumber column (attribute).

var accountNumberKey = new KeyAttributeCollection();
accountNumberKey.Add(new KeyValuePair<string, object>("accountnumber", "123456"));

Account exampleAccount = new Entity("account", accountNumberKey).ToEntity<Account>();
exampleAccount.Name = "New Account Name";
svc.Update(exampleAccount);

More information:

• Work with alternate keys
• Use an alternate key to reference a record

Update and delete records in elastic tables

If you're updating or deleting elastic table data stored in partitions, be sure to specify the partition key when accessing that data. More information: Partitioning and horizontal scaling

Use Upsert

Typically in data integration scenarios you need to create or update data in Dataverse from other sources. Dataverse may already have records with the same unique identifier, which may be an alternate key. If a table row exists, you want to update it. If it doesn't exist, you want to create it so that the data being added is synchronized with the source data. This is the scenario when you want to use upsert.

The following example uses UpsertRequest twice. The first time the account row is created, and the second time it's updated because it has an accountnumber value and there's an alternate key using that column (attrbute).

For both calls, the UpsertResponse.RecordCreated property indicates whether the operation created a row or not.

// This environment has an alternate key set for the accountnumber attribute.

//Instantiate account entity with accountnumber value
var account = new Entity("account", "accountnumber", "0003");
account["name"] = "New Account";

//Use Upsert the first time
UpsertRequest request1 = new UpsertRequest() {
Target = account
};

//The new entity is created
var response1 = (UpsertResponse)svc.Execute(request1);
Console.WriteLine("Record Created: {0}",response1.RecordCreated); //true

//Update the name of the existing account entity
account["name"] = "Updated Account";

//Use Upsert for the second time
UpsertRequest request2 = new UpsertRequest()
{
Target = account
};

//The existing entity is updated.
var response2 = (UpsertResponse)svc.Execute(request2);
Console.WriteLine("Record Created: {0}", response2.RecordCreated); //false

More information: Use Upsert to insert or update a record

Delete

The IOrganizationService.Delete method simply requires the logical name of the table and the unique identifier. Regardless of whether you're using late-bound Entity class or a generated early-bound class, you can use the following syntax for a delete operation by passing the Entity.LogicalName and Entity.Id properties.

svc.Delete(retrievedEntity.LogicalName, retrievedEntity.Id);

Or you can use the values:

svc.Delete("account", new Guid("e5fa5509-2582-e811-a95e-000d3af40ae7"));

Use the DeleteRequest class

You can use the DeleteRequest class instead of the IOrganizationService.Delete method, but it's only required when you want to specify optimistic concurrency behavior.

var retrievedEntity = new Entity("account")
{
    Id = new Guid("c81ffd82-cd82-e811-a95c-000d3af49bf8"),
    RowVersion = "986335"

};

var request = new DeleteRequest()
{
    Target = retrievedEntity.ToEntityReference(),
    ConcurrencyBehavior = ConcurrencyBehavior.IfRowVersionMatches
};

svc.Execute(request);

Optimistic concurrency behavior

You can specify the optimistic concurrency behavior for the operation by setting the ConcurrencyBehavior property of the UpdateRequest or DeleteRequest classes.

The logic to update or delete the row may be based on stale data. If the current data is different because it has changed since it was retrieved, optimistic concurrency provides a way to cancel an update or delete operation so you might retrieve it again and use the current data to determine whether to proceed.

To determine whether the row has been changed, you don't need to compare all the values, you can use the RowVersion property to see if it has changed.

The following example succeeds only when:

• The RowVersion of the row in the database equals 986323
• The account table is enabled for optimistic concurrency (EntityMetadata.IsOptimisticConcurrencyEnabled property is true)
• The RowVersion property is set on the row passed with the request.

If the RowVersion doesn't match, an error with the message The version of the existing record doesn't match the RowVersion property provided. occurs.

var retrievedAccount = new Account()
{   
    Id = new Guid("a976763a-ba1c-e811-a954-000d3af451d6"), 
    RowVersion = "986323" 
};

var account = new Account();
account.Id = retrievedAccount.Id;
account.RowVersion = retrievedAccount.RowVersion;

// set attribute values
account.CreditOnHold = true;

//Update the account
var request = new UpdateRequest()
{ 
    Target = account,
    ConcurrencyBehavior = ConcurrencyBehavior.IfRowVersionMatches 
};

try
{
    svc.Execute(request);
}
catch (FaultException<OrganizationServiceFault> ex)
{
    switch (ex.Detail.ErrorCode)
    {
        case -2147088254: // ConcurrencyVersionMismatch 
        case -2147088253: // OptimisticConcurrencyNotEnabled 
            throw new InvalidOperationException(ex.Detail.Message);
        case -2147088243: // ConcurrencyVersionNotProvided
            throw new ArgumentNullException(ex.Detail.Message);
        default:
            throw ex;
    }
}

More information:

• Optimistic Concurrency
• ConcurrencyBehavior Enum

Legacy update messages

There are several deprecated specialized messages that perform update operations. In earlier versions, it was required to use these messages, but now the same operations should be performed using IOrganizationService.Update or UpdateRequest class with IOrganizationService.Execute method.

<entity> refers to any entity that provides this attribute.

More information: Behavior of specialized update operations

See also

Create table rows using the SDK for .NET
Retrieve a table row using the SDK for .NET
Associate and disassociate table rows using the SDK for .NET