Copied to clipboard

Flag this post as spam?

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


  • Ryios 122 posts 263 karma points
    Jul 30, 2015 @ 21:14
    Ryios
    0

    Umbraco PetaPoco, create stored procedures and views?

    Using PetaPoco and Umbraco's Persistence namespace,

    Is there anyway to check if a stored procedure exists, and if not run it's create procedure sql?

    I'm thinking I can just run a query to check for the stored procedure and return true/false, and if false, run the Create Procedure, using PetaPoco's Sql object and executing it against the umbraco database context.

    However, I wanted to post here to see if anyone has tried this before I get to that piece.

    Basically, I have 3 custom tables my plugin has added (requires Ms Sql Server) and this plugin is mostly for in company use where I work.

    For performance reasons, and ease of use in the API I've created views and stored procedures for working with these 3 tables.

    In short, it's a permissions model that uses a bit flag based permission value and determines what permissions members have to content nodes for our plugin.

    E.g.

    -Create Category -Rename Category -Delete Category -Move Category -Create Article .... etc.

    The permissions can be assigned to any content node, and they inherit down. Each content node can specific "break inheritance" to cause it to get new members/roles that aren't inherited from it's parents.

    A permission can also be added to specific members to deny them access and deny trumps grant.

    I've created a view that flattens permissions for members per content node.

    For example,

    If Bob inherits EditArticle permission from 1046, and is granted CreateArticle Permission on 1047 (child of 1046) then Bob's flat permission for 1046 will be EditArticle and CreateArticle (returns 1 row for 1046 with the combined XOR'd values of it's parents until it hits a BreakInheritance).

    Works great..

    I just want to be able to easily create them on fresh deployments accross our environments.

    I could do all the logic in the application, but it's a lot faster doing the heavy sql lifting in a stored proc allowing the application code to be simplistic.

    I noticed that umbraco doesn't use a single stored proc or view though, so I'm not sure there is anything in the API to do this, and PetaPoco looks like I'll have to do what I suggested above.

  • Pantelis 53 posts 107 karma points
    Mar 16, 2017 @ 12:24
    Pantelis
    0

    Hi Ryios,

    I was wondering if you could share your solution to this, I'm in a similar situation where I have the stored procedures but no way to create them in any fresh deployment other than running a script manually!

    Thanks,

    Pantelis

  • Kevin Jump 1734 posts 11104 karma points MVP 4x c-trib
    Mar 16, 2017 @ 18:58
    Kevin Jump
    2

    Hi Pantelis.

    for later versions of umbraco (v7.3+) you should look at migrations.

    https://cultiv.nl/blog/using-umbraco-migrations-to-deploy-changes/

    with migrations you can write some code that only runs first time your code is installed, and you could run the sql to check / add the stored procedure.

    Within a migration you can call Execute.Sql to run code to create

        public override void Up()
        {
          Execute.Sql("CREATE PROCEDURE ....  
          ...
        }
    

    this should then only run once and create your stored procedure (but you might want to add code to check if it's not already there - just incase)

  • Mark Bowser 265 posts 851 karma points c-trib
    Aug 29, 2018 @ 17:14
    Mark Bowser
    0

    Did anyone get this to work? My stored procedures has parameters like @myParam nvarchar(255), so when I try to run Execute.Sql() on that string, I get errors like this:

    Parameter '@myParam' specified but none of the passed arguments have a property with this name
    

    I believe this is because the Sql object parameterizes the string you pass it.

  • Kevin Jump 1734 posts 11104 karma points MVP 4x c-trib
    Aug 29, 2018 @ 20:51
    Kevin Jump
    0

    HI Mark,

    i believe in you can escape the @ in the param value by using two @ symbols so @myParam becomes @@myParam

    Kevin

Please Sign in or register to post replies

Write your reply to:

Draft