Skip to main content
Home  ›  Docs › Feature

SQL Server Data - using it with Razor

Warning ⚠️: New Docs Available

These are old docs and we haven't found the time to completely move them. You will find comprehensive new docs on docs.2sxc.org.

Introduced in Version 06.00.06

Now there is a DataSource called SqlDataSource which can do amazing stuff like

  1. Simple to configure
  2. Automatically uses Configuration-Providers to retrieve things like ModuleId, QueryString parameters and more
  3. Creates entities for further use (pipelines, JSON-streaming, and more)

Give it a try - the full demo is in the SQL Data Demo App.

Code sample with the official SQL Data-Source

@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"]);

       }

}

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

Code sample using PetaPoco

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

 

Code sample with a DataTable (no DataSource)

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

Code Sample with DataReader

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