Fork me on GitHub
2sxc 9.0 for DNN 7 to 9
Website Builder, Content Manager, App-System: free and amazing - done your way
You are here: Home  >  Docs  >  Feature

SQL Server Data - using it with Razor

Introduced in Version 06.00.06
Demos of this feature

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>

Tags for this feature

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