Get new record ID when inserting data

[StrangerInBeijing]

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?

[Sgt-Peppa]

Hi,

i have only done that in my SqlServer Stored Procedures so far and returned the autogenerated Id.

Like that:

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