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");
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 ).
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.
*** Sent via Developersdex http://www.examnotes.net ***The DBA side of me is cringing, becuae you're not using stored
procedures to insert data into a database; you really should use a
stored procedure when running against SQL Server. I realize that there
may be reasons to not use a stored procedure, but thos should be the
exception, and not the rule. Oh, and if you're going to use IDENTITY
columns, you should probably be using SCOPE_IDENTITY, and not
@.@.IDENTITY to return the last value inserted.
The developer side of me (recognizing that this might be one of those
rare times when a stored procedure is not appropriate) is cringing
because you are not using parameters to issue commands to your
database, thereby opening yourself up to SQL injection. You are also
connecting to your database as "sa", which means that if someone
figures out your connection string, they own your server.
Try replacing the value of tbCustomer.Text with "1'); DROP TABLE
TestParent--" and see what happens.
You need to make the following changes:
1. Don't connect to a database using sa; create a user with the
appropriate permissions.
2. Use a stored procedure for all CRUD operations (if you don't know
what CRUD stands for, Google it; it'll help you understand a lot more
that this single post will explain).
3. If you can't use a stored procedure (vendor issue, server doesn't
support stored procedures, etc), then use parameters on your client
side; don't build strings.
HTH,
Stu
