Skip to main content
Home  › ... Razor

External Data from CSV or SQL

Tutorial Home › External Data
Example from CSV
#2 Various SQL Samples
Use DNN APIs to get DNN Data

Various SQL Samples

The samples can differ based on your Razor base class or if you're running an old version.
Selected: Typed (2sxc 16+) Switch to Dynamic (Razor14 or below)
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].

In this example PortalId uses the preset [Site:Id] which is 59

⬇️ Result | Source ➡️

  1. image-1.jpg
  2. image-2.jpg
  3. ct basic content.png
  4. ct image.png
  5. ct layout element.png
  6. ct links.png
  7. ct location.png
  8. ct person.png
  9. ct video.png
  10. RazorTutoApp1000x1000.png 
@inherits Custom.Hybrid.RazorTyped
@using ToSic.Razor.Blade
@using System.Linq

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

  // Get the query and ask for the "Default" results as a List of Dynamic objects
  var query = Kit.Data.GetQuery(queryName);
  var files = AsItems(query);
}
<ol>
  @foreach (var file in files) {
  <li>
    @file.Get("Name")
  </li>
}
</ol>

View Configuration

This is how this view would be configured for this sample.

  • Query: SqlTop10FilesDnn
Details for Query: SqlTop10FilesDnn

Get 10 SQL Data from Portal

Query Configuration with Params and Test-ValuesQuery TreeSQL Query using Params
Requirements
Resources

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.

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

⬇️ Result | Source ➡️

  1. image-1.jpg
  2. image-2.jpg
  3. ct basic content.png
  4. ct image.png
  5. ct layout element.png
  6. ct links.png
  7. ct location.png
  8. ct person.png
  9. ct video.png
  10. RazorTutoApp1000x1000.png 
@inherits Custom.Hybrid.RazorTyped
@using ToSic.Razor.Blade
@using System.Linq

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

  // Get the query and ask for the "Default" results as a List of Dynamic objects
  var query = Kit.Data.GetQuery(queryName);
  var files = AsItems(query);
}
<ol>
  @foreach (var file in files) {
  <li>
    @file.Get("Name")
  </li>
}
</ol>

View Configuration

This is how this view would be configured for this sample.

  • Query: SqlTop10FilesDnn
Details for Query: SqlTop10FilesDnn

Get 10 SQL Data from Portal

Query Configuration with Params and Test-ValuesQuery TreeSQL Query using Params
Requirements
Resources

Run an App Query multiple times on the same page

The previous examples showed how to use a Query. Now we want to run the query twice, which requires a Reset().
Note that the parameter SiteId is preset to be [Site:Id].

⬇️ Result | Source ➡️

  • image-1.jpg
  • image-2.jpg
  • ct basic content.png
  • ct image.png
  • ct layout element.png
  • ct links.png
  • ct location.png
  • ct person.png
  • ct video.png
  • RazorTutoApp1000x1000.png 
@inherits Custom.Hybrid.RazorTyped
@using ToSic.Razor.Blade
@using System.Linq

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

  // get the query and ask for the "Default" results as a List of Dynamic objects
  // This case doesn't set parameters, so we're using the defaults (current site)
  var query = Kit.Data.GetQuery(queryName);
  var currentFiles = AsItems(query);
}
<ul>
  @foreach (var file in currentFiles) {
  <li>
    @file.Get("Name")
  </li>
}
</ul>

View Configuration

This is how this view would be configured for this sample.

  • Query: SqlTop10FilesDnn
Details for Query: SqlTop10FilesDnn

Get 10 SQL Data from Portal

Query Configuration with Params and Test-ValuesQuery TreeSQL Query using Params

⬇️ Result | Source ➡️

  • 2sic120px.gif
  • WYSIWYG.css
  • PlaceHolder.txt
  • PlaceHolder.txt
  • Default.page.template
  • Chrysanthemum.jpg
  • DemoImage.jpg
  • DemoImageBox.jpg
  • DemoImageBox2.jpg
  • DemoImagex-y.jpg 
@inherits Custom.Hybrid.RazorTyped
@using ToSic.Razor.Blade
@using System.Linq

@{
  // Get a fresh query, so we can use again with other parameters
  var queryName = "SqlTop10Files" + MyContext.Platform.Name;
  var rootFilesQuery = Kit.Data.GetQuery(queryName, parameters: new { 
    SiteId = MyContext.Platform.Name == "Dnn" ? 0 : 1
  });

  var rootFiles = AsItems(rootFilesQuery);
}
<ul>
  @foreach (var file in rootFiles) {
  <li>
    @file.Get("Name")
  </li>
}
</ul>

View Configuration

This is how this view would be configured for this sample.

  • Query: SqlTop10FilesDnn
Details for Query: SqlTop10FilesDnn

Get 10 SQL Data from Portal

Query Configuration with Params and Test-ValuesQuery TreeSQL Query using Params
Requirements
Resources

SQL Database

This example queries the DNN SQL for the files using a DataTable object.

⬇️ Result | Source ➡️

  1. image-1.jpg
  2. image-2.jpg
  3. ct basic content.png
  4. ct image.png
  5. ct layout element.png
  6. ct links.png
  7. ct location.png
  8. ct person.png
  9. ct video.png
  10. RazorTutoApp1000x1000.png
@inherits Custom.Hybrid.RazorTyped
@using ToSic.Razor.Blade
@using System.Linq
@using System.Configuration
@using System.Data
@using System.Data.SqlClient

@{
  // load the sql connection name from Web.Config
  // the default connection string for DNN is SiteSqlServer
  var conString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ToString();

  // You should always write parameters using the @-syntaxt,
  // and never write them directly into the SQL using string-concatenation
  // to protect yourself from SQL injection attacks
  const string sqlCommand = "Select Top 10 * from Files Where PortalId = @PortalId";

  // create an adapter object, tell it what to do, and load results into fileTable
  var adapter = new SqlDataAdapter(sqlCommand, conString);
  adapter.SelectCommand.Parameters.AddWithValue("@PortalId", MyContext.Site.Id);
  var fileTable = new DataTable();
  adapter.Fill(fileTable);
}
<ol>
  @foreach (DataRow row in fileTable.Rows)
  {
    <li>@row["FileName"]</li>
  }
</ol>

⬇️ Result | Source ➡️

  1. RazorTutoApp1000x1000.png
  2. image-2.jpg
  3. image-1.jpg
  4. ct video.png
  5. ct person.png
  6. ct location.png
  7. ct links.png
  8. ct layout element.png
  9. ct image.png
  10. ct basic content.png
@inherits Custom.Hybrid.RazorTyped
@using ToSic.Razor.Blade
@using System.Linq
@using System.Configuration
@using System.Data
@using System.Data.SqlClient

@{
  // load the sql connection name from Web.Config
  // the default connection string for DNN is SiteSqlServer
  var conString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ToString();

  // You should always write parameters using the @-syntaxt,
  // and never write them directly into the SQL using string-concatenation
  // to protect yourself from SQL injection attacks
  const string sqlCommand = "Select Top 10 * from Files Where PortalId = @PortalId";

  // create an adapter object, tell it what to do, and load results into fileTable
  var adapter = new SqlDataAdapter(sqlCommand, conString);
  adapter.SelectCommand.Parameters.AddWithValue("@PortalId", MyContext.Site.Id);
  var fileTable = new DataTable();
  adapter.Fill(fileTable);
  fileTable.DefaultView.Sort = "FileName DESC";
}
<ol>
  @foreach (DataRow row in fileTable.DefaultView.ToTable().Rows)
  {
    <li>@row["FileName"]</li>
  }
</ol>

SQL Datareader

This example queries the DNN SQL for the files using DataReader objects.

⬇️ Result | Source ➡️

  1. image-1.jpg
  2. image-2.jpg
  3. ct basic content.png
  4. ct image.png
  5. ct layout element.png
  6. ct links.png
  7. ct location.png
  8. ct person.png
  9. ct video.png
  10. RazorTutoApp1000x1000.png
@inherits Custom.Hybrid.RazorTyped
@using ToSic.Razor.Blade
@using System.Linq
@using System.Configuration
@using System.Data
@using System.Data.SqlClient

@{
  // load the sql connection name from Web.Config
  // the default connection string for DNN is SiteSqlServer
  var conString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ToString();

  var con = new SqlConnection(conString);
  con.Open();

  // You should always write parameters using the @-syntaxt,
  // and never write them directly into the SQL using string-concatenation
  // to protect yourself from SQL injection attacks
  var command = new SqlCommand("Select Top 10 * from Files Where PortalId = @PortalId", con);
  command.Parameters.Add("@PortalId", MyContext.Site.Id);
  var myReader = command.ExecuteReader();
}
<ol>
  @while (myReader.Read())
  {
    <li>@myReader["FileName"]</li>
  }
</ol>
@{
  myReader.Close();
}

 

 

Example from CSV
#2 Various SQL Samples
Use DNN APIs to get DNN Data