CodeSmith Community
Your Code. Your Way. Faster!

Self-referencing FK insert?

Latest post 09-22-2007 12:17 PM by MeasuredSpace. 4 replies.
  • 09-21-2007 5:06 PM

    Self-referencing FK insert?

    I have a table where I want to FK to the table's Primary key (so as to create parent/child relationships among the rows). The problem I have is getting the PK value on insert. I do an DataRepository.MyObjectProvider.Insert(TransactionManager, o_myobject) but obviously get an error when the FK value doesn't already exist in the table. I searche the list on how to enable this but come up short. I really don't want to remove the FK restraint on this column and the do TWO hits on the DB one to insert and then one to assign the PK value to the child row FK?

     My syntax:

    TransactionManager tm = DataRepository.Provider.CreateTransaction();
    try
    {
      tm.BeginTransaction();
      MyObject o_myobject = new MyObject();
      o_myobject.ThreadStarter = o_myobject.Id;
     
    o_myobject.Text = "Some text";
     
    DataRepository.MyObjectProvider.Insert(tm, o_myobject);
      tm.Commit();
    }
    catch (Exception ex)
    {
     
    if ((tm != null) && tm.IsOpen)
      {
      tm.Rollback();
      }
     
    throw ex;
    }

    TIA - Brad
    • Post Points: 35
  • 09-21-2007 5:19 PM In reply to

    • bgjohnso
    • Top 10 Contributor
    • Joined on 09-15-2005
    • Spokane, WA
    • Posts 764
    • Points 22,530

    RE: Self-referencing FK insert?

    Brad,

     

    I’m a little confused.  Are you tryingto have the record reference itself (ie. ThreadStarter = Id)?  In your example,where does o_comment come from?

     

     

    Ben Johnson
    ------------------------------
     Member of the .NetTiers team
     Visit http://www.nettiers.com
    ------------------------------

    • Post Points: 35
  • 09-21-2007 5:32 PM In reply to

    Re: RE: Self-referencing FK insert?

    ThreadStarter = Id (Yes exactly)

    Sorry, I was trying to genericize my sample syntax and forgot to edit the explination as well.  Disreagrd o_comment (should be o_myobject);

    I also edited the orginial post to correct my imposed confusion.

    • Post Points: 35
  • 09-22-2007 10:38 AM In reply to

    • bgjohnso
    • Top 10 Contributor
    • Joined on 09-15-2005
    • Spokane, WA
    • Posts 764
    • Points 22,530

    Re: RE: Self-referencing FK insert?

    Is you Id an identity field?  If so, I don't know how this could work.  Id wouldn't get a value until the record is saved to the database.  How would you do this using normal T-SQL?  You would have to do two statements (one for the insert followed by an update once the id get's a value), wouldn't you?  You could probably set up a trigger where you could set ThreadStarter to null and then have the trigger set the value to the Id. 

    Ben Johnson
    ------------------------------
     Member of the .NetTiers team
     Visit http://www.nettiers.com
    ------------------------------

    • Post Points: 35
  • 09-22-2007 12:17 PM In reply to

    Re: RE: Self-referencing FK insert?

    Awesome.. Thanks. Yes this is a PK identity filed.  You saved me from stabbing away at it to no avail. Makes total sense and yes in a straight T-SQL situation I would have to allow null on the column and then update subsequent to identity generation with insert. I like the trigger idea.

     -Brad

    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright © 2008 CodeSmith Tools, LLC
Powered by Community Server (Commercial Edition), by Telligent Systems