Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Tom Engan 363 posts 992 karma points
    Sep 13, 2017 @ 08:42
    Tom Engan
    0

    Fetching SQL alias from custom tables in Umbraco database

    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?

  • Tom Engan 363 posts 992 karma points
    Sep 14, 2017 @ 13:23
    Tom Engan
    100

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

  • Tom Engan 363 posts 992 karma points
    Sep 15, 2017 @ 09:15
    Tom Engan
    0

    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?

Please Sign in or register to post replies

Write your reply to:

Draft