Running SQL queries in 2sxc razor templates

Question

I use 2sxc for reasearch projects, and statistics is the most important part of the work I do. With link and visual queries I can do 90% of the work with no hassle at all. However, there are times where I need the optimized database table (with a field - value structure), for example to do a search over multiple columns on a single run (counting the number of "yes" in all questions for example). With a little fiddle I can get this type of data with the following query:

SELECT dbo.ToSIC_EAV_Attributes.StaticName, dbo.ToSIC_EAV_Values.Value
FROM dbo.ToSIC_EAV_Values
LEFT JOIN dbo.ToSIC_EAV_Attributes
ON dbo.ToSIC_EAV_Values.AttributeID = dbo.ToSIC_EAV_Attributes.AttributeID
LEFT JOIN dbo.ToSIC_EAV_AttributesInSets
ON dbo.ToSIC_EAV_Values.AttributeID = dbo.ToSIC_EAV_AttributesInSets.AttributeID
WHERE dbo.ToSIC_EAV_AttributesInSets.AttributeSetID = <my entity SetID>;

What I don't have is the knowledge to implement this on a view code in 2sxc.

How can I output the table that this query outputs?

And if I add a simple SELECT COUNT how can I place the result in a c# var?

Answer 1

So the core question is if you want to use visual query or if you want to use c# code in your view. I recommend visual query, but otherwise it's basically standard microsoft DB objects in c# (nothing related to 2sxc).

You can find some examples here https://2sxc.org/en/Docs/Feature/feature/2579 especially the Code Example with DataTable (no DataSource) or the DataReader example. It's a bit like this:

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