Question about SqlTransaction

[Issac]

Hi,

I am writing a module which will update 2 tables
(Master/Detail) in SQL database. There's a foreign key
protection among those tables so that I have to add a
record in Master table prior to add record in Detail
table. What I want to do is I want to create a
SqlTansaction to rollback Master insertion whenever
there's error occur in Detail insertion.
Here is my code...

\\\
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim myTrans As SqlTransaction
Dim i As Integer
Dim sqlCnn As New SqlConnection(myConnString)

Try
sqlCnn.Open()
myTrans = sqlCnn.BeginTransaction
For i = 1 To aSqlCmd.GetUpperBound(0)
aSqlCmd(i).Connection = sqlCnn
aSqlCmd(i).Transaction = myTrans
da = New SqlDataAdapter(aSqlCmd(i))
ds = New DataSet
da.Fill(ds)
Next
myTrans.Commit()
Catch sqlEx As SqlException
MsgBox(sqlEx.Message)
myTrans.Rollback()
Catch ex As Exception
MsgBox(ex.Message)
myTrans.Rollback()
Finally
sqlCnn.Close()
End Try
///

What I did is I take an array of SqlCommand(), execute one
by one, then rollback all if there's any error.
However, when it goes to second line, I got a sql error
saying there's a foreign key violation.
I did try to make such insertions in Query Analyzer and
wrap all with Begin Trans, both Commit and Rollback works
fine.
Can I do such batch update with .Net or I did something
wrong?

Please help, thanks.

 

[Chandra Sekhar]

Just from my glimpse at your code, I dont think you need to specify
aSqlCmd(i).Transaction=myTrans. It should work if you take that off.

Sekhar.

 

[Miha Markic [MVP C#]]

Hi Isaac,

You are not doing any insert. Rather, you are fetching data from database
and for that, normally, you don't need a transaction.
Your problem is, that you are not filling tables in correct order.
Either invoke Fill methods in correct order (from master to detail) or embed
the loop into
ds.EnforceConstraints = false;
try
{
....
}
finally
{
ds.EnforceConstraints = true;
}

 

[Sijin Joseph]

This is strange, first of all since you are using an untyped dataset you
should not be getting any foreign key violations when you are filling data.
Are you using a typed dataset in your code. And how come you are not
specifying table names in your fill methods?

 

[Issac]

Thanks all for reply.
I figured out where the bug is while I was thinking how to
reply your post, thanks for stimulus

Actually, what I did is, in every aSqlCmd, it is
sqlcommand that will execute a SP. Such SP will first
create a key by calling other SP, then do insertion and
then return some informations from database to indicate
whether it successes or fails, and also a new key that has
been generated. The untype ds is used to catch such
informations from DB. You can treat it as every aSqlCmd
is a command to DS and such command may or may not return
value, that's why I used Fill rather than
ExecuteNonQuery. And I wrap all SqlCommand() with a
SqlTransaction. So the procedure is...

SqlTransaction.Begin
Execute SqlCommand(1) and get result in untype ds.
Execute SqlCommand(2) and get result in untype ds.
Execute SqlCommand(3) and get result in untype ds.
SqlTransaction.Commit

The SqlCommand(1) should get a key from ds, and such key
will be used in SqlCommand(2) as parameter.
And here is the problem, I pass SqlCommand array as
parameter, and within each SqlCommand I have their own
SqlParameter to SP. The key that needed in SqlCommand(2)
didn't get update at all so it always pass in "", that's
why I got foreign key error. I think I should find some
way to update the return key for SqlCommand(2).

Thanks for help all

-----Original Message-----
Hi Isaac,

You are not doing any insert. Rather, you are fetching data from database
and for that, normally, you don't need a transaction.
Your problem is, that you are not filling tables in correct order.
Either invoke Fill methods in correct order (from master to detail) or embed
the loop into
ds.EnforceConstraints = false;
try
{
....
}
finally
{
ds.EnforceConstraints = true;
}

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi,

I am writing a module which will update 2 tables
(Master/Detail) in SQL database. There's a foreign key
protection among those tables so that I have to add a
record in Master table prior to add record in Detail
table. What I want to do is I want to create a
SqlTansaction to rollback Master insertion whenever
there's error occur in Detail insertion.
Here is my code...

\\\
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim myTrans As SqlTransaction
Dim i As Integer
Dim sqlCnn As New SqlConnection(myConnString)

Try
sqlCnn.Open()
myTrans = sqlCnn.BeginTransaction
For i = 1 To aSqlCmd.GetUpperBound(0)
aSqlCmd(i).Connection = sqlCnn
aSqlCmd(i).Transaction = myTrans
da = New SqlDataAdapter(aSqlCmd(i))
ds = New DataSet
da.Fill(ds)
Next
myTrans.Commit()
Catch sqlEx As SqlException
MsgBox(sqlEx.Message)
myTrans.Rollback()
Catch ex As Exception
MsgBox(ex.Message)
myTrans.Rollback()
Finally
sqlCnn.Close()
End Try
///

What I did is I take an array of SqlCommand(), execute one
by one, then rollback all if there's any error.
However, when it goes to second line, I got a sql error
saying there's a foreign key violation.
I did try to make such insertions in Query Analyzer and
wrap all with Begin Trans, both Commit and Rollback works
fine.
Can I do such batch update with .Net or I did something
wrong?

Please help, thanks.

.