In Dynamics 365, the TDS (Tabular Data Stream) endpoint allows developers to query Dataverse data using SQL-like commands. For example, suppose a sales manager wants to find the top three accounts with the highest revenue. Here’s how they can use the TDS endpoint to get that data.
Why Use SQL for Retrieving D365 Data?
Familiar Syntax: If you know SQL, we can easily write queries to fetch data without learning additional APIs or formats.
Efficiency: SQL is optimized for querying large datasets and complex conditions.
Flexibility: SQL queries allow for aggregations, filtering, and joins in a single step.
Note: The TDS endpoint can’t be used with elastic tables.
This guide demonstrates how to achieve this using the TDS endpoint in C#.
Prerequisites
1. Enable TDS Endpoint: Ensure the TDS endpoint is enabled for your Dataverse environment.
-
- Go to Power Platform Admin Center > Environments > Select your environment.
- Under Settings, enable the TDS endpoint.
2. Install Required Libraries:
-
- Install the Microsoft.Data.SqlClient NuGet package to connect to the TDS endpoint. dotnet adds the package – Microsoft.Data.SqlClient
3. Credentials:
-
- Use your Dataverse credentials or OAuth tokens to authenticate.
In this implementation, I have created a custom API that accepts a single input parameter, SqlQuery allowing users to pass any valid SQL query for execution. The API processes this query using the TDS (Tabular Data Stream) endpoint and returns the result as an output parameter in JSON format. This structured response makes it easier for consuming applications to parse and utilize the data effectively.
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Http;
using System.Text.Json;
namespace TdsEndpoint
{
public class TDSEndpoint : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
//string fieldName = (string)context.InputParameters["cr172_Input"];
IOrganizationServiceFactory serviceFactory =
(IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
try
{
var sqlQuery = context.InputParameterOrDefault<string>("SqlQuery");
if (string.IsNullOrEmpty(sqlQuery))
{
throw new ArgumentException("SqlQuery parameter is required.");
}
var token = GetAccessToken();
var result = GetData(sqlQuery, tracingService);
var lst = result.AsEnumerable()
.Select(r => r.Table.Columns.Cast<DataColumn>()
.Select(c => new KeyValuePair<string, object>(c.ColumnName, r[c.Ordinal])
).ToDictionary(z => z.Key, z => z.Value)
).ToList();
context.OutputParameters["Result"] = JsonSerializer.Serialize(lst);
}
catch (Exception ex)
{
tracingService.Trace("Ex " + ex.Message);
}
}
public string GetAccessToken()
{
var client = new HttpClient();
var request = new HttpRequestMessage(HttpMethod.Post, "https://login.microsoftonline.com/your-tenant-id/oauth2/token");
var collection = new Dictionary<string, string>
{
["grant_type"] = "client_credentials",
["client_id"] = "client-id",
["client_secret"] = "client-secret",
["resource"] = "https://your-crm-url.crm.dynamics.com"
};
var content = new FormUrlEncodedContent(collection);
request.Content = content;
var response = client.SendAsync(request).Result;
response.EnsureSuccessStatusCode();
var result = response.Content.ReadAsStringAsync().Result;
return ExtractAccessToken(result);
}
public string ExtractAccessToken(string jsonResponse)
{
try
{
var tokenResponse = JsonSerializer.Deserialize<TokenResponse>(jsonResponse);
if (tokenResponse != null && !string.IsNullOrEmpty(tokenResponse.access_token))
{
return tokenResponse.access_token;
}
else
{
throw new ArgumentException("Access token not found in the JSON response.");
}
}
catch (Exception ex)
{
throw new ArgumentException("Error deserializing JSON response.", ex);
}
}
private DataTable GetData(string sqlQuery , string token, ITracingService tracingService)
{
var sqlConn = "Server=sssuserinterface.crm8.dynamics.com,5558;Database=sssuserinterface;Encrypt=True;TrustServerCertificate=False;Persist Security Info=False";
using (var conn = new SqlConnection(sqlConn))
{
conn.AccessToken = token;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = sqlQuery;
var reader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
conn.Close();
return dataTable;
}
}
}
public class TokenResponse
{
public string access_token { get; set; }
}
}
Breaking Down the Code
-
- Connection String:
-
- Server: Enter your TDS endpoint and use port 5558.
-
- Database: Enter the name of your Dataverse database (usually your organization name).
-
- Authentication: Use Active Directory authentication methods, such as Active Directory Interactive, for a secure connection.
-
- SQL Query:
-
- Retrieves the top three accounts from the account table, sorted by highest to lowest revenue.
-
- SQL Query:
SELECT TOP 3 name AS AccountName, revenue AS Revenue FROM account ORDER BY revenue DESC;
Error Handling:
-
- Wrap the logic in try-catch blocks to handle connection or query execution issues gracefully
Testing the Custom API with XrmToolBox To verify the API implementation, we will use the Custom API Tester tool available in XrmToolBox. This tool allows us to test the functionality of our API in a controlled environment. Steps to Test:
1. Connect to Your Environment:
-
- Open XrmToolBox and connect it to your Dynamics 365 environment.
2. Locate Custom API Tester:
-
- Search for “Custom API Tester” in the tool list and open it.
3. Provide API Details:
-
- Enter the name of your Custom API.
-
- Specify the required input parameter, SqlQuery, with a sample query, such as:
SELECT TOP 3 name AS AccountName,
revenue AS Revenue
FROM account
ORDER BY revenue DESC;
4. Execute the API:
-
- Run the test and observe the response.
-
- The output should display the JSON-formatted data returned by your API.
5. Validate Results:
-
- Verify that the results match the expected output.
Things to Keep in Mind
1. Read-Only: The TDS endpoint only supports SELECT queries. No INSERT, UPDATE, or DELETE operations.
2. Logical Names: Use the logical names of the tables and columns (e.g., accountid instead of Account ID).
3. Efficient Data Retrieval: SQL is optimized for handling complex queries and large volumes of data, which can greatly enhance the performance of data retrieval tasks. I have also included the LIST result format, which can be stringified. This makes it easier to work with in modern applications, ensuring the data is portable for APIs, logs, or UIs.
Conclusion
The TDS endpoint bridges the gap between Dataverse and traditional SQL-based tools. By following the scenario above, you’ve seen how easy it is to integrate Custom API with the TDS endpoint for practical business needs. Whether you’re fetching data for reporting or integrating with other systems, the TDS endpoint offers a powerful solution for Dynamics 365 developers.
