By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,314 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,314 IT Pros & Developers. It's quick & easy.

Need advice for Parent Child insert in transaction mode

P: n/a

I have Parent child table as decribe below:
Parent Table name = TESTPARENT
1. counter bigint isIdentity=Yes Increment=1 Seed=1
2. customer nChar(10)
Child Table name = TESTCHILD
1. counter bigint
2. qty numeric(3,0)

I want to insert new record with code below:
void button1_Click(object sender, EventArgs e)
{
// Define object to catch @@indentity
object myCounter;
// Connect to database & open
myConnection = new SqlConnection("Data Source=54ND1\\SQL2005;Initial
Catalog=Axioma;User ID=sa; Password=sandi");
myConnection.Open();
// define transaction
SqlTransaction myAtom = myConnection.BeginTransaction();
SqlCommand myAtomCmd = myConnection.CreateCommand();
myAtomCmd.Transaction = myAtom;
// Start insert to database with transaction mode
try
{
// Insert parent new record
myAtomCmd.CommandText = string.Format("insert into TESTPARENT
(customer) values ('{0}')", tbCustomer.Text);
myAtomCmd.ExecuteNonQuery();

// Get Indentity
myAtomCmd.CommandText = "SELECT @@identity from
testParent";
myCounter = myAtomCmd.ExecuteScalar();
// insert child new record
myAtomCmd.CommandText = string.Format("insert into TESTCHILD
(counter, qty) values ('{0}', {1})",
Convert.ToInt64(myCounter.ToString()), tbQty.Value);
myAtomCmd.ExecuteNonQuery();

// Commit transaction
myAtom.Commit();
}
catch
{
myAtom.Rollback();
MessageBox.Show("Data not inserted");
}
}
I already try with 2 workstation and 1 server, that code working well
(not duplicate in parent and insert right relation child parent record
in child table ).
If, i run with many many user, I am not sure that code will stay
stable.
Please advice, that code is the right way to archieve parent child
relation insert table??
I using C# and SQl Server 2005
Thank,s and regards
Sandi Antono

May 10 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.