I've spent some time figuring out how to keep data integrity intact when working with several tables in my SQL Database. Several solutions are possible, and one of them are to create a stored procedure which takes care of updating several tables at once. I think that would be the wisest thing to do. However, I've postponed that solution for a while and made it work directly in my web service.

I discovered the @@IDENTITY attribute you can use in SQL. The @@IDENTITY attribute (or directive or whatever it is called) gives you the latest identifier for the table. So I've added it to my query when I insert an entry into the SavedEntries table:

 myCommand.CommandText = "INSERT INTO SavedEntries (ProjectName) " +
                        "VALUES ('" + projName +"'); SELECT @@IDENTITY;";

Then I save the identity just selected:

string latestIdentity = myCommand.ExecuteScalar().ToString();

Finally, I alter the MetricsData table using the identity as a foreign key to the column EntryID:

myCommand.CommandText = "INSERT INTO MetricsData (EntryID, Metrics1, Metrics2, Metrics3, Metrics4, Metrics5) " +
                                       "VALUES (" + Convert.ToInt32(latestIdentity) + "," +
                                       theMetrics[0] + "," +
                                       theMetrics[1] + "," +
                                       theMetrics[2] + "," +
                                       theMetrics[3] + "," +
                                       theMetrics[4] + ");";

I don't know if the conversion to Int32 is necessary (second row above), but it feels safer because latestIdentity is declared as a string.

Anyway, it works. Now I just have to make it work on the VPC. Furthermore, I'll give it a thought about implementing this in a stored procedure instead. We'll see what happens :-). Feedback and thoughts on this matter are much appreciated!