<head> <script type="text/javascript" src='https://www.google.com/recaptcha/api.js'></script> </head>
  • Enter Your Name
  • Enter Your Email
  • Enter Your Phone
  • Enter Your Message
  • Enter Your Phone

Text/HTML

Phone : +1-631-897-7276 | Email : [email protected]
ASP.NET MVC – Retrieving SQL Data Into Razor For DNN Using SQL, PetaPoco And 2sxc
14 July 2014

ASP.NET MVC – Retrieving SQL Data Into Razor For DNN Using SQL, PetaPoco And 2sxc

 

Razor was introduced as a new view engine option for ASP.NET MVC and it is also incorporated into Web Matrix with its cshtml and vbhtml web pages. It has been within third party applications built on .NET most notably DotNetNuke and Umbraco. In this blog post, we’ll view all the ways by which you could access SQL data directly from Razor without pre-compiling something.

#1 Fast Code Generation 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

  • Easy to copy and paste

  • Enables standard .net so not much learning curve involved

  • Probably best performance because almost no abstractions are there

Cons

  • There is only forward looping through the reader

  • Code is purely technical and thus may be difficult

  • Can't use 2sxc pipelines and 2sxc in-built serializers and other features

#2 Use of 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

  • Enables standard .net so not much learning curve involved

  • Allows further data manipulation in memory

  • You can use the data a few times as reader is forward-only

  • Connection handling open/close is automatically done by the Adapter

Cons

  • Technical code so might be somewhat difficult

  • No nice Object.Property-syntax

  • Can't use 2sxc pipelines and 2sxc in-built serializers and other features

 

#3 Use of 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

  • Typed data, entity framework like feeling without pre-compile

  • Less code than the other direct data methods (SQL & DataTable)

  • Short, brief syntax and support paging and other features

Cons

  • Need you to write classes for each type

  • 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 and 2sxc-built-in serializers and other features

#4 Using SQL Data Source

@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

  • Typed / dynamic entities, easy to configure

  • Nice syntax same as any other 2sxc data

  • Configuration instead of programming (less error-prone and less security risks)

  • Benefits from automatic Configuration-Injection

  • Entity framework like feeling without pre-compile

  • Less code than all other methods (SQL Reader, DataTable, PetaPoco)

  • No boilerplate / plumbing code (like PetaPoco)

  • 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

Cons

  • A bit more performance overhead

  • Might not fit all complex scenarios

  • No built-in paging like with PetaPoco may be in the near future

#5 Use of Data Table Data Source

@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

  • Typed / dynamic entities. Lots of control over object structure

  • Nice syntax same as any other 2sxc data

  • Any kind of source can 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

Cons

  • Probably a bit more performance overhead

  • More boilerplate / plumbing code (like PetaPoco)

Related

You need to login in order to comment