C# — query a Dataverse database

Everyone knows that a Dataverse database is reachable from Power Platform apps, flows and a robust set of Power Platform services. Not everyone knows that .NET offers a possibility to work with Dataverse data as well.

Today am going to show three flavors of querying a Dataverse table through C#.

• A Dataverse SDK,
• FetchXML,
• A standard SQL client.

There are two columns retrieved: crf8e_categoryname,

crf8e_categorynumber

from a crf8e_iowa_category table.

There are two filters applied: crf8e_categoryname like ‘America%’ and crf8e_categorynumber ≥ 1051010 ordered by crf8e_categorynumber ascending.

To make this simpler, here is a translation into a SQL query:

SELECT 
  [crf8e_categoryname], 
  [crf8e_categorynumber] 
FROM 
  [dbo].[crf8e_iowa_category] 
WHERE 
      [crf8e_categoryname] like 'America%' 
  AND [crf8e_categorynumber] >= 1051010 
ORDER BY 
  [crf8e_categorynumber] ASC

We need to install two packages as a prerequisite.

dotnet add package Microsoft.Data.SqlClient
dotnet add package Microsoft.PowerPlatform.Dataverse.Client

Access is assured by a Service Principal. So, the first part of the app provides Service Principal data and creates a ServiceClient object.

// A service principal client
string clientId = {Service Principal Client ID};
string clientSecret = {Service Principal Secret};
string dataverseEnvironment = {Power Platform Environment URL};
string dataverseDatabase = {Dataverse Database Name};

var connectionString = @$"Url=https://{dataverseEnvironment};AuthType=ClientSecret;ClientId={clientId};ClientSecret={clientSecret};RequireNewInstance=true";
var serviceClient = new ServiceClient(connectionString);

We are ready now. The first in line is the Dataverse SDK:

//A SDK query
QueryExpression querySdk = new QueryExpression("crf8e_iowa_category");
querySdk.ColumnSet.AddColumns("crf8e_categoryname", "crf8e_categorynumber");
querySdk.TopCount = 10;

querySdk.Criteria = new FilterExpression();
querySdk.Criteria.AddCondition("crf8e_categoryname", ConditionOperator.BeginsWith, "America");
querySdk.Criteria.AddCondition("crf8e_categorynumber", ConditionOperator.GreaterEqual, 1051010);

var accountsCollection = await serviceClient.RetrieveMultipleAsync(querySdk);

System.Console.WriteLine("-----SDK-----");
accountsCollection.Entities.ToList().ForEach(x =>
{
  Console.WriteLine($"{x.Attributes["crf8e_categoryname"]}, {x.Attributes["crf8e_categorynumber"]}");
});

The FetchXML:

// A FetchXML query
string fetchXml = @"
  <fetch top='50' >
    <entity name='crf8e_iowa_category' >
      <attribute name='crf8e_categoryname' />
      <attribute name='crf8e_categorynumber' />
      <filter type='and' >
        <condition 
          attribute='crf8e_categoryname' 
          operator='like' 
          value='America%' />
        <condition 
          attribute='crf8e_categorynumber' 
          operator='ge' 
          value='1051010' />
      </filter>
      <order attribute='crf8e_categorynumber' />
    </entity>
  </fetch>";

var queryFetchXml = new FetchExpression(fetchXml);
EntityCollection results = serviceClient.RetrieveMultiple(queryFetchXml);

System.Console.WriteLine("-----FetchXML-----");
results.Entities.ToList().ForEach(x =>
{
  Console.WriteLine($"{x.Attributes["crf8e_categoryname"]}, {x.Attributes["crf8e_categorynumber"]}");
});

And the last one, a standard SQL client through a Tabular Data Stream which emulates a SQL data connection:

//A SQL query
string ConnectionString = @$"Server={dataverseEnvironment}; Authentication=Active Directory Service Principal; Encrypt=True; Database={dataverseDatabase}; User Id={clientId}; Password={clientSecret}";

var cnn = new SqlConnection(ConnectionString);
cnn.Open();
string querySql = "SELECT [crf8e_categoryname], [crf8e_categorynumber] FROM [dbo].[crf8e_iowa_category] WHERE [crf8e_categoryname] like 'America%' AND [crf8e_categorynumber] >= 1051010 ORDER BY [crf8e_categorynumber] ASC";
SqlCommand sqlCommand = new SqlCommand(querySql, cnn);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

System.Console.WriteLine("-----SQL query-----");
while (sqlDataReader.Read())
{
  System.Console.WriteLine($"{sqlDataReader.GetString(0)}, {sqlDataReader.GetString(1)}");
}

Worth mentioning is a fact that both SQL and FetchXML options are read-only. The SDK goes beyond querying and is able to manipulate data e.g. INSERT/UPDATE/DELETE.

Here is an example of INSERT.

//SDK INSERT
var iowaCategory = new Entity("crf8e_iowa_category");
iowaCategory["crf8e_categorynumber"] = "55555";
iowaCategory["crf8e_categoryname"] = "Polish Vodka";

serviceClient.Create(iowaCategory);

Here is source code at GitHub: Dataverse_Csharp