is there a more effective way to insert new row of data in multiple table [modified]

[neodeaths]

hi i am currently using vs.net 2005 c# window application and using ms sql as the database. usually when i create database it is good practice to separate our database to such as below: table: dad table: child where by when inserting data we have to add the dad data first then the child. insert data into dad table insert data ,max(index) from dad into child table my question is there a more effective way to do this like inserting the data in one sql command while inserting the newest index from the dad table to the child table as a reference. and thus if there are any error during the adding of data to any of the table both table will be then unaffected.

modified on Saturday, August 15, 2009 1:01 PM

[Henry Minute]

neodeaths wrote:

and thus if there are any error during the adding of data to any of the table both table will be then unaffected.

There is a mechanism built into SqlServer to take care of situations like that. It is called Transactions this article on transactions[^] covers the basics. I found it by Googling for sqlserver transactions c# which gives loads of hits. Explore for yourself, experiment, have fun. :)

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[Mycroft Holmes]

As Henry suggested transactions are the way to control this process. However I have trouble with you using max(index). You should be using an identity field for your primary key and then get the scope_identity() value from the dad insert to apply to the child record. I rarely use transactions but I consider them valid for processing related DATA, while the dad/child data is related I don't think a transaction is warranted. I would have a proc that inserts dad and returns the scope_identity() as DadID and then insert the child in a separate proc.

Never underestimate the power of human stupidity RAH

[Ashfield]

Mycroft Holmes wrote:

while the dad/child data is related I don't think a transaction is warranted

A bit of a sweeping staement there. It all depends, if you need to have both records inserted or niether of them then it should be a done in a transaction, but if orphan records are acceptable then there is no need.

Mycroft Holmes wrote:

I would have a proc that inserts dad and returns the scope_identity() as DadID and then insert the child in a separate proc.

Fine, unless you must have them both inserted. In your senario it would be possible to have Dad records with no Child records - maybe, depending on the application, this could be an invalid case.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Mycroft Holmes]

Ashfield wrote:

A bit of a sweeping staement there

That's why I stated it as a personal view, and yes it must be driven by the business requirements every time. I generally work with batch processing and rarely need trans.

Ashfield wrote:

Dad records with no Child records

I would assume that unless the business required the relationship - dad does imply it I must admit.