Get new record ID when inserting data
I am writing automated test cases. In this specific case, I need to enter temporary data into a table, run the test code, and then cleanup by deleting the temporary data I inserted.
What I do currently is storing the INSERT and DELETE statements in a XML file, and execute the INSERT and clean up using the DELETE statement. I feel this is lame.
For instance I got a ‘PerformanceData’ table, with columns ‘ID’, ‘DateCollected’, CounterID, ‘InstanceID’ and ‘Value’
The INSERT statement enter data into the last 4 Columns, as ‘ID’ is an id field, and it’s value is auto generated by sql server.
So, I want to execute the INSERT statement (from my C# code) in a way that I get this auto generated ‘ID’ value and store them in a hashtable (key=ID, Value=TableName). Then afterwards, I just loop through the hashtable, and build a simple DELETE statement using the TableName and ID value is store in the collection.
1) Can one do this?
2) Any advice on streamlining this process further?
Re: Get new record ID when inserting data
Hi,
i have only done that in my SqlServer Stored Procedures so far and returned the autogenerated Id.
Like that:
Code:
INSERT INTO myTable(ColumnTwo,ColumnThree,ColumnFour) VALUES("Two","Three","Four")
SET @myVariable = (SELECT @@IDENTITY AS 'ColumnId')
Thats one option. Maybe it works if you just execute the SELECT @@IDENTITY Statement right after the Update Satement in your code.
Worth a try!
Stephan
