Skip to main content
Home  › ... Razor

Data Tutorials

Tutorial HomeData
Requirements

SQL from a App Query, setting Params

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 SiteId = [Params:SiteId]
Here we override the parameter SiteId=1.

Root Portal

In this example we will set the PortalId for the SQL query.

Output

  1. 2sic120px.gif
  2. WYSIWYG.css
  3. PlaceHolder.txt
  4. PlaceHolder.txt
  5. Default.page.template
  6. Chrysanthemum.jpg
  7. DemoImage.jpg
  8. DemoImageBox.jpg
  9. DemoImageBox2.jpg
  10. DemoImagex-y.jpg
@{
  // Different query in DNN / Oqtane - eg. "SqlTop10FilesDnn"
  var queryName = "SqlTop10Files" + CmsContext.Platform.Name;

  // Prepare the a parameter before we access the query to get data
  var rootPortal = CmsContext.Platform.Name == "Dnn" ? 0 : 1;

  // Get the query
  var query = Kit.Data.GetQuery(queryName, parameters: new {
    SiteId = rootPortal
  });

}
<ol>
  @foreach (var file in AsList(query)) {
  <li>
   @file.Name
  </li>
}
</ol>

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 a App Query, setting Params</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 SiteId = [Params:SiteId]</code> <br>
      Here we override the parameter <code>SiteId=1</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>Root Portal</h3>
<p>In this example we will set the PortalId for the SQL query.</p>

  @{
    // Different query in DNN / Oqtane - eg. "SqlTop10FilesDnn"
    var queryName = "SqlTop10Files" + CmsContext.Platform.Name;

    // Prepare the a parameter before we access the query to get data
    var rootPortal = CmsContext.Platform.Name == "Dnn" ? 0 : 1;

    // Get the query
    var query = Kit.Data.GetQuery(queryName, parameters: new {
      SiteId = rootPortal
    });

  }
  <ol>
    @foreach (var file in AsList(query)) {
    <li>
     @file.Name
    </li>
  }
  </ol>


@* Footer *@
@Html.Partial("../Shared/Layout/FooterWithSource.cshtml", new { Sys = Sys })