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.
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.
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!
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)
but i would be really careful with Stored procedures, they tend to be very SQL specific so you probably will end up with code that will likely only work on one type of Database provider.
Hmm yeah I had thought of that being an issue, what is the alternative then, just plain sql within a DAL or sorts? I’d be interested to know more into how uSync does custom db work when installing etc.
I went the stores procedure route for the compiled natured of them.
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.
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.
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
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.Sqlto run code to createthis 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)
Kevin, I’ve been looking at this and your “DoStuff” git repo, what would the equivalent be in Umbraco 9 using a migration plan?
Hi,
I believe you could put the SQL string in the
Database.Executecommandhttps://github.com/KevinJump/DoStuffWithUmbraco/blob/NetCore/src/DoStuff.Core/Migrations/ExecuteSql/ExecuteSqlMigration.cs
but i would be really careful with Stored procedures, they tend to be very SQL specific so you probably will end up with code that will likely only work on one type of Database provider.
Hmm yeah I had thought of that being an issue, what is the alternative then, just plain sql within a DAL or sorts? I’d be interested to know more into how uSync does custom db work when installing etc.
I went the stores procedure route for the compiled natured of them.
Did anyone get this to work? My stored procedures has parameters like
@myParam nvarchar(255), so when I try to runExecute.Sql()on that string, I get errors like this:I believe this is because the
Sqlobject parameterizes the string you pass it.HI Mark,
i believe in you can escape the @ in the param value by using two @ symbols so
@myParambecomes@@myParamKevin
is working on a reply...