none
linq to sql obtain primary key value RRS feed

  • Question

  • In a C# 2010 application that I am working on, I want to use linq to sql to update 2 tables in a sql server 2008 database.

    I want to do the following:
    create new Table1 object ;
    InsertOnSubmit(tbl1)
    **Table 1 will contain the primary key.
    create new Table2 object;
    table2 will contain a foreign key column that refers to primary key in
    table1 object.
    InsertOnSubmit(tbl2)
    SubmitChanges()

    Before and/or right after the submitchanges() event occurs, I would like to know what the value is for the primary key in table1 and the foreign key value is in table2. I would like to know what the table key value is so I can display this information on reports that will be generated right after the record(s) have been inserted into the database. Thus can you tell me how to determine what the primary key value is for table1 that also refers to the foreign key value in table 2?


    • Edited by scamper_cat Saturday, August 25, 2012 1:31 PM
    Saturday, August 25, 2012 5:31 AM

Answers

  • Hi scamper_cat;

    You can do your code a little different then you have shown, something line the following.

    //Create Table 1 object
    Table1Object t1 = new Table1Object();
    // Fill in the fields
    // ...
    //Create Table 2 object
    Table2Object t2 = new Table2Object();
    // Fill in the fields
    // ...
    // Assign Table2Object to the collection in Table1Object / Navigation property
    t1.NavPropertyToT2.Add(t2);
    // Add t1 to context on insert which will automatically add t2 to context insert
    Context.Table2Object.InsertOnSubmit(t1);
    // Update the database
    Context.SubmitChanges();
    // To get the primary key of the inserted record just access the primary key field
    // after the SubmitChanges method call. 
    int pk = t1.PrimaryKeyFieldName;

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".


    Saturday, August 25, 2012 6:00 PM
  • Hi scamper_cat;

    To your question, "I believe the production code is similar to your example, correct?" The code does the same thing. But the code I posted does it in less steps by assigning the child object to the parent object and inserts the parent object into the DataContext which the DataContext will handle inserting both objects and updating both primary keys and any foreign keys as are needed.

    To your question, "If not, can you show me the code that would allow me to access the primary key of Th tran table?", As I stated earlier to access the primary key use the local variable you created. From your last post you have a variable called t which is of type Transaction. After you update the database with SubmitChanges you use that same variable t to view what the database assigned as the primary key. So lets assume that the primary key in the Transaction table is called TranID. Then to see what was assigned as the primary key do the following.

    int primaryKey = t.TranID;

    To your question, "After the submitchanges command has been executed, how can I access some of the data that I just inserted into the tables?", The same way you get the primary key above. So lets say in the db table there is a column named FirstName then to access that value FirstName do the following.

    var fName = t.FirstName;

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by scamper_cat Sunday, August 26, 2012 2:33 AM
    Saturday, August 25, 2012 11:04 PM

All replies

  • Hi scamper_cat;

    You can do your code a little different then you have shown, something line the following.

    //Create Table 1 object
    Table1Object t1 = new Table1Object();
    // Fill in the fields
    // ...
    //Create Table 2 object
    Table2Object t2 = new Table2Object();
    // Fill in the fields
    // ...
    // Assign Table2Object to the collection in Table1Object / Navigation property
    t1.NavPropertyToT2.Add(t2);
    // Add t1 to context on insert which will automatically add t2 to context insert
    Context.Table2Object.InsertOnSubmit(t1);
    // Update the database
    Context.SubmitChanges();
    // To get the primary key of the inserted record just access the primary key field
    // after the SubmitChanges method call. 
    int pk = t1.PrimaryKeyFieldName;

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".


    Saturday, August 25, 2012 6:00 PM
  • Can you tell me how obtain selected values from the two rows I just inserted without doing a call to the database?
    Saturday, August 25, 2012 6:25 PM
  • Hi scamper_cat;

    To your question, "Can you tell me how obtain selected values from the two rows I just inserted without doing a call to the database?", You must do a SubmitChanges before you can access the primary keys in the new records.  The two rows exist in local memory in the variables t1 and t2 from the sample I posted. After the SubmitChanges method call the Framework will insert the new data in t1 and t2 into the database. Then after the database completes the insert the database return the primary keys of the inserted records back to the data context in your code and it will update the variable t1 and t2 with there respective primary key values. At this point to access the fields of those two rows of data use the t1 and t2 variables to access there properties.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, August 25, 2012 6:48 PM
  • I want to mention the following items:

    1. You mentioned that I should use your code example listed above. However the production code I am working with looks like the following:

         DtDataContext didc = new DtDataContext();
          Tran t = new Transaction();
           EnrollD ed = new EnrollD();
           didc.Tran.InsertOnSubmit(t);
           ed.Tran = t;
           didc.EnrollDiInsertOnSubmit(ed);
           didc.SubmitChanges();                                                                                                                                                                                                              I believe the  production code is similar to your example, correct? If not, can you show me the code that would allow me to access the primary key of Th tran table?

    2. After the submitchanges command has been executed, how can I access some of the data that I just inserted into the tables?  do I need to run a linq to sql query? Can you show me some code that I can use as an example?                                                                                                                                                                                                                                                                                                                                                                                                                        

    Saturday, August 25, 2012 9:40 PM
  • Hi scamper_cat;

    To your question, "I believe the production code is similar to your example, correct?" The code does the same thing. But the code I posted does it in less steps by assigning the child object to the parent object and inserts the parent object into the DataContext which the DataContext will handle inserting both objects and updating both primary keys and any foreign keys as are needed.

    To your question, "If not, can you show me the code that would allow me to access the primary key of Th tran table?", As I stated earlier to access the primary key use the local variable you created. From your last post you have a variable called t which is of type Transaction. After you update the database with SubmitChanges you use that same variable t to view what the database assigned as the primary key. So lets assume that the primary key in the Transaction table is called TranID. Then to see what was assigned as the primary key do the following.

    int primaryKey = t.TranID;

    To your question, "After the submitchanges command has been executed, how can I access some of the data that I just inserted into the tables?", The same way you get the primary key above. So lets say in the db table there is a column named FirstName then to access that value FirstName do the following.

    var fName = t.FirstName;

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by scamper_cat Sunday, August 26, 2012 2:33 AM
    Saturday, August 25, 2012 11:04 PM