![\"\"](\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/02\/Retrieving-Dynamics365-Data-Using-SQL-in-Plugin.png\")
<\/p>\n<\/p>\n
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.<\/p>\n
Why Use SQL for Retrieving D365 Data? <\/strong><\/p>\n Familiar Syntax<\/strong>: If you know SQL, we can easily write queries to fetch data without learning additional APIs or formats.<\/p>\n Efficiency<\/strong>: SQL is optimized for querying large datasets and complex conditions.<\/p>\n Flexibility<\/strong>: SQL queries allow for aggregations, filtering, and joins in a single step.<\/p>\n Note: The TDS endpoint can’t be used with elastic tables.<\/strong><\/p>\n This guide demonstrates how to achieve this using the TDS endpoint in C#.<\/p>\n Prerequisites<\/strong><\/p>\n 1. Enable TDS Endpoint<\/strong>: Ensure the TDS endpoint is enabled for your Dataverse environment.<\/p>\n 2. Install Required Libraries<\/strong>:<\/p>\n 3. Credentials<\/strong>:<\/p>\n 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.<\/p>\n <\/li>\n<\/ol>\n<\/li>\n<\/ol>\n SELECT TOP 3\u00a0\u00a0\u00a0 name AS AccountName, revenue AS Revenue FROM account ORDER BY revenue DESC;<\/span><\/p>\n Error Handling<\/strong>:<\/p>\n Testing the Custom API with XrmToolBox<\/strong> To verify the API implementation, we will use the Custom API Tester<\/strong> tool available in XrmToolBox. This tool allows us to test the functionality of our API in a controlled environment. Steps to Test:<\/strong><\/p>\n 1. Connect to Your Environment<\/strong>:<\/p>\n 2. Locate Custom API Tester<\/strong>:<\/p>\n 3. Provide API Details<\/strong>:<\/p>\n <\/p>\n SELECT TOP 3 name AS AccountName,<\/strong> \u00a0 <\/strong><\/p>\n revenue AS Revenue<\/strong><\/p>\n FROM account<\/strong><\/p>\n ORDER BY revenue DESC;<\/strong><\/p>\n 4. Execute the API<\/strong>:<\/p>\n 5. Validate Results<\/strong>:<\/p>\n Things to Keep in Mind<\/strong><\/p>\n 1. Read-Only<\/strong>: The TDS endpoint only supports SELECT<\/strong> queries. No INSERT, UPDATE, or DELETE operations.<\/p>\n 2. Logical Names<\/strong>: Use the logical names of the tables and columns (e.g., accountid instead of Account ID).<\/p>\n 3. Efficient Data Retrieval<\/strong>: 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.<\/p>\n The TDS endpoint bridges the gap between Dataverse and traditional SQL-based tools. By following the scenario above, you\u2019ve seen how easy it is to integrate Custom API with the TDS endpoint for practical business needs. Whether you\u2019re fetching data for reporting or integrating with other systems, the TDS endpoint offers a powerful solution for Dynamics 365 developers.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" 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\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":15,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16,2361],"tags":[3111],"class_list":["post-40363","post","type-post","status-publish","format-standard","hentry","category-dynamics-365","category-technical","tag-retrieving-dynamics365-data-using-sql-in-plugin"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/40363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=40363"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/40363\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=40363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=40363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=40363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
\n
![\"Retrieving](\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/02\/1Retrieving-Dynamics365-Data-Using-SQL.png\")
<\/p>\n\n
\n
\n
\n
using Microsoft.Xrm.Sdk;\r\nusing Microsoft.Xrm.Sdk.Extensions;\r\n\r\nusing System;\r\n\r\nusing System.Collections.Generic;\r\n\r\nusing System.Data;\r\n\r\nusing System.Data.SqlClient;\r\n\r\nusing System.Linq;\r\n\r\nusing System.Net.Http;\r\n\r\nusing System.Text.Json;\r\n\r\n\u00a0\r\n\r\nnamespace TdsEndpoint\r\n\r\n{\r\n\r\npublic class TDSEndpoint : IPlugin\r\n\r\n{\r\n\r\npublic void Execute(IServiceProvider serviceProvider)\r\n\r\n{\r\n\r\nITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));\r\n\r\nIPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));\r\n\r\n\/\/string fieldName = (string)context.InputParameters[\"cr172_Input\"];\r\n\r\nIOrganizationServiceFactory serviceFactory =\r\n\r\n(IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));\r\n\r\nIOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);\r\n\r\ntry\r\n\r\n{\r\n\r\nvar sqlQuery = context.InputParameterOrDefault<string>(\"SqlQuery\");\r\n\r\n\u00a0\r\n\r\nif (string.IsNullOrEmpty(sqlQuery))\r\n\r\n{\r\n\r\nthrow new ArgumentException(\"SqlQuery parameter is required.\");\r\n\r\n}\r\n\r\n\u00a0\r\n\r\nvar token = GetAccessToken();\r\n\r\nvar result = GetData(sqlQuery, tracingService);\r\n\r\nvar lst = result.AsEnumerable()\r\n\r\n.Select(r => r.Table.Columns.Cast<DataColumn>()\r\n\r\n.Select(c => new KeyValuePair<string, object>(c.ColumnName, r[c.Ordinal])\r\n\r\n).ToDictionary(z => z.Key, z => z.Value)\r\n\r\n).ToList();\r\n\r\ncontext.OutputParameters[\"Result\"] = JsonSerializer.Serialize(lst);\r\n\r\n}\r\n\r\n\u00a0\r\n\r\ncatch (Exception ex)\r\n\r\n{\r\n\r\ntracingService.Trace(\"Ex \" + ex.Message);\r\n\r\n}\r\n\r\n}\r\n\r\n\u00a0\r\n\r\npublic string GetAccessToken()\r\n\r\n{\r\n\r\nvar client = new HttpClient();\r\n\r\nvar request = new HttpRequestMessage(HttpMethod.Post, \"https:\/\/login.microsoftonline.com\/your-tenant-id\/oauth2\/token\");\r\n\r\nvar collection = new Dictionary<string, string>\r\n\r\n{\r\n\r\n[\"grant_type\"] = \"client_credentials\",\r\n\r\n[\"client_id\"] = \"client-id\",\r\n\r\n[\"client_secret\"] = \"client-secret\",\r\n\r\n[\"resource\"] = \"https:\/\/your-crm-url.crm.dynamics.com\"\r\n\r\n};\r\n\r\nvar content = new FormUrlEncodedContent(collection);\r\n\r\nrequest.Content = content;\r\n\r\nvar response = client.SendAsync(request).Result;\r\n\r\nresponse.EnsureSuccessStatusCode();\r\n\r\nvar result = response.Content.ReadAsStringAsync().Result;\r\n\r\n\u00a0\r\n\r\nreturn ExtractAccessToken(result);\r\n\r\n}\r\n\r\n\u00a0\r\n\r\npublic string ExtractAccessToken(string jsonResponse)\r\n\r\n{\r\n\r\ntry\r\n\r\n{\r\n\r\nvar tokenResponse = JsonSerializer.Deserialize<TokenResponse>(jsonResponse);\r\n\r\nif (tokenResponse != null && !string.IsNullOrEmpty(tokenResponse.access_token))\r\n\r\n{\r\n\r\nreturn tokenResponse.access_token;\r\n\r\n}\r\n\r\nelse\r\n\r\n{\r\n\r\nthrow new ArgumentException(\"Access token not found in the JSON response.\");\r\n\r\n}\r\n\r\n}\r\n\r\ncatch (Exception ex)\r\n\r\n{\r\n\r\nthrow new ArgumentException(\"Error deserializing JSON response.\", ex);\r\n\r\n}\r\n\r\n}\r\n\r\n\u00a0\r\n\r\nprivate DataTable GetData(string sqlQuery , string token, ITracingService tracingService)\r\n\r\n{\r\n\r\nvar sqlConn = \"Server=sssuserinterface.crm8.dynamics.com,5558;Database=sssuserinterface;Encrypt=True;TrustServerCertificate=False;Persist Security Info=False\";\r\n\r\n\u00a0\r\n\r\nusing (var conn = new SqlConnection(sqlConn))\r\n\r\n{\r\n\r\nconn.AccessToken = token;\r\n\r\nconn.Open();\r\n\r\n\u00a0\r\n\r\nvar cmd = conn.CreateCommand();\r\n\r\ncmd.CommandText = sqlQuery;\r\n\r\nvar reader = cmd.ExecuteReader();\r\n\r\nvar dataTable = new DataTable();\r\n\r\ndataTable.Load(reader);\r\n\r\nconn.Close();\r\n\r\n\u00a0\r\n\r\nreturn dataTable;\r\n\r\n}\r\n\r\n}\r\n\r\n}\r\n\r\n\u00a0\r\n\r\npublic class TokenResponse\r\n\r\n{\r\n\r\npublic string access_token { get; set; }\r\n\r\n}\r\n\r\n}<\/pre>\nBreaking Down the Code<\/h3>\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
![\"\"](\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2025\/02\/2Retrieving-Dynamics365-Data-Using-SQL.png\")
<\/p>\n\n
\n
Conclusion<\/strong><\/h2>\n