Hi,
I'm writing an employee contact details application where each employee will have one or many contact addresses, emails and phone numbers. The database is normalized such that the main employee record holds simply an employee id, first name(s) and last name. I then have separate address, email and phone number tables with the employee id in these tables being a foreign key to the main employee record. The application will need to be flexible with respect to how many addresses/email/phone numbers the user enters for a particular employee.
My question relates to the best approach as far as inserting a new record is concerned. Should i
1. Create a single stored procedure that will contain multiple insert statements for the various tables and wrap the entire stored procedure from start to finish with a BEGIN TRAN.. COMMIT TRAN block OR
2. Create separate stored procedures for updating each table (i.e. a stored procedure to insert the employee into the main table, another stored procedure to insert the addresses using the @@IDENTITY value returned from the first stored procedure etc...), wrap each stored procedure within its own BEGIN TRAN...COMMIT TRAN and call each stored procedure, one after the other from the client code wrapping all of the calls within client initiated ADO.NET transactions.
I'm probably going to be using Datasets for retrieving data and direct command bypassing the dataset altogether for inserting, updating and deleting.
Thanks for your time
M