Execute requires the command to have a transaction object when the connection as

[skumar]

This is the sequence of events :

foo() {
SqlConnection conn = conn.Open();
SqlTransaction tx = conn.BeginTransaction();
... // do something
AnotherModule.bar(conn);
}

AnotherModule.bar(SqlConnection conn) {
SqlCommand cmd = conn.CreateCommand();
.. // calls a stored procedure
..
conn.ExecuteNonQuery(); // <<== Exception here
}

The exception is :-
"Execute requires the command to have a transaction object
when the connection assigned to the command is in a
pending local transaction. The Transaction property of
the command has not been initialized."

Why is it required for the bar() method to know the
transaction. It just wants to re-use the same connection
object. In fact the transaction is handled inside the
stored procedure that it is calling.

The api expects that, in the SqlCommand that is created in
bar() method also set the same transaction that the
connection is using. Why is this required?

I do not want to tie the transaction created in foo() to
the SqlCommand created in bar(). Is not a realistic
situation? What if Im calling a third party stored
procedure?

Looks to me that the the only way it could be solved is by
opening a new connection in bar(), instead of getting the
connection object as a parameter from the caller.

Anybody faced this problem?
- shiv

 

[Joe Fallon]

If you declare a Transaction (which you did) then you have to use it
everywhere.

Just don't declare one.

 

[George Mihailidis]

Joe, what do you mean when you say use it everywhere?

 

[Joe Fallon]

I mean everywhere within the code block:
If you have procedure and a command object and want to run 5 SQL commands on
it but you start a transaction then all 5 commands must use that same
transaction.

A different procedure with its own command object doesn't need to decalre or
use a transaction.