SQL4CDS Queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup\n",
    "\n",
    "In order to use this Notebook as a Dataverse SQL Notebook you would first need to import the Notebook below which creates the <code>#!dataverse-sql</code> magic command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "vscode": {
     "languageId": "dotnet-interactive.csharp"
    }
   },
   "outputs": [],
   "source": [
    "#!import \"SQL 4 CDS Main.dib\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List of managed solutions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "sql"
    },
    "vscode": {
     "languageId": "dotnet-interactive.sql"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-sql -e https://dreamingincrm.crm6.dynamics.com\n",
    "\n",
    "SELECT solutionid, friendlyname, uniquename \n",
    "FROM Solution\n",
    "WHERE isvisible=1 AND ismanaged=1\n",
    "ORDER by friendlyname"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 users sorted by createdon desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "sql"
    },
    "vscode": {
     "languageId": "dotnet-interactive.sql"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-sql -e https://dreamingincrm.crm6.dynamics.com\n",
    "\n",
    "SELECT TOP 10 \n",
    "systemuserid,\n",
    "fullname,\n",
    "internalemailaddress \n",
    "FROM SystemUser\n",
    "ORDER BY createdon DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List of Tables in Field Service Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "sql"
    },
    "vscode": {
     "languageId": "dotnet-interactive.sql"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-sql -e https://dreamingincrm.crm6.dynamics.com\n",
    "\n",
    "SELECT \n",
    "logicalname AS [Table Name], \n",
    "logicalcollectionname AS [Table Collection Name], \n",
    "ObjectTypeCode, uniquename AS [Solution Unique Name], \n",
    "friendlyname AS [Solution Friendly Name]\n",
    "FROM Metadata.entity\n",
    "JOIN solutioncomponent ON entity.metadataid = solutioncomponent.objectid\n",
    "JOIN solution ON solutioncomponent.solutionid = solution.solutionid\n",
    "WHERE iscustomentity=1 AND islogicalentity=0 AND uniquename='FieldService'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List of Code Components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "sql"
    },
    "vscode": {
     "languageId": "dotnet-interactive.sql"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-sql -e https://dreamingincrm.crm6.dynamics.com\n",
    "\n",
    "SELECT \n",
    "solution.friendlyname AS Solution, \n",
    "customcontrol.Name, \n",
    "customcontrol.CompatibleDataTypes, \n",
    "customcontrol.ComponentState, \n",
    "customcontrol.Version, customcontrol.createdon\n",
    "FROM customcontrol\n",
    "join solution ON customcontrol.solutionid = solution.solutionid\n",
    "ORDER BY customcontrol.createdon DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List of Plugins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "sql"
    },
    "vscode": {
     "languageId": "dotnet-interactive.sql"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-sql -e https://dreamingincrm.crm6.dynamics.com\n",
    "\n",
    "SELECT \n",
    "sdkmessagefilter.PrimaryObjectTypeCode, \n",
    "sdkmessage.Name AS Message, \n",
    "sdkmessageprocessingstep.Rank, \n",
    "sdkmessageprocessingstep.StageName, \n",
    "plugintype.AssemblyName, \n",
    "plugintype.Name AS PluginName, \n",
    "sdkmessageprocessingstep.Name AS StepName, \n",
    "sdkmessageprocessingstep.Description AS StepDescription, \n",
    "sdkmessageprocessingstep.StatusCodeName,\n",
    "sdkmessageprocessingstep.FilteringAttributes\n",
    "FROM sdkmessageprocessingstep\n",
    "JOIN sdkmessagefilter ON SdkMessageProcessingStep.sdkmessagefilterid = sdkmessagefilter.sdkmessagefilterid\n",
    "JOIN sdkmessage ON sdkmessagefilter.sdkmessageid = sdkmessage.sdkmessageid\n",
    "JOIN plugintype ON sdkmessageprocessingstep.eventhandler = plugintype.plugintypeid\n",
    "WHERE \n",
    "sdkmessagefilter.IsCustomProcessingStepAllowed = 1\n",
    "AND sdkmessageprocessingstep.IsHidden = 0\n",
    "AND sdkmessageprocessingstep.CustomizationLevel = 1\n",
    "ORDER BY sdkmessagefilter.PrimaryObjectTypeCode,sdkmessage.Name,plugintype.AssemblyName, plugintype.Name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Variable sharing\n",
    "\n",
    "Notebooks support variable sharing. The previous query result is stored in the _lastResults_ variable inside the SQL 4 CDS subkernel. So, you can use this value inside a PowerShell cell as seen in the example below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "pwsh"
    },
    "vscode": {
     "languageId": "dotnet-interactive.pwsh"
    }
   },
   "outputs": [],
   "source": [
    "#!share --from csharp lastResults\n",
    "$results = $lastResults | ConvertFrom-Json -AsHashtable | select -first 3\n",
    "$results"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".NET (C#)",
   "language": "C#",
   "name": ".net-csharp"
  },
  "language_info": {
   "file_extension": ".cs",
   "mimetype": "text/x-csharp",
   "name": "C#",
   "pygments_lexer": "csharp",
   "version": "9.0"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "9c2e2c61ff21337d472f6dad47fcf17bfaea716bdc72662572b3b6689b4c47aa"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}