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

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

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

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

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