(Sorry for my bad English)
I have imported an access database to a C# winform project (.net 4.0) in visual studio 2013. It automatically creates a .cs file with a DataSet, TableAdapter and a TableAdapterManager. I import data from the database to the DataSet, without error. I succeed to manipulate data, and save change to the database with TableAdapterManager.UpdateAll().
But now I try to insert new data, with relation between tables. For example, a database like mine
Parent table : autonum key string parentname
Child table autonum key string childname int parentKey
First try : I create a new record with parentTable.AddparenttableRow(data ...) and get a parentRow. I create a new record with childTable.AddchildtableRow(parentRow, data ...)
But if I call TableAdpaterManager.UpdateAll(), I get an error "can't add or modify a record because a related record is required in parentTable" (not the real message, it's a translation). I think that AddchildtableRow create the correct relation. And another problem appears : because of the error, the database isn't modified (which is good), but the records I had add, are always in the table of the DataSet.
So I try another method : TableAdpaterManager.tablenameTableAdpater.Insert() First I insert a parentRow without any problem. But when I want to insert a childRow, the insert function asks for the parent key. But I don't have it (the insert parent call doesn't return the key).
My question is : how can I use the DataSet, TableAdapter and TableAdapterManager to insert records in the DataSet AND in the database, and with a transaction (if there is an error, the data won't be written to the database, and won't be added to the DataSet) ? And actually, how to correctly use these classes ?
Look up the typed dataset code. Switch between the default TableAdapterManager.UpdateOrderOption.InsertUpdateDelete to UpdateInsertDelete (msdn). For hierarchical updates you have to merge new values for your identity columns (msdn). Also see this post. The way ADO.NET deals with preventing collisions with it's disconnected dataset, it assigns negative IDENTITY column values, because it wouldn't know a possible positive number that IS NOT a collision as it's disconnected. Also managing a @@identity crisis with parent-child relations. The typed dataset technology also had issues with circular table references.