Skip to main content
Home  › ... 2sxc Apps

Data Tutorials

Tutorial Home
Use App Query to get SQL Data
#6 SQL data from an App.Query with Params
App.Query with Params - Run multiple times

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

Requirements
Resources

Root Portal

In this example we will set the PortalId for the SQL query.
  • home.css
  • background_header.gif
  • icon-btn-sm-circle-arrow.png
  • icon-btn-sm-circle-arrow-shadow.png
  • icon-social-facebook.png
  • icon-social-linkedin.png
  • icon-social-twitter.png
  • icon-social-youtube.png
  • logo.png
  • logo2.png 

    @{
      // get the query and set a parameter
      var query = App.Query["DnnFilesFromSql"];
      query.Params("PortalId", "0");
      var rootFiles = AsList(query);
    }
    <ul>
      @foreach(var file in rootFiles) {
        <li>
          @file.FileName
        </li>
      }
    </ul>
Use App Query to get SQL Data
#6 SQL data from an App.Query with Params
App.Query with Params - Run multiple times

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
<!-- unimportant stuff, hidden -->
@using ToSic.Razor.Blade;
@using System.Linq;
SQL from a App.Query, setting Params The... <!-- unimportant stuff, hidden -->
<div class="row">
  <div class="col-lg-7">
    <h2>Root Portal</h2>
    <div>In this example we will set the PortalId for the SQL query.</div>
    
    @{
      // get the query and set a parameter
      var query = App.Query["DnnFilesFromSql"];
      query.Params("PortalId", "0");
      var rootFiles = AsList(query);
    }
    <ul>
      @foreach(var file in rootFiles) {
        <li>
          @file.FileName
        </li>
      }
    </ul>
    
    
  </div>
  <div class="col-lg-5">
    @fancybox.PreviewWithLightbox(App.Path + "/data/assets/sql-query-dnn-files.png", width, height, "float-left", label: "Query Tree") 
    @fancybox.PreviewWithLightbox(App.Path + "/data/assets/sql-query-configuration.png", width, height, "float-left", label: "Query Configuration with Params and Test-Values")
    @fancybox.PreviewWithLightbox(App.Path + "/data/assets/sql-query-select-statement.png", width, height, "float-left", label: "SQL Query using Params")
  </div>
</div>


<!-- unimportant stuff, hidden -->