Need advice for Parent Child insert in transaction mode

[sandi]

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

 

[David Browne]

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:

.. . .

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

Basically your code is correct. However, there are a few little things,
like not using parameters, and not using "using" blocks for the connection
and transaction. And you can get the inserted identity value in the same
batch as you insert the parent.

I think this is a bit cleaner:

static SqlConnection Connect()
{
SqlConnection con = new SqlConnection(
"Data Source=54ND1\\SQL2005;Initial Catalog=Axioma;User ID=sa;
Password=sandi");
con.Open();
return con;
}
static void InsertCustomer(string Customer, long Quantity)
{
using (SqlConnecton con = Connect())
using (SqlTransaction tran = con.BeginTransaction())
{
SqlCommand insertParent = new SqlCommand(
@"insert into TESTPARENT (customer) values (@customer);
set @ID = SCOPE_IDENTITY()", con, tran);

SqlParameter pCustomer =
insertParent.Parameters.Add("@customer", SqlDbType.VarChar, 50);
SqlParameter pParentID =
insertParent.Parameters.Add("@ID", SqlDbType.Int);
pParentID.Direction = ParameterDirection.Output;

SqlCommand insertChild = new SqlCommand(
"insert into TESTCHILD (counter, qty) values (@parent, @qty)",
con, tran);

SqlParameter pChildParent =
insertChild.Parameters.Add("@parent", SqlDbType.Int);
SqlParameter pChildQty =
insertChild.Parameters.Add("@qty", SqlDbType.BigInt);

pCustomer.Value = Customer;
insertParent.ExecuteNonQuery();

int parentID = (int)pParentID.Value;

pChildParent.Value = parentID;
pChildQty.Value = Quantity;
insertChild.ExecuteNonQuery();

tran.Commit();
}
}


David

 

[sandi]

Thank's for your advice David, I very appreciate that.

Base on your advice, i have some question:

1. I still don't get about
1. "like not using parameters, "
2. "and not using "using" blocks for the connection and transaction"
please more info Dav.... thank's

2. I am new in C# language, and i am comfort with my early code.
honestly, i read carefully your code for more than 30minutes and
...... oooops i only undestand not more than 10% ... poor me.
If i still use that code, what will happen Dav??
It will be cost a lot in future ??? how bad ???
I read on help, microsoft recommend just like you when i am using
transaction (transaction can execute like single command), but i can't
understand Dav (i read more than 100times), It's out my league, so i
used command like that

3. In your opinion Dav, When i used transaction then i don't need to
use try & cactch ??

Thank's Dav... have a nice day
Sandi Antono

 

[David Browne]

Thank's for your advice David, I very appreciate that.

Base on your advice, i have some question:

1. I still don't get about
1. "like not using parameters, "

In this code:

SqlCommand insertChild = new SqlCommand(
"insert into TESTCHILD (counter, qty) values (@parent, @qty)",
con, tran);

SqlParameter pChildParent =
insertChild.Parameters.Add("@parent", SqlDbType.Int);
SqlParameter pChildQty =
insertChild.Parameters.Add("@qty", SqlDbType.BigInt);

"@parent" and "@qty" are parameter markers. Using parameters is superior to
pasting together literal SQL strings for a couple of reasons. First, it's
easier, since you don't have to convert the values to and from their SQL
literal representation. You just declare the parameter type and ADO.NET
handles the conversion for your. Second, parameterized queries generally
perform better. Third they are more secure since they are immune to SQL
injection attacks.

2. "and not using "using" blocks for the connection and transaction"
please more info Dav.... thank's

See belos.

2. I am new in C# language, and i am comfort with my early code.
honestly, i read carefully your code for more than 30minutes and
..... oooops i only undestand not more than 10% ... poor me.
If i still use that code, what will happen Dav??
It will be cost a lot in future ??? how bad ???

No your code is OK, except that you're not closing the connection. That is
very bad. You need to use a TRY/CATCH to make sure to close the connection,
just like for the transaction. The "using" block is a C# construct that
cleans up all those nested TRY/CATCH blocks, and automaically rolls back the
transaction and closes the connection on any error.

I read on help, microsoft recommend just like you when i am using
transaction (transaction can execute like single command), but i can't
understand Dav (i read more than 100times), It's out my league, so i
used command like that

Honestly, that's OK. There's quite a few C#, ADO.NET and SQL constructs in
there you might not have seen before.

3. In your opinion Dav, When i used transaction then i don't need to
use try & cactch ??

"using" is like Try/Catch.

using (SqlTransaction tran = con.BeginTransaction())
{
//whatever

tran.Commit();
}

is equivilent to

SqlTransaction tran = con.BeginTransaction();
try
{
//whatever

tran.Commit();
}
catch (Exception ex)
{
tran.Rollabck();
}

A SqlTransaction is a Disposable object, and on Dispose it will rollback
if it hasn't been commited. So a using block is semantically equivilent to
a try/catch.


A copy with some comments.


static void InsertCustomer(string Customer, long Quantity)
{

//nested using blocks will create nested execution scopes
using (SqlConnecton con = Connect())
using (SqlTransaction tran = con.BeginTransaction())
{
//SQL batch with two seperate statements and two parameter markers
//one of the parameters is an output parameter
SqlCommand insertParent = new SqlCommand(
@"insert into TESTPARENT (customer) values (@customer);
set @ID = SCOPE_IDENTITY()", con, tran);

//bind the parameters and get references to the parameter objects
SqlParameter pCustomer =
insertParent.Parameters.Add("@customer", SqlDbType.VarChar, 50);
SqlParameter pParentID =
insertParent.Parameters.Add("@ID", SqlDbType.Int);
pParentID.Direction = ParameterDirection.Output;

//set up the second SqlCommand with parameters
SqlCommand insertChild = new SqlCommand(
"insert into TESTCHILD (counter, qty) values (@parent, @qty)",
con, tran);

SqlParameter pChildParent =
insertChild.Parameters.Add("@parent", SqlDbType.Int);
SqlParameter pChildQty =
insertChild.Parameters.Add("@qty", SqlDbType.BigInt);

//assign the input parameters for the command
pCustomer.Value = Customer;

//execute the command
insertParent.ExecuteNonQuery();

//after command execution the output parameters will be populated
int parentID = (int)pParentID.Value;

//use the value from the output parameter to populate the input
//parameters for the child query
pChildParent.Value = parentID;
pChildQty.Value = Quantity;
insertChild.ExecuteNonQuery();

//commit the transaction. If an exception is thrown the using
blocks
//will roll back the transaction
tran.Commit();
}
}

David

The