Being able to perform CRUD (Create/Read/Update/Delete) Operations in Dataverse is a fundamental task you’ll likely be doing a lot when working with the Web API. I’ll show you some common practical examples of how to perform this.
This is a continuation in my series on using the Dataverse Web API and PowerShell scripting. If you haven’t already, please read my post on Obtaining an Access Token, as you will need to know how to do this first.
For my examples, I will be using the Dataverse Contacts Table because it is a standard table every Dataverse environment has so you can follow along.
Web Request Basics
Every web request will consist of a URI, Headers, and a Method. Additionally, depending on the method type, you may also have a body.
Getting the URI for your your table endpoint will look something this:
https://YOURENVIRONMENTNAME.crm.dynamics.com/api/data/v9.2/TABLENAME
You’ll want to get your environment name by going to the PowerApps Admin Center, selecting the environment you are working in, and copying the Environment URL listed in the Details section.
For the table name, this is going to be the logical name of your tables. You’ll want to go to Tables, search for your table, and under the table properties, click tools > Copy Logical Name.
For the header, this is going to vary somewhat depending on the type of metadata we want to pass with the request. The header will always need a valid Bearer Token. From my post on Obtaining an Access Token, I had created a PowerShell Function that requests this and returns it to my script.
$Header = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)" # This is populated from the Access Token Function Script
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
The method is nothing more than the type of request we are doing. There are many method types like GET, POST, PATCH, and DELETE.
In PowerShell, the cmdlet that actually makes the request is Invoke-RestMethod. We’ll see this in action next.
In our header, we are using the option “Prefer” = “return=representation”. This allows an actual response of the data to pass back to us. If we don’t specify this, the response will be blank. For the examples below, we want a response back because we are going to use the output later.
The Get Request
In this first example, I am getting all contacts and returning the data back into a variable called $ContactsResponse which has automatically been parsed into an object PowerShell can work with. I could do something like export it to a csv file for example:
# Get Contacts and export to csv File
$ExportFileName = "MyContacts.csv"
# Export Path for Current Directory
$ExportPath = Join-Path $($pwd.Path) -ChildPath $ExportFileName
# Dot Source Access Token Script
. .\GetDataverseAccessToken.ps1
$Params =@{
ClientId = "CLIENT ID HERE"
TenantId = "TENANT ID HERE"
ClientSecret = "CLIENT SECRET HERE"
EnvironmentURL = "https://ORGNAMEHERE.crm.dynamics.com/"
}
# Get Access Token
$authResponseObject = Get-DataverseAccessToken @Params
$Header = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
$URI = "$($params.EnvironmentURL)/api/data/v9.2/contacts"
# Fetch Contacts
$ContactResponse = (Invoke-RestMethod -Uri $URI -Headers $Header -Method Get).value
# Ouput the results and export to CSV File
$ContactResponse | Export-Csv $ExportPath -NoTypeInformation
A couple things to consider that’s unique for the contacts table. There are many…many columns within the contacts table. It’s a good practice to only fetch the columns you really need to greatly improve performance. I can use the $Select and $Top query parameters to limit the results. Get me the top 5 contacts, and return the FullName, and Email columns only.
For PowerShell, you’ll have to use a ` character whenever you want to treat the $ character as a string literal otherwise PowerShell will treat it as a variable and throw an error.
$URI = "$($params.EnvironmentURL)/api/data/v9.2/contacts?`$select=fullname,emailaddress1&`$top=5"
Even if you specify specific columns, the unique indentifer and odata.etag columns will be part of the response. You can further refine the columns before export to csv using Select-Object.
Not too bad so far, right? Alright…let’s take the difficulty up a notch.
The Create Request
Let’s create a single Contact in our Contacts Table. This time, we’ll be passing a body with our request. This is nothing more than the column names and the values we want to pass. The column names, like the table name, need to be the logical names of the columns. Go under your Table > Columns. Under Advanced Tools, Make sure you copy the column’s logical name. It is case sensitive!
In the example below, we are populating the First Name, Last Name, and Email for the contact Mickey Mouse
# Create Contact
$CreateURI = "$($params.EnvironmentURL)/api/data/v9.2/contacts"
$CreateBody = @{
"emailaddress1" = "[email protected]"
"firstname" = "Mickey"
"lastname" = "Mouse"
} | ConvertTo-Json
$CreateResponse = Invoke-RestMethod -Uri $CreateURI -Body $CreateBody -Method Post -Headers $Header
Finally, I store the response in the $CreateResponse variable. This is important because we want to get the unique identifier for the record we just created to use for later. Using $CreateResponse.contactid gets us the guid we need.
We can append ?`$select=contactid to the end of the URI to retrieve only the contactid, rather than all contact columns. This will help speed up your script, especially if you’re looping through a large list of contacts to create, for example.
The Update Request
Now that we’ve done the create, let’s say we need to update Mickey’s email. We’ll need to the pass the email address property and on the URI endpoint, reference the guid for contact record. The method becomes Patch instead of Post.
# Update Contact
$UpdateURI = "$($params.EnvironmentURL)/api/data/v9.2/contacts($($CreateResponse.contactid))"
$ContactBodyUpdate = @{
"emailaddress1" = "[email protected]"
} | ConvertTo-Json
$UpdateResponse = Invoke-RestMethod -uri $UpdateURI -Method Patch -Body $ContactBodyUpdate -Headers $Header
The Delete Request
The delete request involves changing the method to Delete and specifying the specific guid of the record. No data will be returned upon deletion, so I don’t store the response in a variable this time.
# Delete Contact $DeleteURI = "$($params.EnvironmentURL)/api/data/v9.2/contacts($($CreateResponse.contactid))" Invoke-RestMethod -uri $DeleteURI -Method Delete -Headers $Header
Putting it all Together
I’m going to loop through a pscustomobject array of contacts to create, then update the email address, and finally delete the last contact created.
# Look through the $ContactsToCreate and Perform CRUD operations with the Dataverse Web API
$ContactsToCreate = @(
[pscustomobject]@{
emailaddress1 = "[email protected]"
firstname = "Mickey"
lastname = "Mouse"
},
[pscustomobject]@{
emailaddress1 = "[email protected]"
firstname = "Goofy"
lastname = "Goof"
},
[pscustomobject]@{
emailaddress1 = "[email protected]"
firstname = "Donald"
lastname = "Duck"
}
)
# Dot Source Access Token Script
. .\GetDataverseAccessToken.ps1
$Params =@{
ClientId = "CLIENT ID HERE"
TenantId = "TENANT ID HERE"
ClientSecret = "CLIENT SECRET HERE"
EnvironmentURL = "https://ORGNAMEHERE.crm.dynamics.com/"
}
# Get Access Token
$authResponseObject = Get-DataverseAccessToken @Params
$Header = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
$BaseURI = "$($params.EnvironmentURL)api/data/v9.2/contacts"
# Create the Contacts
foreach ($Contact in $ContactsToCreate) {
# Construct Create URI and New Email
$CreateURI = $BaseURI + "?`$select=contactid,firstname"
$NewEmail = "$($Contact.firstname)$($Contact.lastname)@updatedemail.com"
# Construct the body for contact creation
$CreateBody = @{
emailaddress1 = $Contact.emailaddress1
firstname = $Contact.firstname
lastname = $Contact.lastname
} | ConvertTo-Json
try {
# Make API call to create the contact
Write-Host "Creating contact $($Contact.firstname)"
$CreateResponse = Invoke-RestMethod -Uri $CreateURI -Body $CreateBody -Method Post -Headers $Header
# Construct Update URI for the newly created contact
$UpdateURI = $BaseURI + "($($CreateResponse.contactid))"
# Prepare the update body
$ContactBodyUpdate = @{
emailaddress1 = $NewEmail
} | ConvertTo-Json
# Make API call to update the contact
$UpdateResponse = Invoke-RestMethod -Uri $UpdateURI -Method Patch -Body $ContactBodyUpdate -Headers $Header
# Output the result
Write-Host "Updated $($Contact.firstname)'s email to $($UpdateResponse.emailaddress1)"
}
catch {
Write-Error "Error processing contact $($Contact.firstname): $_"
}
}
# Delete the Most Recent Contact Created
if ($CreateResponse.contactid) {
$DeleteURI = $BaseURI + "($($CreateResponse.contactid))"
try {
Invoke-RestMethod -Uri $DeleteURI -Method Delete -Headers $Header
Write-Host "Deleted contact $($CreateResponse.firstname)"
}
catch {
Write-Error "Error deleting contact $($CreateResponse.contactid): $_"
}
}
And that’s that for this lesson! Hopefully this get you thinking about your own use-cases.
