newbie transaction question

[ChrisB]

Hello All:

I am attempting to execute two statements as part of a single transaction
using the Microsoft data access application block. The code, which is
similar to what follows, returns this message when it attempts to execute
the second "SqlHelper.ExecuteReader" statement:
"The SqlCommand is currently busy Open, Fetching"

I would greatly appreciate any insight!

Thanks,
Chris

----------------------------------------------------------------------------
-
SqlConnection sqlConnection = new SqlConnection(DB("SPMS"));
sqlConnection.Open();

SqlTransaction sqlTransaction =
sqlConnection.BeginTransaction(IsolationLevel.Serializable);

try
{
// Set parameters

// Insert consumer
SqlDataReader sqlDataReader =
SqlHelper.ExecuteReader(sqlTransaction, CommandType.StoredProcedure,
"ConsumerAdd", consumerParameters));

safeDataReader.Close();

// Insert consumer history
SqlDataReader sqlDataReaderHistory =

SqlHelper.ExecuteReader(sqlTransaction,CommandType.StoredProcedure,"Consumer
HistoryAdd",consumerParameters));

sqlTransaction.Commit();
}
catch(Exception Exception)
{
sqlTransaction.Rollback();
throw Exception;
}
finally
{
sqlConnection.Close();
}

 

[Cowboy \(Gregory A. Beamer\)]

I do not see where you are setting up the Parameters, but I can pretty much
guess you are going about this incorrectly. The Parameters argument is an
params argument, meaning you would add like so:

SqlHelper.ExecuteReader(sqlTransaction, CommandType.StoredProcedure,
"ConsumerAdd", consumerParameter1, consumerParameter2));

Architecturally, I would run the transaction a bit differently. First, since
you are using the same database, I would simply have the transaction in the
stored procedure and run the two commands from there. There is no reason to
involve .NET in this transaction when the entire transaction can be handled
by the database server (T-SQL has transaction ability).

The main reason to move a transaction to .NET is when you have other actions
that are not easily handled by SQL Server.

Here is the reasoning:

..NET version
1. Connect to SQL Server
2. Run reader 1 - chat with server
3. Run reader 2 - chat with server
4. If error, re-contact the server to rollback the two commands


SQL version
1. Contact SQL Server
2. Run command (retrieving two reader "recordsets" from two SELECT
commands) - chat with server
3. If fail, rollback

I also have to question why you are creating a reader that is simply killed
off. You should fire off a ExecuteNonQuery if you are doing nothing with
return value(s). Neither of your readers are used for anything but running
the command.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[ChrisB]

Gregory:

Thanks very much for all of your input. The error message I was receiving
has been resolved (comments below).

I do not see where you are setting up the Parameters, but I can pretty much
guess you are going about this incorrectly. The Parameters argument is an
params argument, meaning you would add like so:

This is how the parameters were being set up (not an issue):

SqlParameter[] consumerParameters = new SqlParameter[26];
consumerParameters[0] = new SqlParameter("@ConsumerID", _consumerID);

consumerParameters[1] = new SqlParameter("@FirstName", _firstName);

etc.

Architecturally, I would run the transaction a bit differently. First, since
you are using the same database, I would simply have the transaction in the
stored procedure and run the two commands from there. There is no reason to
involve .NET in this transaction when the entire transaction can be handled
by the database server (T-SQL has transaction ability).

The main reason to move a transaction to .NET is when you have other actions
that are not easily handled by SQL Server.

Here is the reasoning:

.NET version
1. Connect to SQL Server
2. Run reader 1 - chat with server
3. Run reader 2 - chat with server
4. If error, re-contact the server to rollback the two commands


SQL version
1. Contact SQL Server
2. Run command (retrieving two reader "recordsets" from two SELECT
commands) - chat with server
3. If fail, rollback

I understand the point you are making. Actually, there are other objects
involved in the transaction that are being passed the sqlTransaction object,
but I left this code out of the example for the sake of simplicity.

I also have to question why you are creating a reader that is simply killed
off. You should fire off a ExecuteNonQuery if you are doing nothing with
return value(s). Neither of your readers are used for anything but running
the command.

Good point! Converting the "ExecuteReader" statements to "ExecuteNonQuery"
statements eliminated the "SqlCommand is Busy, Fetching" issue, although I
am still not quite sure why the error message was being raised.

Thanks again!

Chris

 

[Guest]

I'm having a similar problem and haven't found a viable workaround. I'm using one function that sets up and calls SqlHelper.ExecuteReader with a transaction object. I need the reader in this case because the proc I call returns an IDENTITY that I use for the remaining calls. This call works fine and returns me what I need. For the remaining calls, I use SqlHelper.ExecuteNonQuery with the same transaction object. The first one of these is where I'm getting the cryptic exception message. The ExecuteNonQuery instantiates a new local instance of the command object (variable is called cmd).

I could drop back and punt and build a wrapper stored proc to do all the work but I'd like to see what's going on here first. Any help is appreciated.