Need advice for Parent Child simultaneusly insert by many user

Posted on 2006-05-14
Medium Priority
Last Modified: 2010-04-16
Need advice for Parent Child insert in transaction mode

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=SQL2005;Initial Catalog=XXX;User ID=sa; Password=YYY");

// define transaction
SqlTransaction myAtom = myConnection.BeginTransaction();
SqlCommand myAtomCmd = myConnection.CreateCommand();
myAtomCmd.Transaction = myAtom;

// Start insert to database with transaction mode
// Insert parent new record
myAtomCmd.CommandText = string.Format("insert into TESTPARENT (customer) values ('{0}')", tbCustomer.Text);

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

// Commit transaction
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 ).
I am not sure that code will stay stable when the table inserted simultaneusly by many user.
Please advice, that code is the right way.
Question by:jameslimin
  • 2
LVL 20

Expert Comment

ID: 16680250
It will be OK because @@identity returns the last ID inserted in the current session/transaction. So if two people insert a record, @@identity will return the correct value for every one of them. The worst thing that can happen is that if the child insert fails, one ID of the parent will be skipped, but this should be no problem for you.
LVL 35

Accepted Solution

mrichmon earned 150 total points
ID: 16683324
LVL 20

Expert Comment

ID: 16683508
mrichmon is correct. My answer was wrong, I made a mistake between @@IDENTITY and SCOPE_IDENTITY

Author Comment

ID: 16683879
Thanks for the advice

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question