I have a number of records to add to the database so Im using a Dataset to compose the table and then calling the adapter's Update(myDataSet, "myTable") to refresh the data source with the new records. The problem is with the autonumber primary key the database automatically generates for each new record added to it. I need this unqiue value for each row of my dataset because I'm creating another table which requires it.
I've been using the adapter's Fill(myDataSet, "myTable") in hopes of refreshing the dataset's primary key field with the proper values from the datasource but with no luck. However, calling with Fill(myDataSet, "myNewTable"), where myNewTable is just a new table name within the same dataset, does retrieve the actual primary keys from the data source. The problem I have with the second method is it retrieves ALL the records from the data source, including the newly added items) instead of just the new records, and more importantly the specific coulmn, that arent within the dataset.
I have no idea what the adapter's Update() command does underneath but using a dataset for all my new records and updating the datasource as one block seems like a more efficient method than calling the INSERT sql command, .ExecuteNonQuery(), then SELECT @@IDENTITY for each record. But how exactly do i retrieve the actual datasource primary keys for each record within the dataset that was added? ...Without having to retrieve the entire datasource again?
Hope this makes sense.