Skip to main content
Home  › ... Razor

Data Tutorials

Tutorial Home › Data
SQL data from a SQL query and DataTable
#2 SQL data from a SQL query and DataReader
DNN data (pages/tabs) from the DNN TabController
Requirements
Resources

The top 10 files found in this portal as returned from DB

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

Output

  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
@{
  // 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", CmsContext.Site.Id);
  var myReader = command.ExecuteReader();
}
<ol>
  @while (myReader.Read())
  {
    <li>@myReader["FileName"]</li>
  }
</ol>
@{
  myReader.Close();
}
SQL data from a SQL query and DataTable
#2 SQL data from a SQL query and DataReader
DNN data (pages/tabs) from the DNN TabController

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;
@using System.Configuration
@using System.Data
@using System.Data.SqlClient
<!-- unimportant stuff, hidden -->


<div @Sys.PageParts.InfoWrapper()>
  @Html.Partial("../shared/DefaultInfoSection.cshtml")
  <div @Sys.PageParts.InfoIntro()>
    <h2>The top 10 files found in this portal as returned from DB</h2>
    <p>
      This example queries the DNN SQL for the files using <code>DataReader</code> objects.
    </p>
  </div>
</div>

  @{
    // 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", CmsContext.Site.Id);
    var myReader = command.ExecuteReader();
  }
  <ol>
    @while (myReader.Read())
    {
      <li>@myReader["FileName"]</li>
    }
  </ol>
  @{
    myReader.Close();
  }



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