Whats the most efficient way to get a Primary Key for a row that has been added to DataTable and then to database via SqlDataAdapter.Update method? RRS feed

  • Question

  • Example of what I am doing:

    DataTable table1 = LoadTable();
    DataRow newrow = new DataRow();
    SqlDataAdapter adapter1 = new SqlDataAdapter();
    // initialize all of my row fields - not shown
    The code example is incomplete as I simply wanted to give an idea of what I need and not provide more info than necessary. I want the Primary Key of the newly INSERT'ed row in the DataTable() to have a Primary Key set after the SqlDataAdapter.Update(table) method is executed. I could refresh the entire DataTable() with the SqlDataAdapter.Fill(table) method but this seems very inefficient since I only need the Primary Key of the newly inserted record. I could also query the database for the newly inserted record and get the key somehow that way. Im just wondering if there is an easy way that I am missing. Maybe the SqlDataAdapter.Update() method itself provides this Primary Key after an insert? Thanks for help on this one.

    System configuration: Winndows 7 RTM, Gigabyte GA-EP45-UD3L motherboard, Intel Pentium Dual-Core E6300 Wolfdale @ 2.8GHz, 4GB PC-1066 DDR2 Patriot Viper Memory, 500 GB Western Digital Hard Disk, GeForce 8800GT Videocard
    Thursday, August 12, 2010 5:01 PM


  • Yes, ADO.NET has solutions for this, but this is definitely one of the "rough edges"!

    The easiest way to do this is to use a strongly-typed DataSet and the wizard.  The wizard will automatically set things up such that the values in the DataSet are replaced with the actual identity values during the adapter Update call when the "Refresh the data table" option is chosen.  See: http://blogs.msdn.com/b/vsdata/archive/2009/09/14/refresh-the-primary-key-identity-column-during-insert-operation.aspx  It will cause there to be extra SELECTs to get the new value -- no way around that*.  Because there is an extra SELECT command for every INSERT command, is might not be substantially more efficient than what you are doing with Fill (unless there are many more rows in the DataSet than were actually last added).

    *No way around that using the code that ADO.NET automatically knows how generate.  You can probably improve upon it using SQL 2005 and up's INSERT OUTPUT clause, but this was never a significant issue for me to pursue that.

    Also read: http://www.betav.com/Files/Content/Articles/Managing%20and%20Identity%20Crisis.pdf

    For gory details: http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx

    Note that you don't really have to use the wizard.  You can study the code it generates and do the same thing on your own.  The link immediately above describes what to do, though it will probably be easier to follow if you study the code the wizard generates too.


    • Proposed as answer by Alex Liang Thursday, August 19, 2010 6:57 AM
    • Marked as answer by Alex Liang Thursday, August 19, 2010 10:30 AM
    Thursday, August 12, 2010 11:42 PM