Understanding CRUD Operations in Dataverse with a PowerShell Script

Accessing Dataverse using PowerShell is helpful while data migration. This blog will provide you with the necessary links for creating and registering an app in Azure Active Directory (AD), as well as adding the app user to your environment. We’ll also go through how to use PowerShell to create, read, update, and delete a record from a Dataverse Table.

Before you begin writing your PowerShell script, there are a few requirements you need to meet. Let’s review them.

App Registration within Azure AD

The first thing is to register an app within Azure (AD) for Dataverse. Follow the instructions in this Microsoft article.

Generate the Client Secret after App Registration

Once we have done the app registration, we then need to generate a client secret to get the authorization token. Follow the instructions in this Microsoft article to learn how to do this.

Add App User and Set Roles in Dataverse Environment

During an app registration, we can add an application user to the Dataverse environment. We’ll then configure the expected roles for the app users. You can do this by following the directions in this Microsoft article.

Set Privileges to the Security Roles in the Dataverse Environment

After setting the security role(s), make sure the security role has privileges such as create, read, write, and delete, and so on for the table that you are going to use. Then perform the following steps to assign privileges:

• Go to the Power Apps Site as an administrator.
• Select the environment you would like to administer.
• Select “Settings” on the navigation bar.
• Select “Advanced Settings.”
• From “Settings” click “Security.”
• Click “Security Roles.”
• Select the security role that you assigned to the app user.

Select each tab in the security role designer to locate the tables that your app uses. Your custom tables will be available under the “Custom Entities” tab.

Once you have located your tables, choose the privileges you want to grant your users, such as create, read, write, delete, and so on. Select the scope for performing that action by selecting the name of the table. Scope decides how deep or high within the environment’s hierarchy the user can perform a particular action.

Select “save” and “close.”

PowerShell Script

We can use the PowerShell script below to perform CRUD operations on the Dataverse table by leveraging Azure AD.  If you want to view the below script on GitHub, click here. Here you will get scripts for all four operations: create, read, update, and delete.

To understand setting parameters, see below:

$TenantId = 'xxxxxxxxxxx'    
    #The Directory (tenant) ID of the App registration 

$AppId = 'xxxxxxxxxxx'   
    #The Application (client) ID of the App registration 

$ClientSecret = 'xxxxxxxxxxx '   
    #The client secret generated within the App registration 

$PowerPlatformOrg = 'xxxxxxxxxxx'    
    #Dynamics 365 Organization ID / YourEnvironmentId

$PowerPlatformEnvironmentUrl = "https://$($PowerPlatformOrg).crm8.dynamics.com" 
    #The URL of the Dataverse environment you want to connect to perform CRUD Operation

$oAuthTokenEndpoint = "https://login.microsoftonline.com/$($TenantId)/oauth2/v2.0/token" 
    <# The “v2 OAuth” endpoint is for the App registration. You’ll want to open the app registration and click the “endpoints” button 
       in the overview area to find it. Then, copy the “OAuth 2.0” token endpoint (v2) URL. #>

To generate the access token, see below:  

# OAuth Body Access Token Request
$authBody = @{
    client_id = $AppId;
    client_secret = $ClientSecret;    
    # The v2 endpoint for OAuth uses scope instead of resource
    scope = "$($PowerPlatformEnvironmentUrl)/.default"    
    grant_type = 'client_credentials'
}

# Parameters for OAuth Access Token Request
$authParams = @{
    URI = $oAuthTokenEndpoint
    Method = 'POST'
    ContentType = 'application/x-www-form-urlencoded'
    Body = $authBody
}
# Get Access Token
$authResponseObject = Invoke-RestMethod @authParams -ErrorAction Stop
# Output
$authResponseObject

To read data from the Dataverse table, see below:

$getDataRequestUri = 'accounts?$top=5&$select=name,accountid';
# Set up web API call parameters, including a header for the access token
$getApiCallParams = @{
    URI = "$($PowerPlatformEnvironmentUrl)/api/data/v9.1/$($getDataRequestUri)"
    Headers = @{
        "Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
        "Accept" = "application/json"
        "OData-MaxVersion" = "4.0"
        "OData-Version" = "4.0"
    }
    Method = 'GET'
}

# Call API to Get Response
$getApiResponseObject = Invoke-RestMethod @getApiCallParams -ErrorAction Stop
# Output
$getApiResponseObject.value

If you’re interested in exploring other CRUD operations besides the “Read” operation, you must visit this GitHub repository here.

A Final Word

I hope that this article has given you enough knowledge you need to get started with CRUD operations in Dataverse with the help of a PowerShell script. Please feel free to share your thoughts in the comment section below.

For more information, contact our commerce experts today.