Using transaction with multiple commands

T

tshad

How do I use a "transaction" with multiple SqlCommands and one Sql
Connection?

For example if I have the following where I have 1 connection and then loop
through multiple SqlCommands (multiple inserts to a variety of tables) where
I want it to roll back if any of them fail:

using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
foreach(...)
{
using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
...
}
}
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

return errorMessage;

I would change this to something like:

SqlTransaction transaction = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
transaction = conn.BeginTransaction();

foreach(...)
{
using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
...
}
}
transaction.Commit();
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

return errorMessage;

But how do I deal with the "cmd.Transaction = transaction" statement?

If I leave it as is, I will get an error.

Do just keep assigning the same transaction to each "cmd" - which I assume
would be right after the "using (SqlCommand ..." statement?

Thanks,

Tom
 
F

Fredo

Tom,

The answer is yes, you just keep using the same transaction object with each
command object. You're basically associating each command with the single
transaction object. If you commit the transaction, all the commands get
committed. If you rollback the transaction, it will rollback all the
commands you executed.
 
T

tshad

Fredo said:
Tom,

The answer is yes, you just keep using the same transaction object with
each command object. You're basically associating each command with the
single transaction object. If you commit the transaction, all the commands
get committed. If you rollback the transaction, it will rollback all the
commands you executed.
Ok,

Thanks,

Tom
 
M

Marc Gravell

Note that an alternative (especially useful if your commands are in
separate methods etc) is TransactionScope:

using (TransactionScope tran = new TransactionScope())
{
BlockOfCodeA();
BlockOfCodeB();
BlockOfCodeC();
tran.Complete();
}

Here the transaction is "ambient"; it also allows for DTC transactions
for multi-database queries (using the LTM for single-database queries
on SQL Server 2005 and above).

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Marc
 
T

tshad

Also, as an aside,

at the moment I am doing:


dbReader = cmd.ExecuteReader();
while (dbReader.Read())
{
...
}
dbReader.Close();

Can I do:

Using(dbReader = cmd.ExecuteReader())
{
while (dbReader.Read())
{
...
}
}

Will the dbReader be closed here at the closing "using" brace?

I was wonding because of my previous code for multiple command objects using
the same connection. I wasn't closing the dbReader because I assumed that
by creating a new Command object it would also have a different dbReader
associated with it.

But it was giving me an error:

"There is already an open DataReader associated with this Command which
must be closed first."

The code is something like (as before):

using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
foreach(...)
{
cmd = new SqlCommand("proc" + ptn.TableName + "_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
...
}
}
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

I took the Using for the "new SqlCommand" just for the question.

In this scenario, I am using the cmd pointer but I am creating a new
SqlCommand object (so I assume a new Reader object). The error occurs on
the:

dbReader = cmd.ExecuteReader();

How is there a Data Reader associated with this command if I now have a new
command?

Also, I can fix this by changing the code to:

using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
foreach(...)
{
cmd = new SqlCommand("proc" + ptn.TableName + "_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
...
}
dbReader.Close();
}
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

Or I could change to the above (if the dbReader is closed by the Using
clause):

using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
foreach(...)
{
using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
Using (dbReader = cmd.ExecuteReader())
{
while (dbReader.Read())
{
...
}
}
}
}
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

Would this work and is it overkill?

Thanks,

Tom
 
F

Fredo

Yes, you can do what you're asking and no, it's not overkill. There is no
"overkill" when it comes to the "using" statement. It saves you from writing
extra code and it saves you from having to manage disposal and cleanup, so
if anything, not using it often enough would be overkill.

"using" will cleanup by calling Dispose() on the object. Dispose() will
close streams, DB connections, DB readers, etc...

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top