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

Data Tutorials

Tutorial Home
App.Query with Params - Run multiple times
#8 SQL data from a SQL query and DataTable
SQL data from a SQL query and DataReader

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.

Requirements
Resources 

@{
  // 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);
}
  1. home.css
  2. background_header.gif
  3. icon-btn-sm-circle-arrow.png
  4. icon-btn-sm-circle-arrow-shadow.png
  5. icon-social-facebook.png
  6. icon-social-linkedin.png
  7. icon-social-twitter.png
  8. icon-social-youtube.png
  9. logo.png
  10. logo2.png
 

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

The top 10 files found in this portal with reverse sorting
  1. logo2.png
  2. logo.png
  3. icon-social-youtube.png
  4. icon-social-twitter.png
  5. icon-social-linkedin.png
  6. icon-social-facebook.png
  7. icon-btn-sm-circle-arrow-shadow.png
  8. icon-btn-sm-circle-arrow.png
  9. home.css
  10. background_header.gif
 

@{
  // 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>
App.Query with Params - Run multiple times
#8 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
<!-- unimportant stuff, hidden -->
@using System.Configuration
@using System.Data
@using System.Data.SqlClient

The top 10 files found in this portal as... <!-- unimportant stuff, hidden -->

@{
  // 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>




The top 10 files found in this portal... <!-- unimportant stuff, hidden -->

@{
  // 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>



<!-- unimportant stuff, hidden -->