locked
ADO > SQL Server Identity column question RRS feed

  • Question

  • I am working with strongly typed datasets inside a c# application which uses ADO.NET to populate a SQL Server 2005 database. ADO does not seem to recognize the actual current identity seed value in the database and I am wondering what I can do to prevent that.

    For example, if I insert a row into a table, and column A is IDENTITY(1,1), column A will then have a value of 1. Let's say I now DELETE that row, and add another row, column A will be 2.

    In ADO however, it sees no rows in the dataset, so assumes the seed value is the next available, and says that column A should be 1.

    This poses problems for me as I am working with Parent/Child tables within one class.

    For example, I insert a row into a DataTable:

    CustomerDS.CustomerRow row = dsCustomer.Customer.NewCustomerRow();

    row.blah = x;

    row.blah2 = y;

    etc..

    dsCustomer.Customer.AddCustomerRow(row)

    The Customer table has an identity field, which is used as a CustomerID. I need  to capture that ID - not as it might be in the DataSet - but as what it will be in SQL Server.

    So I do this:

    CustomerID = Convert.ToInt32(dsCustomer.Customer.Rows[dsCustomer.Customer.Rows.Count-1]["CustomerID"].ToString());

    I can then use that CustomerID to insert into the child tables. Which works for the most part, until stuff is deleted from the tables, and ADO can no longer recognize the actual identity value.

    Does this make sense? I'm totally lost here and I have posted this throughout several forums with zero response....

    • Moved by Stoyko Kostov - MSFT Friday, March 20, 2009 9:49 PM DataSet-specific question (Moved from SQL Server Data Access to ADO.NET DataSet)
    Thursday, March 19, 2009 4:33 PM

Answers

  • Another related thread
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/834b2817-5154-4579-bc8b-bcdce0c7d8cc

    Developers generally have the DataTable generate negative numbers for the autoincrement (seed -1, step -1).
    That way unsaved DataRow will have a negative ID and rows from the server have positive ID, usually without any overlap. 

    Then when saving the DataRow, select back the server generated ID and update the DataRow which can propagate from the parent row to the child row.
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Saturday, March 21, 2009 12:36 AM

All replies

  • Am I asking in the wrong forum? If so, can someone direct me to a forum more fitting for my question? 
    Thursday, March 19, 2009 8:43 PM
  • Hi Justin,

    I think you want to get back the identity number that was given when the row was inserted.

    INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager'
     
    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] 


    This might help you... scope_identity will give you the latest identity returned...
    Friday, March 20, 2009 10:22 AM
  • Thank you Fabrice for responding..

    Yes, I want to get back the identity number that was given when the row was inserted, but I need it when it is inserted into the dataset not the sql server database. 

    I am inserting batches of like 5000 rows into the database at one time, and inserting into the dataset row by row

    Friday, March 20, 2009 1:28 PM
  • It seems to be a current problem for C# developper !
    I found this paper interesting :
    http://csharplearnings.blogspot.com/2008/08/updating-identity-columns-in-dataset.html
    don't know if it could help you ...
    Friday, March 20, 2009 1:46 PM
  • Another related thread
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/834b2817-5154-4579-bc8b-bcdce0c7d8cc

    Developers generally have the DataTable generate negative numbers for the autoincrement (seed -1, step -1).
    That way unsaved DataRow will have a negative ID and rows from the server have positive ID, usually without any overlap. 

    Then when saving the DataRow, select back the server generated ID and update the DataRow which can propagate from the parent row to the child row.
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Saturday, March 21, 2009 12:36 AM