Need advice for Parent Child simultaneusly insert by many user

Posted on 2006-05-14
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
    LVL 20

    Expert Comment

    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

    LVL 20

    Expert Comment

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

    Author Comment

    Thanks for the advice

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now