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
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