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