Fork me on GitHub
DNN Apps - Demo-Zone
Discover DNN-Apps - simple and elegant, easy to customize
You are here: Home  >  Apps  >  SQL Data - Examples

Automatic 2sxc Pipeline SqlDataSource

This demo uses the 2sxc Pipeline (req. 2sxc 6.0.6+). More info here.

The top 10 files in this portal as returned by the Pipeline

  1. Hydrangeas.jpg
  2. Koala.jpg
  3. Penguins.jpg
  4. Google Tag Manager Source Code Demo 2014-03-13.png
  5. Beni.jpg
  6. Daniel.jpg
  7. Raphael.jpg
  8. GettingStarted.css
  9. Chrysanthemum.jpg
  10. Lighthouse.jpg

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 [QueryString:ProductId|0] )
  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 future

Simple Demo with PetaPoco Data access

This demo uses PetaPoco as a mini-ORM to get the data. More info on PetaPoco here.

The top 10 files found in this portal as returned by PetaPoco

  1. Hydrangeas.jpg (613)
  2. Koala.jpg (614)
  3. Penguins.jpg (615)
  4. Google Tag Manager Source Code Demo 2014-03-13.png (616)
  5. Beni.jpg (620)
  6. Daniel.jpg (621)
  7. Raphael.jpg (622)
  8. GettingStarted.css (653)
  9. Chrysanthemum.jpg (659)
  10. Lighthouse.jpg (660)

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

Simple Demo using DataReader access

This demo accesses the data directly, uses a SQL parameter for the PortalId then shows the first 10 files it finds. More intro-material for direct database access in this article on codeplex.

The top 10 files found in this portal

  1. Hydrangeas.jpg
  2. Koala.jpg
  3. Penguins.jpg
  4. Google Tag Manager Source Code Demo 2014-03-13.png
  5. Beni.jpg
  6. Daniel.jpg
  7. Raphael.jpg
  8. GettingStarted.css
  9. Chrysanthemum.jpg
  10. Lighthouse.jpg

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

Simple Demo with DataTable access

This demo accesses the data by filling it into a DataTable.

The top 10 files found in this portal with reverse sorting

  1. Raphael.jpg
  2. Penguins.jpg
  3. Lighthouse.jpg
  4. Koala.jpg
  5. Hydrangeas.jpg
  6. Google Tag Manager Source Code Demo 2014-03-13.png
  7. GettingStarted.css
  8. Daniel.jpg
  9. Chrysanthemum.jpg
  10. Beni.jpg

The top 10 files found in this portal as returned from DB

  1. Hydrangeas.jpg
  2. Koala.jpg
  3. Penguins.jpg
  4. Google Tag Manager Source Code Demo 2014-03-13.png
  5. Beni.jpg
  6. Daniel.jpg
  7. Raphael.jpg
  8. GettingStarted.css
  9. Chrysanthemum.jpg
  10. Lighthouse.jpg

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

Simple Demo with custom data (for example to use non-SQL data)

This demo uses the 2sxc Pipeline (req. 2sxc 6.0.6+). More info here.

These entities resources are constructed by code

  1. de-de - Herzlich Willkommen
  2. en-us - Welcome

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)

Look inside

Content Item

These are the values a content-editor can manage.
Name Type Value
ConnectionName System.String SiteSqlServer SiteSqlServer


Presentation Item

These are additional, optional presentation instructions a content-editor can manage. If none are entered, a default set (predefined by the designer) will be used.

No Presentation parameters specified, using default presentation for this item.


Template file

@using System.Configuration
@using System.Data.SqlClient
@functions{
	private SqlDataReader myReader;

	// Official place to provide data preparation. Is automatically called by 2SexyContent
	public override void CustomizeData()
	{
		var conString = ConfigurationManager.ConnectionStrings[Content.ConnectionName].ToString();
		var con = new SqlConnection(conString);
		con.Open();
		var command = new SqlCommand("Select Top 10 * from Files Where PortalId = @PortalId", con);
		command.Parameters.Add("@PortalId", Dnn.Portal.PortalId);
		myReader = command.ExecuteReader();
	}
}
<div class="sc-element">
	@Content.Toolbar
	<h1>Simple Demo using DataReader access</h1>
	<p>This demo accesses the data directly, uses a SQL parameter for the PortalId then shows the first 10 files it finds. More intro-material for direct database access in this <a href="http://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C" target="_blank">article on codeplex</a>.</p>
	<h2>The top 10 files found in this portal</h2>
	<ol>
		@while (myReader.Read())
		{
			<li>@myReader["FileName"]</li>
		}
	</ol>
	@{
		myReader.Close();
	}
</div>

<div class="ym-grid">
	<div class="ym-g50 ym-gl">
		<h3>Pros</h3>
		<ol>
			<li>Easy - copy paste etc.</li>
			<li>Standard .net, no learning curve</li>
			<li>Probably best performance of all shown samples because almost no abstractions</li>
		</ol>
	</div>
	<div class="ym-g50 ym-gr">

		<h3>Cons</h3>
		<ol>
			<li>Only forward looping through the reader</li>
			<li>Code feels technical and maybe difficult</li>
			<li>Can't use 2sxc pipelines</li>
			<li>Can't use 2sxc-built-in serializers and other features</li>
		</ol>
	</div>
</div>

Look inside

Content Item

These are the values a content-editor can manage.
Name Type Value


Presentation Item

These are additional, optional presentation instructions a content-editor can manage. If none are entered, a default set (predefined by the designer) will be used.

No Presentation parameters specified, using default presentation for this item.


Template file

@using System.Configuration
@using System.Data
@using System.Data.SqlClient
@functions{
	private DataTable fileTable = new DataTable();

	// Official place to provide data preparation. Is automatically called by 2SexyContent
	public override void CustomizeData()
	{
		var conString = ConfigurationManager.ConnectionStrings[Content.ConnectionName].ToString();
		const string sqlCommand = "Select Top 10 * from Files Where PortalId = @PortalId";
		var adapter = new SqlDataAdapter(sqlCommand, conString);
		adapter.SelectCommand.Parameters.AddWithValue("@PortalId", Dnn.Portal.PortalId);
		adapter.Fill(fileTable);

		// for the demo, apply some operation to the data
		fileTable.DefaultView.Sort = "FileName DESC"; 
	}

}
<div class="sc-element">
	@Content.Toolbar
	<h1>Simple Demo with DataTable access</h1>
	<p>This demo accesses the data by filling it into a DataTable. </p>
	<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>
	<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>
</div>


<div class="ym-grid">
	<div class="ym-g50 ym-gl">
		<h3>Pros</h3>
		<ol>
			<li>Standard .net, no learning curve</li>
			<li>Allows further data manipulation in memory</li>
			<li>You can use the data a few times (reader is forward-only)</li>
			<li>Connection handling open/close is done automatically by the Adapter</li>
		</ol>
	</div>
	<div class="ym-g50 ym-gr">

		<h3>Cons</h3>
		<ol>
			<li>Code feels technical and maybe difficult</li>
			<li>no nice Object.Property-syntax</li>
			<li>Can't use 2sxc pipelines</li>
			<li>Can't use 2sxc-built-in serializers and other features</li>
		</ol>
	</div>
</div>

Look inside

Content Item

These are the values a content-editor can manage.
Name Type Value


Presentation Item

These are additional, optional presentation instructions a content-editor can manage. If none are entered, a default set (predefined by the designer) will be used.

No Presentation parameters specified, using default presentation for this item.


Template file

@functions
{
	// for PetaPoco you must first create a class containing the fields you want
	private class fileRecord
	{
		public int FileId { get; set; }
		public string FileName { get; set; }
		public int Size { get; set; }
		public int FolderId { get; set; }
	}

	private IEnumerable<fileRecord> files;
	
	// Official place to provide data preparation. Is automatically called by 2SexyContent
	public override void CustomizeData()
	{
		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);
		files = db.Query<fileRecord>(sqlCommand, Dnn.Portal.PortalId);
	}

}
<div class="sc-element">
	@Content.Toolbar
	<h1>Simple Demo with PetaPoco Data access</h1>
	<p>This demo uses PetaPoco as a mini-ORM to get the data. More info on <a href="http://www.toptensoftware.com/petapoco/" target="_blank">PetaPoco here</a>.</p>
	<h2>The top 10 files found in this portal as returned by PetaPoco</h2>
	<ol>
		@foreach (var file in files)
		{
			<li>@file.FileName (@file.FileId)</li>
		}
	</ol>
</div>

<div class="ym-grid">
	<div class="ym-g50 ym-gl">
		<h3>Pros</h3>
		<ol>
			<li>Typed data</li>
			<li>Entity-Framework-like feeling without needing pre-compile</li>
			<li>Less code than the other direct data methods (SQL & DataTable)</li>
			<li>Short, brief syntax</li>
			<li>Would already support paging and other features (read the PetaPoco docs)</li>
		</ol>
	</div>
	<div class="ym-g50 ym-gr">
		<h3>Cons</h3>
		<ol>
			<li>Requires you to write classes for each type you need</li>
			<li>Lots of boilerplate / plumbing code for typed classes</li>
			<li>Numbered Parameters @@0 instead of @@PortalId</li>
			<li>Default mode with Query is forward-only like using a SQLReader</li>
			<li>Can't use 2sxc pipelines</li>
			<li>Can't use 2sxc-built-in serializers and other features</li>
		</ol>
	</div>
</div>


Look inside

Content Item

These are the values a content-editor can manage.
Name Type Value


Presentation Item

These are additional, optional presentation instructions a content-editor can manage. If none are entered, a default set (predefined by the designer) will be used.

No Presentation parameters specified, using default presentation for this item.


Template file

@using ToSic.Eav.DataSources
@functions
{
	// Default data initialization - should be the place to write data-retrieval code
	// In the future when routing & pipelines are fully implemented, this will usually be an external configuration-only system
	// For now, it's done in a normal event, which is automatically called by the razor host in 2SexyContent
	public override void CustomizeData()
	{
		var source = CreateSource<SqlDataSource>();
		// source.TitleField = "EntityTitle"; // not necessary, default
		// source.EntityIdField = "EntityId"; // not necessary, default
		// source.ConnectionString = "...";   // not necessary, we're using the ConnectionStringName on the next line
		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"]);

		// enable publishing
		Data.Publish.Enabled = true;
		Data.Publish.Streams = "Default,FileList";
	}
}
<br />
<div class="sc-element">
	@Content.Toolbar
	<h1>Automatic 2sxc Pipeline SqlDataSource</h1>
	<p>This demo uses the 2sxc Pipeline (req. 2sxc 6.0.6+). More info <a href="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>
<div class="ym-grid">
	<div class="ym-g50 ym-gl">
		<h3>Pros</h3>
		<ol>
			<li>Typed / dynamic entities</li>
			<li>nice syntax, same as any other 2sxc data</li>
			<li>Easy to configure</li>
			<li>Configuration instead of programming (less error-prone and less security risks)</li>
			<li>Benefits from automatic Configuration-Injection (like when @@IdFilter can be [QueryString:ProductId|0] )</li>
			<li>Entity-Framework-like feeling without needing pre-compile</li>
			<li>Less code than all other methods (SQL Reader, DataTable, PetaPoco)</li>
			<li>No boilerplate / plumbing code (like PetaPoco)</li>
			<li>Can benefit from other pipeline-features like additional filtering, paging, attribute-stripping</li>
			<li>Can be auto-serialized using 2sxc and is then in the default format for JavaScript use - <a href="?mid=@Dnn.Module.ModuleID&standalone=true&type=data&popUp=true" target="_blank">try here</a></li>
		</ol>
	</div>
	<div class="ym-g50 ym-gr">
		<h3>Cons</h3>
		<ol>
			<li>Probably a bit more performance overhead</li>
			<li>Might not fit all complex scenarios</li>
			<li>No built-in paging like with PetaPoco, probably in the future</li>
		</ol>
	</div>
</div>


Look inside

Content Item

These are the values a content-editor can manage.
Name Type Value


Presentation Item

These are additional, optional presentation instructions a content-editor can manage. If none are entered, a default set (predefined by the designer) will be used.

No Presentation parameters specified, using default presentation for this item.


Template file

@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 = new DataTable();
		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;
	}
}

<div class="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 <a href="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>

<div class="ym-grid">
	<div class="ym-g50 ym-gl">
		<h3>Pros</h3>
		<ol>
			<li>Typed / dynamic entities</li>
			<li>lots of control over object structure</li>
			<li>nice syntax, same as any other 2sxc data</li>
			<li>any kind of source could be used - XML, file-lists, etc.</li>
			<li>Entity-Framework-like feeling without needing pre-compile</li>
			<li>Can benefit from other pipeline-features like additional filtering, paging, attribute-stripping</li>
			<li>Can be auto-serialized using 2sxc and is then in the default format for JavaScript use</li>
			<li>to see the automatic RSS-feed <a href="?mid=@Dnn.Module.ModuleID&standalone=true&type=data&popUp=true" target="_blank">try here</a></li>
		</ol>
	</div>
	<div class="ym-g50 ym-gr">
		<h3>Cons</h3>
		<ol>
			<li>Probably a bit more performance overhead</li>
			<li>more boilerplate / plumbing code (like PetaPoco)</li>
		</ol>
	</div>
</div>

2serve . 2invent . 2create is 2be.