Copied to clipboard

Flag this post as spam?

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


  • Thomas 160 posts 335 karma points
    Apr 08, 2015 @ 22:15
    Thomas
    0

    Calling a stored procedure and get results

    Hi,

    I need help - example how to call a custom (developed by me) store procedure and get the results using the DatabaseContext in Umbraco 7 and not the SqlHelper as it is obsolete.

    Regards

    Thomas

  • Jeroen Breuer 4908 posts 12265 karma points MVP 4x admin c-trib
    Apr 09, 2015 @ 09:57
    Jeroen Breuer
    1

    Hello,

    There are multiple ways of calling an sp. Do you need to get just 1 value back of multiple results?

    First you need to acces the petapoco database object:

    private Database Database
    {
        get { return ApplicationContext.Current.DatabaseContext.Database; }
    }

    Getting 1 result:

    SP Example:

    ALTER PROCEDURE [dbo].[spHAND_GetNodeIdByFeedId] 
        @feedId nvarchar(255),
        @alias nvarchar(255)
    AS
    BEGIN
    select cpd.contentNodeId as nodeId from cmsDocument cd --rest of the query END

    C# code:

    var result = Database.ExecuteScalar<string>(";EXEC spHAND_GetNodeIdByFeedId @feedId, @alias", new { feedId = feedId, alias = alias });

    Getting multiple results:

    SP Example:

    ALTER PROCEDURE [dbo].[spHAND_GetProjectsByIds] 
        @nodeIds nvarchar(max),
        @latitude FLOAT,
        @longitude FLOAT
    AS
    BEGIN
        SELECT *,
    --rest of the query END

    C# code:

    var projects = Database.Fetch<ProjectDto>
    (
        //The sp which needs to be called. In the sp the distance is calculated.
        ";EXEC spHAND_GetProjectsByIds @nodeIds, @latitude, @longitude",
    
        //The parameters passed to the stored procedure.
        new { nodeIds = nodeIds, latitude = latitude, longitude = longitude }
    );

    ProjectDto is the petapoco model.

    [TableName("beterwoneninProject")]
    [PrimaryKey("projectId")]
    [ExplicitColumns]
    public class ProjectDto
    {
        [Column("projectId")]
        [PrimaryKeyColumn()]
        public int ProjectId { get; set; }
    
        [Column("modificationDate")]
        public DateTime ModificationDate { get; set; }
    
        [Column("name")]
        public string Name { get; set; }
    }

    If you have an inner join in your SP you can also map to multiple petapoco dto objects, but that get's a bit more complicated.

    I hope this helps.

    Jeroen

  • Jeroen Breuer 4908 posts 12265 karma points MVP 4x admin c-trib
    Apr 13, 2015 @ 10:24
    Jeroen Breuer
    101

    Hello,

    Did the above example help you?

    Jeroen

  • Thomas 160 posts 335 karma points
    Apr 15, 2015 @ 09:28
    Thomas
    0

    Hi, 

    I am sorry for the late reply due to easter holidays in Greece i was out of office. Yes it helped me and now it is working.

    Thanks a lot

    Thomas

Please Sign in or register to post replies

Write your reply to:

Draft