/
SQL 4 CDS Main.dib
153 lines (141 loc) · 6.91 KB
/
SQL 4 CDS Main.dib
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
#!csharp
#r "nuget:MarkMpn.Sql4Cds.Engine"
#r "nuget:Microsoft.PowerPlatform.Dataverse.Client"
#r "nuget:Azure.Identity"
#r "nuget:System.Text.Json"
#!csharp
using MarkMpn.Sql4Cds.Engine;
using static System.Console;
using System.Data;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using Azure.Core;
using Azure.Identity;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Crm.Sdk.Messages;
using System.Runtime.Caching;
using Microsoft.DotNet.Interactive;
using System.CommandLine;
using System.CommandLine.Invocation;
using Microsoft.DotNet.Interactive.Commands;
using System.Text.Json;
#!csharp
var lastResults = string.Empty;
private async Task<string> RunSQL(string query, string environment = "", string connectionString = "")
{
ObjectCache cache = MemoryCache.Default;
using var con = string.IsNullOrEmpty(connectionString) ?
new Sql4CdsConnection(
new ServiceClient(
tokenProviderFunction: async f => await GetToken(environment, new DefaultAzureCredential(), cache),
useUniqueInstance: true,
instanceUrl: new Uri(environment)))
: new Sql4CdsConnection(connectionString);
using var cmd = con.CreateCommand();
// Get the IDs of all accounts with the same name
cmd.CommandText = query;
var table = new List<Dictionary<string,object>>();
using var reader = await cmd.ExecuteReaderAsync();
int rowCount = 1;
while (await reader.ReadAsync())
{
var rows = new Dictionary<string,object>();
for (var i = 0; i < reader.FieldCount; i++)
{
if(i == 0)
rows["#"] = rowCount++;
rows[reader.GetName(i)] = reader.GetValue(i);
}
table.Add(rows);
}
lastResults = JsonSerializer.Serialize(table, options: new JsonSerializerOptions { WriteIndented = true });
if(table.Any())
{
// var markDownResult = new StringBuilder();
// markDownResult.AppendLine($@"| {string.Join(" | ", table[0].Keys)} |");
// markDownResult.AppendLine($@"| {string.Join(" | ", table[0].Keys.Select(k => "-"))} |");
// markDownResult.AppendLine($@"{string.Join($"{Environment.NewLine}", table.Select(r => $@"| {string.Join(" | ", r.Values)} |"))}");
// markDownResult.ToString().DisplayAs("text/markdown");
//https://marketplace.visualstudio.com/items?itemName=RandomFractalsInc.vscode-data-table has no issues with CSV after reload.
var csvResult = new StringBuilder();
csvResult.AppendLine($@"{string.Join(",", table[0].Keys.Select(k=>$@"""{k}"""))}");
csvResult.AppendLine($@"{string.Join($"{Environment.NewLine}", table.Select(r => $@"{string.Join(",", r.Values.Select(v => $@"""{v}"""))}"))}");
csvResult.ToString().DisplayAs("text/csv");
//https://marketplace.visualstudio.com/items?itemName=RandomFractalsInc.vscode-data-table does not render JSON after reloading the notebook. So, cannot use Flat Data Grid Renderer with JSON output.
// results.DisplayAs("application/json");
}
else
{
"<h3>No results</h3>".DisplayAs("text/html");
}
return lastResults;
}
private async Task<string> GetToken(string environment, DefaultAzureCredential credential, ObjectCache cache)
{
//TokenProviderFunction is called multiple times, so we need to check if we already have a token in the cache
var accessToken = cache.Get(environment);
if (accessToken == null)
{
accessToken = (await credential.GetTokenAsync(new TokenRequestContext(new[] { $"{environment}/.default" })));
cache.Set(environment, accessToken, new CacheItemPolicy { AbsoluteExpiration = DateTimeOffset.UtcNow.AddMinutes(50) });
}
return ((AccessToken)accessToken).Token;
}
#!csharp
var environmentOption = new Option<string>("--environment", "Environment URL to connect to e.g. https://org.crm.dynamics.com");
environmentOption.AddAlias("-e");
var connectionString = new Option<string>("--connectionString", "Connection string for the Dataverse environment");
connectionString.AddAlias("-c");
var runSQLCommand = new Command("#!dataverse-sql", "Run a SQL query against Dataverse using SQL4CDS"){ environmentOption, connectionString };
runSQLCommand.SetHandler((string environmentValue, string connectionStringValue) =>
{
//Using AddMiddleware and parsing parsing SQL because passing the query using magic command params feels clunky.
Kernel.Root.AddMiddleware(async (command, context, next) =>
{
if (command is SubmitCode submitCode && submitCode.TargetKernelName == "sql")
{
var originalCode = (submitCode.Parent as SubmitCode).Code.Replace(submitCode.Code,"").Replace(@"""","");
var environmentUrlIndex = originalCode.IndexOf("-e");
var connectionStringIndex = originalCode.IndexOf("-c");
if(environmentUrlIndex > -1)
environmentValue = originalCode.Substring(environmentUrlIndex).Replace(@"-e","").Trim();
if(connectionStringIndex > -1)
connectionStringValue = originalCode.Substring(connectionStringIndex).Replace(@"-c","").Trim();
await RunSQL(submitCode.Code, environmentValue, connectionStringValue);
}
else
{
await next(command, context);
}
});
}, environmentOption, connectionString);
Kernel.Root.AddDirective(runSQLCommand);
@"<div>
<p>
<code>#!dataverse-sql</code> magic command added.
</p>
<p>Use <code>#!dataverse-sql</code> <i>-e</i> ""[Dataverse environment Url]""<br>
or <code>#!dataverse-sql</code> <i>-c</i> ""[Dataverse environment Connection String]"".</p>
<p>If <i>-c</i> parameter is not specified, SQL 4 CDS will connect using Azure authentication(Azure CLI, Visual Studio Code, Azure PowerShell</p>
<p>Examples</p>
<p>
<code>
#!dataverse-sql -e https://org.crm.dynamics.com<br>
SELECT TOP 10 solutionid, friendlyname, uniquename FROM Solution
</code>
</p>
<p>
<code>
#!dataverse-sql -c ""AuthType=OAuth; Url=https://org.crm.dynamics.com; AppId=51f81489-12ee-4a9e-aaae-a2591f45987d; RedirectUri=http://localhost; TokenCacheStorePath=.\tokencache; LoginPrompt=Auto""<br>
SELECT TOP 10 solutionid, friendlyname, uniquename FROM Solution
</code>
</p>
<p>Since there is no Intellisense on the Notebook cells the best options to craft your SQL queries is either use SQL Server Management Studio (<a target=""_blank"" href=""https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query"">connect to TDS endpoint</a>)
or use XrmToolBox's <a target=""_blank"" href=""https://markcarrington.dev/sql-4-cds/"">SQL 4 CDS tool.</a></p>
<p>
Do not mix -e or -c switch inside a single Notebook i.e. use either connection strings or Azure authentication for Dataverse authentication.
</p>
</div>".DisplayAs("text/html");