Using CRUD Stored Procedures in PLINQO

[GoogleCodeExporter]

I would like to see PLINQO improve it's support for using stored procedures to 
do CRUD operations on entities. Most of the code is already there, 
but the hooks seem to be a little off. I generated a PLINQO project, modified 
the dbml to associate a stored procedure with a delete stored procedure on one 
of my 
entities. That is what was generated in the datacontext.generated.cs file:

         /// <summary>Called before a <see cref="Application"/> is deleted.</summary>
        /// <param name="instance">The instance.</param>
        [System.CodeDom.Compiler.GeneratedCode("CodeSmith", "6.0.0.0")]
        private void DeleteApplication(PSASecurity.Model.Application instance)
        {

            GspApplicationDelete(
                instance.ApplicationID,
                instance.AuditOperatorID);

        }

 This is actually correct. The only problem is the method is defined as being "private" when it should have been defined as being "public". I have it, 
 but I cannot call it without creating a public method in the datacontext.cs class just to call this method. Having this method definition go from 
 "private" to "public" would be a major step in the right direction as far as using CRUD stored procedures in PLINQO.

 As part of my project, I also generated a Manager for the entity. PLINQO created the following delete method to handle deletions:

         /// <summary>
        /// Immediately deletes the entity by the primary key from the underlying data source with a single delete command.
        /// </summary>
        /// <returns>The number of rows deleted from the database.</returns>
        [System.CodeDom.Compiler.GeneratedCode("CodeSmith", "6.0.0.0")]
        public int Delete(int applicationID)
        {
            return Entity.Delete(a => a.ApplicationID == applicationID);
        }

 This code will cause the runtime to delete my entity using SQL constructs instead of the delete stored proc that I associated with the operation. Is 
 there a way to have PLINQO support associating stored procedures with entities as defined in the dbml file so that the runtime will automatically use
 the stored procedure?

 Please let me know.

 Thanks,
 Andrew

Original issue reported on code.google.com by phantas...@gmail.com on 9 Feb 2012 at 2:53

[GoogleCodeExporter]

Hello,

Thanks for reporting this behavior. We will take a look into both of these 
issues and update this post accordingly.

I think the correct behavior should be that the managers and queries should 
both use your stored procedure for deletes. However, I'm assuming that there 
are no checks being done for this. If you could submit a patch for these two 
issues that would be greatly appreciated.

Original comment by bniemyjski on 17 Feb 2012 at 2:50

• Changed state: Investigating
• Added labels: Framework-PLINQO, Type-Enhancement

[GoogleCodeExporter]

Hi,

I was wondering if PLINQO was still under active development and if there are 
any updates to this issue? You are correct that both the Managers and Queries 
should call the appropriate CUD procedures instead of generating the raw SQL 
statements. I manually changed all the generated Managers to do what I need 
them to do, but now I want to use PLINQO against a much bigger DB (1000+ tables 
with over 3000 stored procedures for controlling CUD operations). Obviously 
changing all these by hand would be too time consuming so I am hoping that 
there has been a fixed to this issue.

Thanks for any help you may be able to give.

Original comment by phantas...@gmail.com on 20 Nov 2013 at 7:14

[GoogleCodeExporter]

Hello,

My apologies for not getting back to you. I lost track of this issue. I just 
looked into this and we generate the exact same thing that Microsoft does for 
stored procedures.

        /// <summary>Called before a <see cref="Inventory"/> is deleted.</summary>
        /// <param name="instance">The instance.</param>
        [System.CodeDom.Compiler.GeneratedCode("CodeSmith", "6.0.0.0")]
        private void DeleteInventory(PetShop.Data.Inventory instance)
        {
            PetShop.Data.Inventory original = Inventory.GetOriginalEntityState(instance);

            CSLAInventoryDelete(
                original.ItemId);

        }

        private void DeleteInventory(Inventory obj)
        {
            Inventory original = ((Inventory)(Inventories.GetOriginalEntityState(obj)));
            this.CSLA_Inventory_Delete(original.ItemId);
        }

This appears to be the correct behavior. When you call 
table.DeleteOnSubmit(entity) it should call this stored procedure that is 
private. Please see this for more information:

http://msdn.microsoft.com/en-us/library/bb546178(v=vs.110).aspx?cs-save-lang=1&c
s-lang=csharp#code-snippet-2
http://www.west-wind.com/weblog/posts/2009/Jul/14/LINQ-to-SQL-and-Transactions

If you wish to make this public (not recommended as it's as designed by 
microsoft) then you would need to update line 271 of DataContext.Generated.cst.

Please let me know if you have any questions.

Original comment by bniemyjski on 21 Nov 2013 at 4:11

• Changed state: WontFix

[GoogleCodeExporter]

Hi,

Thank you for your quick response. Your code example really cleared things up 
for me. I was not calling the table.DeleteOnSubmit(entity) method to delete the 
record but instead calling dbcontext.Manager.table.delete(entity.id). Using the 
DeleteOnSubmit method called the stored procedure as expected while 
dbcontext.Manager.table.delete(entity.id) used the runtime to delete the record.

Thanks once again.
Andrew

Original comment by phantas...@gmail.com on 22 Nov 2013 at 1:59

[GoogleCodeExporter]

No problem!

Original comment by bniemyjski on 22 Nov 2013 at 2:01