fetching sql aliases from custom tables in umbraco - Extending Umbraco and using the API

Tom Engan 430 posts 1173 karma points

Sep 13, 2017 @ 08:42

Fetching SQL alias from custom tables in Umbraco database

Extending Umbraco and using the API

Umbraco 7

The SQL statement works as it should in SQL Server Management Studio - displays all the values from alias and all fields.

But how do we extract the value of SQL alias NumberOfHikingDestinations into the model?

The field I want values from is retrieved with: COUNT(Id) AS NumberOfHikingDestinations below.

public static IList<HikingDestinationViewModel> GetHikingDestinationGroupedByMember(int NodeId)
{
    UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;

    return db.Fetch<HikingDestinationViewModel>(
        "SELECT DISTINCT COUNT(Id) AS NumberOfHikingDestinations, nodeId, SelectedHikingDestination, HikingCode " +
        "FROM [HikingDestinations] WHERE DATEPART(yyyy, StartDate) >= DATEPART(yyyy, GETDATE()) AND SelectedHikingDestination = @0 " +
        "GROUP BY SelectedHikingDestination, nodeId, HikingCode ORDER BY NumberOfHikingDestinations DESC", NodeId);
}

These are parts of the model HikingDestinationViewModel I use:

public int nodeId {get; set; }
public int? SelectedHikingDestination {get; set; }
public DateTime StartDate {get; set; }
public string HikingCode {get; set; }

which refers to the values in a custom database table [HikingDestinations]:

enter image description here

and in the surface controller the sql results is looped and the values can be obtained in this foreach loop:

IEnumerable<HikingDestinationViewModel> allHikingDestinationsGroupedByMember = HikingDestinations.GetHikingDestinationGroupedByMember(Node.getCurrentNodeId());
foreach (HikingDestinationViewModel hikingDestination in allHikingDestinationsGroupedByMember)
{
    //Codes to fetch the values from the databasetable [HikingDestinations] goes here..
    //Values OK (not empty) from nodeId, SelectedHikingDestination, HikingCode            
    //Values EMPTY in hikingDestination.NumberOfHikingDestinations
}

If I put this in model HikingDestinationViewModel:

public int NumberOfHikingDestinations { get; set; }    << NOTE: I've tried int? and int

and I try to retrieve hikingDestination.NumberOfHikingDestinations in the loop of the surface controller, the content is empty.

So how are we doing this, fetching data from SQL alias NumberOfHikingDestinations?

Copy Link

Tom Engan 430 posts 1173 karma points

Sep 14, 2017 @ 13:23

The solution: Add [Column("NumberOfHikingDestinations")] to HikingDestinationViewModel, and the SQL alias COUNT(Id) AS NumberOfHikingDestinations are now defined with values in the foreach loop:

[Column("nodeId")] 
public int nodeId { get; set; }

[Column("SelectedHikingDestination")]
public int? SelectedHikingDestination { get; set; } 

[Column("StartDate")]
public DateTime StartDate { get; set; }  

[Column("HikingCode")]
public string HikingCode { get; set; }

[Ignore] // Column not created in the custom Umbraco database table
[Column("NumberOfHikingDestinations")]
public int NumberOfHikingDestinations { get; set; }

Now the class in my repository can run the SQL string with all defined columns from HikingDestinationViewModel

public static IList<HikingDestinationViewModel> GetHikingDestinationGroupedByMember(int NodeId)
{
    UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;

    return db.Fetch<HikingDestinationViewModel>(
        "SELECT DISTINCT COUNT(Id) AS NumberOfHikingDestinations, nodeId, SelectedHikingDestination, HikingCode " +
        "FROM [HikingDestinations] WHERE DATEPART(yyyy, StartDate) >= DATEPART(yyyy, GETDATE()) AND SelectedHikingDestination = @0 " +
        "GROUP BY SelectedHikingDestination, nodeId, HikingCode ORDER BY NumberOfHikingDestinations DESC", NodeId);
}

Then looping all the values from the repository in the surfacecontroller,:

IEnumerable<HikingDestinationViewModel> allHikingDestinationsGroupedByMember = HikingDestinations.GetHikingDestinationGroupedByMember(Node.getCurrentNodeId());
foreach (HikingDestinationViewModel hikingDestination in allHikingDestinationsGroupedByMember)
{
    //Codes to fetch the values from the custom db table [HikingDestinations], defined in HikingDestinationViewModel goes here..
}

Then the result was send to the partial view, and the calculations now works (showing numbers other than 0).

Copy Link

Tom Engan 430 posts 1173 karma points

Sep 15, 2017 @ 09:15

enter image description here But of course, always something that is not going to work. I get the same errormessage with insert and update.

The error message come if I use [Ignore]

[Ignore] // Column not created in the custom Umbraco database table
[Column("NumberOfHikingDestinations")]
public int NumberOfHikingDestinations { get; set; }

in my viewmodel HikingDestinationViewModel instead of

[Column("NumberOfHikingDestinations")]
public int NumberOfHikingDestinations { get; set; }

That is, in order not to get an error message, I have to create an empty column named NumberOfHikingDestinations in custom database table [HikingDestinations] (or not use [Ignore] in my viewmodel).

This is not quite optimal, so someone who knows how to avoid having to create an empty column?

Copy Link

is working on a reply...

Flag this post as spam?

Fetching SQL alias from custom tables in Umbraco database