Find a post...

DNN-Connect Blogs

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

Cons

  1. Only forward looping through the reader
  2. Code feels technical and maybe difficult
  3. Can't use 2sxc pipelines
  4. 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 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

Cons

  1. Code feels technical and maybe difficult
  2. no nice Object.Property-syntax
  3. Can't use 2sxc pipelines
  4. 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 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)

Cons

  1. Requires you to write classes for each type you need
  2. Lots of boilerplate / plumbing code for typed classes
  3. Numbered Parameters @0 instead of @PortalId
  4. Default mode with Query is forward-only like using a SQLReader
  5. Can't use 2sxc pipelines
  6. 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();

             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

Cons

  1. Probably a bit more performance overhead
  2. Might not fit all complex scenarios
  3. 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);

 

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

Cons

  1. Probably a bit more performance overhead
  2. more boilerplate / plumbing code (like PetaPoco)

Hope you liked it...

give me feedback, yours truly, iJungleboy

Daniel Mettler learned programming with the bible translation computer of his parents, deep in the jungles of Indonesia. Since he was only 12 years old at that time and the BIOS only had a version of BASICA, that's what got him started. With 16 he went back to Switzerland and learned German and basic city-survival skills. Equipped with this know-how he founded 2sic internet solutions in 1999 which was focused on web solutions on the Microsoft platform. After a few self-developed CMSs 2sic switched to DNN in 2003 and has been one of the largest partners (17 employees, 700+ projects) in Europe. Daniel is also the chief architect behind the open source 2sxc, a strong promoter of standardization (boostrap, patterns, AngularJS, checklists, etc.) and loves to eat anything - dead or alive. His motto: if the natives eat it, it game.
Comment(s)

Hosting liberally provided by

Philipp Becker 6011 7
Geoff Barlow 547 4
DNN-Connect 431 6
Peter Donker 5055 30
Christopher Hammond 680 2
Olivier Jooris 419 1
Daniel Mettler 12007 88
Clint Patterson 1 1
Jos Richters 65 1
James Rosewell 327 2
Will Strohl 1546 27
Ernst Peter Tamminga 437 4
Barry Waluszko 2798 2
Declan Ward 450 1
Gifford Watkins 722 9
Torsten Weggen 2688 3