Razor is the future for most output-oriented .net stuff. This also applies to WinForms-based CMS like DNN. But fortunately, they got it right and added Razor-Support about 2 years ago. In my opinion, this is something that most people haven’t figured out yet – partially because they can’t find the code-snippets to help them. So here goes: all the ways you could use to access SQL-data directly from Razor without pre-compiling something.
So no Entity-Framework or similar. The five options we’ll review are:
- Fastest code: using a simple SQL-Reader
- A bit more comfy: using a DataTable
- With typed POCOs: using PetaPoco
- Nicest: using 2SexyContent DataPipelines (SqlDataSource)
- Nicest but with more complexity if needed: using 2SexyContent DataPipelines with manual data (DataTableDataSource)
BTW: the easiest way to try this out and to play with the code is to
- Install a new DNN (7.2+)
- Install 2SexyContent 6.0.6+ in the DNN
- Install this SQL with Razor Data-Demo-App with all the samples here included
The SQL samples are described and documented here.
For just quickly looking at the results, you can find it all here on the page with the app-demo.
#1 Fastest code: using SQL Reader
@using System.Configuration
@using System.Data.SqlClient
@{
var conString = ConfigurationManager.ConnectionStrings[Content.ConnectionName].ToString();
var con = newSqlConnection(conString);
con.Open();
var command = newSqlCommand(„Select Top 10 * from Files Where PortalId = @PortalId“, con);
command.Parameters.Add(„@PortalId“, Dnn.Portal.PortalId);
SqlDataReader myReader = command.ExecuteReader();
}
<divclass=“sc-element“>
@Content.Toolbar
<h1>Simple Demo using DataReader access</h1>
<ol>
@while (myReader.Read())
{
<li>@myReader[„FileName“]</li>
}
</ol>
@{
con.Close();
}
</div>
Pros
- Easy – copy paste etc.
- Standard .net, no learning curve
- Probably best performance of all shown samples because almost no abstractions
Cons
- Only forward looping through the reader
- Code feels technical and maybe difficult
- Can’t use 2sxc pipelines
- Can’t use 2sxc-built-in serializers and other features
#2 More Comfy: using DataTable
@using System.Configuration
@using System.Data
@using System.Data.SqlClient
@{
var conString = ConfigurationManager.ConnectionStrings[Content.ConnectionName].ToString();
var sqlCommand = „Select Top 10 * from Files Where PortalId = @PortalId“;
var adapter = newSqlDataAdapter(sqlCommand, conString);
adapter.SelectCommand.Parameters.Add(„@PortalId“, Dnn.Portal.PortalId);
var fileTable = newDataTable();
adapter.Fill(fileTable);
// for the demo, apply some operation to the data
fileTable.DefaultView.Sort = „FileName DESC“;
}
<divclass=“sc-element“>
@Content.Toolbar
<h1>Simple Demo with DataTable access</h1>
<h2>The top 10 files found in this portal as returned from DB</h2>
<ol>
@foreach (DataRow row in fileTable.Rows)
{
<li>@row[„FileName“]</li>
}
</ol>
<h2>The top 10 files found in this portal with reverse sorting</h2>
<ol>
@foreach (DataRow row in fileTable.DefaultView.ToTable().Rows)
{
<li>@row[„FileName“]</li>
}
</ol>
</div>
Pros
- Standard .net, no learning curve
- Allows further data manipulation in memory
- You can use the data a few times (reader is forward-only)
- Connection handling open/close is done automatically by the Adapter
Cons
- Code feels technical and maybe difficult
- no nice Object.Property-syntax
- Can’t use 2sxc pipelines
- Can’t use 2sxc-built-in serializers and other features
#3 With typed POCOs: using PetaPoco
@functions
{
// for PetaPoco you must first create a class containing the fields you want
privateclassfileRecord
{
public int FileId {get;set;}
public string FileName { get; set; }
public int Size { get; set; }
public int FolderId { get; set; }
}
}
@{
var sqlCommand = „Select Top 10 * from Files Where PortalId = @0“; // PetaPoco requires numbered parameters like @0 instead of @PortalId
var db = new PetaPoco.Database(Content.ConnectionName);
var files = db.Query<fileRecord>(sqlCommand, Dnn.Portal.PortalId);
}
<divclass=“sc-element“>
@Content.Toolbar
<h2>The top 10 files found in this portal as returned by PetaPoco</h2>
<ol>
@foreach (var file in files)
{
<li>@file.FileName</li>
}
</ol>
</div>
Pros
- Typed data
- Entity-Framework-like feeling without needing pre-compile
- Less code than the other direct data methods (SQL & DataTable)
- Short, brief syntax
- Would already support paging and other features (read the PetaPoco docs)
Cons
- Requires you to write classes for each type you need
- Lots of boilerplate / plumbing code for typed classes
- Numbered Parameters @0 instead of @PortalId
- Default mode with Query is forward-only like using a SQLReader
- Can’t use 2sxc pipelines
- Can’t use 2sxc-built-in serializers and other features
#4 Nicest: using 2SexyContent DataPipelines (SqlDataSource)
@using ToSic.Eav.DataSources
@functions
{
public override void CustomizeData()
{
var source = CreateSource<SqlDataSource>();
source.ConnectionStringName = Content.ConnectionName;
// Special note: I’m not selecting * from the DB, because I’m activating JSON and want to be sure that no secret data goes out
source.SelectCommand = „Select Top 10 FileId as EntityId, FileName as EntityTitle, Extension, PublishedVersion, Size, UniqueId, FileName FROM Files WHERE PortalId = @PortalId“;
source.Configuration.Add(„@PortalId“, Dnn.Portal.PortalId.ToString());
Data.In.Add(„FileList“, source.Out[„Default“]);
}
}
<br/>
<divclass=“sc-element“>
@Content.Toolbar
<h1>Automatic 2sxc Pipeline SqlDataSource</h1>
<p>This demo uses the 2sxc Pipeline (req. 2sxc 6.0.6+). More info <ahref=“http://2sexycontent.org/en-us/docsmanuals/feature.aspx?feature=2579″target=“_blank“>here</a>.</p>
<h2>The top 10 files in this portal as returned by the Pipeline</h2>
<ol>
@foreach (var file in AsDynamic(Data.In[„FileList“]))
{
<li>@file.FileName</li>
}
</ol>
</div>
Pros
- Typed / dynamic entities
- nice syntax, same as any other 2sxc data
- Easy to configure
- Configuration instead of programming (less error-prone and less security risks)
- Benefits from automatic Configuration-Injection (like when @IdFilter can be [QueryString:ProductId|0] )
- Entity-Framework-like feeling without needing pre-compile
- Less code than all other methods (SQL Reader, DataTable, PetaPoco)
- No boilerplate / plumbing code (like PetaPoco)
- Can benefit from other pipeline-features like additional filtering, paging, attribute-stripping
- Can be auto-serialized using 2sxc and is then in the default format for JavaScript use – try here
Cons
- Probably a bit more performance overhead
- Might not fit all complex scenarios
- No built-in paging like with PetaPoco, probably in the near future
#5 Nicest with control: 2SexyContent Table-DataPipelines (DataTableDataSource)
@using System.Data
@using ToSic.Eav.DataSources
@functions
{
// Official place to provide data preparation. Is automatically called by 2SexyContent
public override void CustomizeData()
{
var res = CreateResourcesSource();
res.Source.Rows.Add(1031, „de-de“, „Deutsch“, „Herzlich Willkommen“, „Schön, dass Sie dies lesen, bitte haben Sie Spass!“, „Vorname“, „Nachname“);
res.Source.Rows.Add(1033, „en-us“, „English“, „Welcome“, „Thanks for looking at this!“, „First name“, „Last name“);
Data.In.Add(res.ContentType, res.Out[„Default“]);
// enable publishing
Data.Publish.Enabled = true;
Data.Publish.Streams = „Default,UIResources“;
}
private DataTableDataSource CreateResourcesSource()
{
var dataTable = newDataTable();
dataTable.Columns.AddRange(new[]
{
new DataColumn(„EntityId“, typeof(int)),
new DataColumn(„EntityTitle“),
new DataColumn(„Language“),
new DataColumn(„Title“),
new DataColumn(„Introduction“),
new DataColumn(„FirstNameLabel“),
new DataColumn(„LastNameLabel“)
});
var source = CreateSource<DataTableDataSource>();
source.Source = dataTable;
source.ContentType = „UIResources“;
//source.TitleField = „FullName“; // not necessary because we’re already using the default
//source.EntityIdField = „EntityId“;// not necessary because we’re already using the default
return source;
}
}
<divclass=“sc-element“>
@Content.Toolbar
<h1>Simple Demo with custom data (for example to use non-SQL data)</h1>
<p>This demo uses the 2sxc Pipeline (req. 2sxc 6.0.6+). More info <ahref=“http://2sexycontent.org/en-us/docsmanuals/feature.aspx?feature=2580″target=“_blank“>here</a>.</p>
<h2>These entities resources are constructed by code</h2>
<ol>
@foreach (var resource in AsDynamic(Data.In[„UIResources“]))
{
//var resource = AsDynamic(eRes);
<li>@resource.EntityTitle – @resource.Title</li>
}
</ol>
</div>
Pros
- Typed / dynamic entities
- lots of control over object structure
- nice syntax, same as any other 2sxc data
- any kind of source could be used – XML, file-lists, etc.
- Entity-Framework-like feeling without needing pre-compile
- Can benefit from other pipeline-features like additional filtering, paging, attribute-stripping
- Can be auto-serialized using 2sxc and is then in the default format for JavaScript use
- to see the automatic RSS-feed try here
Cons
- Probably a bit more performance overhead
- more boilerplate / plumbing code (like PetaPoco)
Hope you liked it…
give me feedback, yours truly, iJungleboy