Press Ctrl / CMD + C to copy this to your clipboard.
This post will be reported to the moderators as potential spam to be looked at
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.
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.
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).
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.
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!
for later versions of umbraco (v7.3+) you should look at migrations.
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)
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.
i believe in you can escape the @ in the param value by using two @ symbols so @myParam becomes @@myParam
is working on a reply...
Write your reply to:
Image will be uploaded when post is submitted