none
Changing Stored proc dynammically RRS feed

  • Question

  • Hi Guys,

    I use linq to sql with storec procs. basically , I have an entity that I need to update, but I want to use different stored procs at different time to update the entity.
    for an example, based on a parameter sent to service, I would want to update this entity differently, in this I mean, one insert SP just updates the entity table and the other updates the entity table and does some releated changes in other tables as well.
    So how can I do this with LINQ to SQL with SPs?, is there a way I can do this?

    Thanx,
    Nairooz Nilafdeen

    Wednesday, November 11, 2009 4:19 PM

Answers

  • You can although you'll need to do a bit of programming.

    Map both your stored procedures as functions in the DBML and choose one as the update mechanism for that entity.

    Head into the .designer.cs (or .designer.vb file - use show all files to see it), and find the section of code Entity_Update (where Entity is the name of your entity). Copy the entire method and paste it into the actual .cs/.vb file for your DBML (hit view code from the designer surface to create it).

    Now go and change the DBML to use the other stored procedure and copy that to your class too. Finally set it back to not using either.

    Look at your actual .cs/.vb file that now contains two Entity_Update methods and simply turn them into one method that calls either the first or the second based on your parameter/condition.

    [)amien
    Wednesday, November 11, 2009 5:35 PM
    Moderator
  • I think you misunderstand my instructions.

    If LINQ to SQL finds an Entity_Update method it will use that during SubmitChanges instead of dynamically generating SQL.

    If you follow my instructions you just need to call SubmitChanges, you do not call Entity_Update directly (it will have the conditional logic to call either of the two stored procedure method wrappers).

    [)amien
    • Marked as answer by Yichun_Feng Wednesday, November 18, 2009 2:12 AM
    Thursday, November 12, 2009 7:21 AM
    Moderator

All replies

  • You can although you'll need to do a bit of programming.

    Map both your stored procedures as functions in the DBML and choose one as the update mechanism for that entity.

    Head into the .designer.cs (or .designer.vb file - use show all files to see it), and find the section of code Entity_Update (where Entity is the name of your entity). Copy the entire method and paste it into the actual .cs/.vb file for your DBML (hit view code from the designer surface to create it).

    Now go and change the DBML to use the other stored procedure and copy that to your class too. Finally set it back to not using either.

    Look at your actual .cs/.vb file that now contains two Entity_Update methods and simply turn them into one method that calls either the first or the second based on your parameter/condition.

    [)amien
    Wednesday, November 11, 2009 5:35 PM
    Moderator
  • Hi, Tx for te reply, I was expecting not expecting to call the update method like this... if(x = 100) { context.UpdateEntity1(entity); } else { context.UpdateEntity2(entity); } but what I was expecting was to use SubmitChanges() like this.... //at this point I need to tell linq to use different update SPs contezt.Entity.Attach(entity); context.SubmitChanges() I tried out your solution, having the methods in the partial classes and calling the required one, but is there a way I can do this using sps and context.SubmitChanges.
    Thursday, November 12, 2009 1:40 AM
  • I think you misunderstand my instructions.

    If LINQ to SQL finds an Entity_Update method it will use that during SubmitChanges instead of dynamically generating SQL.

    If you follow my instructions you just need to call SubmitChanges, you do not call Entity_Update directly (it will have the conditional logic to call either of the two stored procedure method wrappers).

    [)amien
    • Marked as answer by Yichun_Feng Wednesday, November 18, 2009 2:12 AM
    Thursday, November 12, 2009 7:21 AM
    Moderator
  • Tx Damien,

    I understood it now, but as I understand, this would work if the consitional prameter is a field in the entity I am updating, but I the condition that I have is not found in the entity, but comes as a service layer para,eter.
    Can you explain how I could do this?


    Regards,
    Nairooz nilafdeen
    Thursday, November 12, 2009 5:42 PM
  • So where is this parameter/property stored?  As long as you can access it from Entity_Update and it is not slow to access it should be fine.

    [)amien
    Friday, November 13, 2009 12:19 AM
    Moderator