#4 Use App Query to get SQL Data
Requirements
Resources
SQL from an App Query
The easiest way to get SQL data is using Visual Query. Note that this only works, if your razor is inside 2sxc/eav. This example gets the list of files from DNN using a query like
Select Top 10 * from Files Where PortalId = [Params:SiteId]
Note that the parameter SiteId
is preset to be [Site:Id]
.
Current Portal
In this example PortalId uses the preset [Site:Id]
which is 2
Output
- Last Continent.png
- photo-1651590814269-ccf8dee671ae.jpg
- ed hardy.png
- Resize and crop with presets.png
- settings-stack.svg
- image.png
- Per Anhalter.jpg
- Per Anhalter-1.jpg
- content-templates.svg
- good omens.png
#4 Use App Query to get SQL Data
Source Code of this file
Below you'll see the source code of the file. Note that we're just showing the main part, and hiding some parts of the file which are not relevant for understanding the essentials. Click to expand the code
@inherits Custom.Hybrid.Razor14 @using ToSic.Razor.Blade; @using System.Linq; <!-- unimportant stuff, hidden --> <div @Sys.PageParts.InfoWrapper()> @Html.Partial("../shared/DefaultInfoSection.cshtml") <div @Sys.PageParts.InfoIntro()> <h2>SQL from an App Query</h2> <p> The easiest way to get SQL data is using Visual Query. Note that this only works, if your razor is inside 2sxc/eav. This example gets the list of files from DNN using a query like <br> <code>Select Top 10 * from Files Where PortalId = [Params:SiteId]</code> <br> Note that the parameter <code>SiteId</code> is preset to be <code>[Site:Id]</code>. </p> <div class="row"> <div class="col-4">@Sys.Fancybox.PreviewWithLightbox(App.Path + "/data/assets/sql-query-dnn-files.png", 200, 200, "float-left", label: "Query Tree")</div> <div class="col-4">@Sys.Fancybox.PreviewWithLightbox(App.Path + "/data/assets/sql-query-configuration.png", 200, 200, "float-left", label: "Query Configuration with Params and Test-Values")</div> <div class="col-4">@Sys.Fancybox.PreviewWithLightbox(App.Path + "/data/assets/sql-query-select-statement.png", 200, 200, "float-left", label: "SQL Query using Params")</div> </div> </div> </div> <h3>Current Portal</h3> <p> In this example PortalId uses the preset <code>[Site:Id]</code> which is <code>@CmsContext.Site.Id</code> </p> @{ // Different query in DNN / Oqtane - eg. "SqlTop10FilesDnn" var queryName = "SqlTop10Files" + CmsContext.Platform.Name; // Get the query and ask for the "Default" results as a dynamic List var query = Kit.Data.GetQuery(queryName); var files = AsList(query); } <ol> @foreach (var file in files) { <li> @file.Name </li> } </ol> @* Footer *@ @Html.Partial("../Shared/Layout/FooterWithSource.cshtml", new { Sys = Sys })