SqlCommand.Dispose（）SqlTransaction.Commit（）之前？ [英] SqlCommand.Dispose() before SqlTransaction.Commit()?
问题描述
将它的工作处置分配给交易指令的事务提交之前？我测试了以下code自己，它似乎已经工作得很好，但是这是一个相当小的例子，所以我在寻找确认是否有人正知道。
内部静态无效TestTransaction（）
{
尝试
{
Program.dbConnection.Open（）;
使用（的SqlTransaction事务= Program.dbConnection.BeginTransaction（））
{
布尔doRollback = FALSE;
的for（int i = 0;我小于10;我++）
{
使用（CMD的SqlCommand =新的SqlCommand（INSERT INTO [TESTDB]。[DBO]。[transactiontest]（testvalcol）VALUES（@index），Program.dbConnection，事务））
{
cmd.Parameters.AddWithValue（@指数，我）;
尝试
{
cmd.ExecuteNonQuery（）;
}
赶上（SQLEXCEPTION）
{
doRollback = TRUE;
打破;
}
}
}
如果（doRollback）
transaction.Rollback（）;
其他
器transaction.commit（）;
}
}
最后
{
Program.dbConnection.Close（）;
}
}
的连接，事务和命令的对象只是车辆将命令发送到数据库。一旦命令被执行数据库已收到。不管你做的命令对象之后，处理，焚烧，或将其拍摄到月球，这个事实不会改变。 （它只能被回滚）。
您可以创建并为你喜欢的一个的SqlConnection （带或不带的SqlTransaction ）。你就可以开始，当你想在一个的SqlConnection 处理尽可能多的交易。为了证明这一点，请参见：
使用（VAR康恩=新的SqlConnection（@服务器=（本地）\ SQL2008;数据库=垃圾;集成安全性= SSPI））
{
conn.Open（）;
//你喜欢经常重复此块...
使用（VAR TRAN = conn.BeginTransaction（））
{
使用（VAR CMD =新的SqlCommand（INSERT INTO混乱VALUES（'mess1'），康涅狄格州，TRAN））
{
cmd.ExecuteNonQuery（）; //显示在SQL事件探查器
}
tran.Commit（）; //承诺
}
使用（VAR CMD =新的SqlCommand（INSERT INTO混乱VALUES（'mess2'），康涅狄格州））
{
cmd.ExecuteNonQuery（）; //执行并提交（隐式事务）。
}
}
当然，对于健康的code，你需要按照正确的顺序处理所有的对象。处理后废弃的命令的的SqlConnection 可能会导致连接对象留在内存中。
would it work to dispose a command assigned to a transaction before the transaction is committed? I tested the following code myself, and it seems to have worked fine, but this is a rather small example, so I'm looking for confirmation if someone positively knows.
internal static void TestTransaction()
{
try
{
Program.dbConnection.Open();
using (SqlTransaction transaction = Program.dbConnection.BeginTransaction())
{
Boolean doRollback = false;
for (int i = 0; i < 10; i++)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [testdb].[dbo].[transactiontest] (testvalcol) VALUES (@index)", Program.dbConnection, transaction))
{
cmd.Parameters.AddWithValue("@index", i);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException)
{
doRollback = true;
break;
}
}
}
if (doRollback)
transaction.Rollback();
else
transaction.Commit();
}
}
finally
{
Program.dbConnection.Close();
}
}
The connection, transaction and command objects are just vehicles to send commands to a database. Once a command is executed the database has received it. Whatever you do with the command object afterwards, dispose it, burn it, or shoot it to the moon, this fact does not change. (It can only be rolled back).
You can create and dispose as many commands as you like within the scope of one SqlConnection (with or without SqlTransaction). And you can start and dispose as many transactions as you like within one SqlConnection. To demonstrate this, see:
using (var conn = new SqlConnection(@"server=(local)\sql2008;database=Junk;Integrated Security=SSPI"))
{
conn.Open();
// Repeat this block as often as you like...
using (var tran = conn.BeginTransaction())
{
using (var cmd = new SqlCommand("INSERT INTO Mess VALUES ('mess1')", conn, tran))
{
cmd.ExecuteNonQuery(); // Shows in Sql profiler
}
tran.Commit(); // Commits
}
using (var cmd = new SqlCommand("INSERT INTO Mess VALUES ('mess2')", conn))
{
cmd.ExecuteNonQuery(); // Executes and commits (implicit transaction).
}
}
Of course, for healthy code you need to dispose of all objects in the correct order. Disposing a command after disposing a SqlConnection may cause the connection object to stay in memory.
这篇关于SqlCommand.Dispose（）SqlTransaction.Commit（）之前？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
