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].
Select Top 10 * from Files Where PortalId = [Params:SiteId]
SiteId
[Site:Id]
In this example PortalId uses the preset [Site:Id] which is 59
59
@inherits Custom.Hybrid.Razor14 @using ToSic.Razor.Blade @using System.Linq @{ // Different query in DNN / Oqtane - eg. "SqlTop10FilesDnn" var queryName = "SqlTop10Files" + CmsContext.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 = AsList(query); } <ol> @foreach (var file in files) { <li> @file.Name </li> } </ol>
This is how this view would be configured for this sample.
Get 10 SQL Data from Portal
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.
Select Top 10 * from Files Where SiteId = [Params:SiteId]
SiteId=1
In this example we will set the PortalId for the SQL query.
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].
Query
Reset()
@inherits Custom.Hybrid.Razor14 @using ToSic.Razor.Blade @using System.Linq @{ // Different query in DNN / Oqtane - eg. "SqlTop10FilesDnn" var queryName = "SqlTop10Files" + CmsContext.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 = AsList(query); } <ul> @foreach (var file in currentFiles) { <li>@file.Name</li> } </ul>
@inherits Custom.Hybrid.Razor14 @using ToSic.Razor.Blade @using System.Linq @{ // Get a fresh query, so we can use again with other parameters var queryName = "SqlTop10Files" + CmsContext.Platform.Name; var rootFilesQuery = Kit.Data.GetQuery(queryName, parameters: new { SiteId = CmsContext.Platform.Name == "Dnn" ? 0 : 1 }); var rootFiles = AsList(rootFilesQuery); } <ul> @foreach (var file in rootFiles) { <li>@file.Name</li> } </ul>
This example queries the DNN SQL for the files using a DataTable object.
DataTable
@inherits Custom.Hybrid.Razor14 @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", CmsContext.Site.Id); var fileTable = new DataTable(); adapter.Fill(fileTable); } <ol> @foreach (DataRow row in fileTable.Rows) { <li>@row["FileName"]</li> } </ol>
@inherits Custom.Hybrid.Razor14 @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", CmsContext.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>
This example queries the DNN SQL for the files using DataReader objects.
DataReader
@inherits Custom.Hybrid.Razor14 @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", CmsContext.Site.Id); var myReader = command.ExecuteReader(); } <ol> @while (myReader.Read()) { <li>@myReader["FileName"]</li> } </ol> @{ myReader.Close(); }