Dataverse SQL – Custom API

In a previous post I covered how to create an Azure Function to run Dataverse SQL queries if SQL traffic is blocked on your network. This post is a variation on that and shows how you can basically use the same code in a Dataverse Plug-in and expose it as a Custom API. Using a Custom API has advantages over using an Azure Function

• Restrict access to the Custom API using Dataverse Authentication
• No need to deploy Azure components
• All the components can be packaged in a Power Platform solution for deployment.
• Run Dataverse SQL queries from Power Automate by calling the Custom API
• Use Dataverse plugin trace log records

Here is the main code from my plugin it grabs an input parameter called sqlquery and use this to run a SQL query against Dataverse. The rest of the code it is the same as my previous post

public class CustomAPIDataverseSQL : IPlugin
{
    public void Execute(IServiceProvider serviceProvider)
    {
        ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
        tracingService.Trace("Custom API - Dataverse SQL Plugin Started");

        IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
        IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
        IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

        var orgRequest = new RetrieveCurrentOrganizationRequest();
        var orgResponse = (RetrieveCurrentOrganizationResponse)service.Execute(orgRequest);
        string environmentUrl = orgResponse.Detail.Endpoints.Where(e => e.Key == EndpointType.WebApplication).FirstOrDefault().Value;
        tracingService.Trace("Dataverse Environment URL: " + environmentUrl);
        SqlApiCrendentials cred = GetSqlApiCrendentials(); 
        string sqlQuery = context.InputParameters["sqlquery"].ToString();
        string tok = GetToken(environmentUrl, tracingService, cred.TenantId, cred.ClientId, cred.Secret);
        context.OutputParameters["sqlresults"] = QuerySqlServer(environmentUrl, tok, sqlQuery, tracingService);

    }
    private static string QuerySqlServer(string environmentUrl, string accessToken, string query, ITracingService tracingService)
    {
        tracingService.Trace("QuerySqlServer: " + query);
        string database = environmentUrl.Replace("https://", "").TrimEnd('/');
         string result;
        string connectionString = $"Server={database}; Encrypt=True";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.AccessToken = accessToken;
            connection.Open();

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    DataTable dataTable = new DataTable();
                    dataTable.Load(reader);
                    result = ConvertDataTableToJson(dataTable, tracingService);
                    tracingService.Trace(result);
                }
            }
        }
        return result;
    }

Once I deployed my .NET Plug-in dll into the Dataverse environment I added it so a solution and used a great XrmToolBox tool called Custom API Manager to create the API and its request and response parameters.

When the API is created use the “Open in API Tester” option to test the API. Set the sqlquery request parameters and execute the API. The results are returned in a parameters called sqlresults in Json format.

Not all tools can connect to the Dataverse SQL endpoint, and Power Automate is one of them. However, you can execute an unbound action to call a Custom API to run your SQL queries as shown.

I hope you found this post interesting.