today I've a question about how to get the last inserted record id of a given sql query in .NET when using .SqlExecute command on a VServer in a asynchronous scenario on different tables.
On one db client (.NET) we open a connection to a VServer.
Our client software creates data that should be inserted into the database asynchronously. Asynchronously in this context would mean that we have different master- and detail tables. First we insert a master record and get the id of that record back.
Now we have two (or more) threads that insert a few hundred records asynchronously into the one detail table each (one thread -> one detail table). Then we "collect" the inserted record ids and in the last step try to link them to the master record id.
Currently we try to get the .lastRecId from the database object to later link the records to the master record. But as soon as we use this function from the database object there may be some new records from one other thread in another table.
Then we get .lastRecId's back that belong to the wrong thread/table. Currently we get the .lastRecId through the corresponding function of our database object.
We found out that it is possible via SQL to get the last record id of a table but that would mean we've to "insert" -> then "select" and so on. Seems like some overhead to me.
So the question is: What would be the best way to get the .lastRecId from one .SqlExecute command without using the .lastRecId function from the database object and get it for each table instead?
The return data from the .SqlExecute function in .NET is only "0" or "1". It looks like this should only show sort of "false"/"true" if the query worked or not. Maybe there's another way to handle such a scenario.
Thanks in advance!