Need advice for Parent Child insert in transaction mode

Question

Need advice for Parent Child insert in transaction mode

Sandi Antono

SSC Enthusiast

Points: 174
More actions
May 9, 2006 at 9:30 am

#168037

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

wayneph SSC Eights! Points: 828 More actions · Answer 1

Why don't you create a Stored Procedure that does all of this at once? Then within the Procedure you can use Scope_Identity() instead of @@Identity to make sure that you get the proper ID from the parent table.

If you really don't want to use a Stored Procedure, you can proably add the 'Select Scop_Identity' to the first insert query to run it as a batch. Then use ExecuteScalar on your first insert instead of ExecuteNonQuery and you can skip the second trip to the database.

wayne

Sandi Antono SSC Enthusiast Points: 174 More actions · Answer 2

Thank for your advice wayne

Do you mean, i must change this code:

// 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();

with :

// Insert parent new record

myAtomCmd.CommandText = string.Format("insert into TESTPARENT (customer) values ('{0}')", tbCustomer.Text) + "; "SELECT @@identity from testParent";

myCounter = myAtomCmd.ExecuteScalar();

wayneph SSC Eights! Points: 828 More actions · Answer 3

I'd probably format it a little differently. I'd also use Scope_Identity instead of @@Identity. That's just to make sure that two different people don't get mixed up.

myAtomCmd.CommandText = string.Format("insert into TESTPARENT (customer) values ('{0}');SELECT scope_identity() from testParent", tbCustomer.Text);

myCounter = myAtomCmd.ExecuteScalar();

wayne