Going MVC - getting SQL Data into Razor for DNN using SQL, PetaPoco, 2sxc

Razor is the future for most output-oriented .net stuff. This also applies to WebForms-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:

1. Fastest code: using a simple SQL-Reader
2. A bit more comfy: using a DataTable
3. With typed POCOs: using PetaPoco
4. Nicest: using 2SexyContent DataPipelines (SqlDataSource)
5. 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

1. Install a new DNN (7.2+)
2. Install 2SexyContent 6.0.6+ in the DNN
3. 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 accessh1>

       <ol>

             @while (myReader.Read())

             {

                    <li>@myReaderli>

             }

       ol>

       @{

             con.Close();

       }

div>

Pros

1. Easy - copy paste etc.
2. Standard .net, no learning curve
3. Probably best performance of all shown samples because almost no abstractions

#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 accessh1>

       <h2>The top 10 files found in this portal as returned from DBh2>

       <ol>

             @foreach (DataRow row in fileTable.Rows)

             {

                    <li>@rowli>

             }

       ol>

 

       <h2>The top 10 files found in this portal with reverse sortingh2>

       <ol>

             @foreach (DataRow row in fileTable.DefaultView.ToTable().Rows)

             {

                    <li>@rowli>

             }

       ol>

div>

Pros

1. Standard .net, no learning curve
2. Allows further data manipulation in memory
3. You can use the data a few times (reader is forward-only)
4. Connection handling open/close is done automatically by the Adapter

#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 PetaPocoh2>

       <ol>

             @foreach (var file in files)

             {

                    <li>@file.FileNameli>

             }

       ol>

 

div>

Pros

1. Typed data
2. Entity-Framework-like feeling without needing pre-compile
3. Less code than the other direct data methods (SQL & DataTable)
4. Short, brief syntax
5. Would already support paging and other features (read the PetaPoco docs)

#4 Nicest: using 2SexyContent DataPipelines (SqlDataSource)

@using ToSic.Eav.DataSources

@functions

{

       public override void CustomizeData()

       {

             var source = CreateSource();

             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);

       }

}

<br/>

<divclass="sc-element">

       @Content.Toolbar

       <h1>Automatic 2sxc Pipeline SqlDataSourceh1>

       <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">herea>.p>

       <h2>The top 10 files in this portal as returned by the Pipelineh2>

       <ol>

             @foreach (var file in AsDynamic(Data.In))

             {

                    <li>@file.FileNameli>

             }

       ol>

div>

Pros

1. Typed / dynamic entities
2. nice syntax, same as any other 2sxc data
3. Easy to configure
4. Configuration instead of programming (less error-prone and less security risks)
5. Benefits from automatic Configuration-Injection (like when @IdFilter can be )
6. Entity-Framework-like feeling without needing pre-compile
7. Less code than all other methods (SQL Reader, DataTable, PetaPoco)
8. No boilerplate / plumbing code (like PetaPoco)
9. Can benefit from other pipeline-features like additional filtering, paging, attribute-stripping
10. Can be auto-serialized using 2sxc and is then in the default format for JavaScript use - try here

#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);

 

             // 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">herea>.p>

       <h2>These entities resources are constructed by codeh2>

       <ol>

             @foreach (var resource in AsDynamic(Data.In))

             {

                    //var resource = AsDynamic(eRes);

                    <li>@resource.EntityTitle - @resource.Titleli>

             }

       ol>

div>

 

Pros

1. Typed / dynamic entities
2. lots of control over object structure
3. nice syntax, same as any other 2sxc data
4. any kind of source could be used - XML, file-lists, etc.
5. Entity-Framework-like feeling without needing pre-compile
6. Can benefit from other pipeline-features like additional filtering, paging, attribute-stripping
7. Can be auto-serialized using 2sxc and is then in the default format for JavaScript use
8. to see the automatic RSS-feed try here

Hope you liked it...

give me feedback, yours truly, iJungleboy