Skip to main content
Home  › ... Razor

Data Tutorials

Tutorial Home › Data
Conversion Factory Options
#1 SQL data from a SQL query and DataTable
SQL data from a SQL query and DataReader
Requirements
Resources

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

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

Initial Code

The following code runs at the beginning and creates some variables/services used in the following samples.

@{
    // 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", CmsContext.Site.Id);
    var fileTable = new DataTable();
    adapter.Fill(fileTable);
  }

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
<ol>
  @foreach (DataRow row in fileTable.Rows)
  {
    <li>@row["FileName"]</li>
  }
</ol>

The top 10 files found in this portal with reverse sorting

Output

  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
@{
  // This applies a default-view with more parameters
  fileTable.DefaultView.Sort = "FileName DESC";
}
<ol>
  @foreach (DataRow row in fileTable.DefaultView.ToTable().Rows)
  {
    <li>@row["FileName"]</li>
  }
</ol>
Conversion Factory Options
#1 SQL data from a SQL query and DataTable
SQL data from a SQL query and DataReader

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 a <code>DataTable</code> object.
    </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();

      // 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", CmsContext.Site.Id);
      var fileTable = new DataTable();
      adapter.Fill(fileTable);
    }


  <ol>
    @foreach (DataRow row in fileTable.Rows)
    {
      <li>@row["FileName"]</li>
    }
  </ol>



<h3>The top 10 files found in this portal with reverse sorting</h3>

  @{
    // This applies a default-view with more parameters
    fileTable.DefaultView.Sort = "FileName DESC";
  }
  <ol>
    @foreach (DataRow row in fileTable.DefaultView.ToTable().Rows)
    {
      <li>@row["FileName"]</li>
    }
  </ol>



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