通过事务管理多个Insert \ Update \ Delete SqlCommand [英] Managing multiple Insert\Update\Delete SqlCommand with a Transaction

问题描述

大家好，

我试图搜索有关我的问题的其他帖子，但找不到我的问题的答案.

我的应用程式.使用SqlConnnection连接到DS.
我正在使用SqlDataAdapter Insert \ Update \ Delete命令在其中的某些表上进行操作.
我需要确保一次交易"中的操作安全，但是这些操作会在不同的方法中触发.

方法1触发(///一些代码-> SqlCommand 1)

方法2触发(//一些代码-> SqlCommand 2)

方法3触发(//一些代码-> SqlCommand 3)

方法4触发(//一些代码-> SqlCommand 4)

....
由于用户的操作，SqlCommand 2、3、4被独立触发. 每个命令可以是唯一的.

我需要启用显式交易模式"(?)，在该交易中，必须先执行sql命令，然后事务才自行提交交易.

我稍微了解了Trasactions，这是我的问题.默认情况下，事务设置为自动提交，但是如果我想在一个事务中执行多个命令，那么我需要具有Transaction Scope?(但我需要插入 所有命令都在其主体右侧吗?)是否有任何模式设置可将自动提交选项更改为手动?
我了解这种方法可能不是完美的方法(我正在等待用户插入参数等值的操作).

  < div class ="h2_lin">解决方案

SqlTransaction transaction = null;

使用(SqlConnection conn = new SqlConnection(connectionString)) {
try
{
conn.Open();
transaction = conn.BeginTransaction();
foreach(...)
{
使用(SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_ insert"，conn)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = transaction
dbReader = cmd.ExecuteReader();
...
}
}
transaction.Commit();
}
catch(异常排除)
{
errorMessage = exc.Message;
}
}
返回errorMessage;

一种替代方法(特别是在您的命令位于的情况下有用
其他方法等)是TransactionScope:
使用(TransactionScope tran = new TransactionScope())
{
BlockOfCodeA();
BlockOfCodeB();
BlockOfCodeC();
tran.Complete();
}
此处的交易环境"；它还允许DTC交易
用于多数据库查询(对于单个数据库查询使用LTM (在SQL Server 2005及更高版本上).

Hello everybody, 

I've tried to search for some other post concerning my problem, but I couldn't find answers to my problem.

My app. is connected to a DS using SqlConnnection. 
I'm using SqlDataAdapter Insert\Update\Delete Commands to operate on certain tables in it. 
I need to secure the operations in ONE TRANSACTION, but the operations fire in different methods.

Methods 1 fires(//some code->SqlCommand 1)

Method 2 fires(//some code->SqlCommand 2)

Method 3 fires(// some code ->SqlCommand 3)

Method 4 fires(// some code - >SqlCommand 4)

....
SqlCommand 2,3,4 are fired independently, as a result of user actions. 
Each command can be unique. 

I need a "Explicit Transaction mode"(?) on, where the transaction doesn't commits it self after a sql command has been executed positively. 

I read about Trasactions a bit, and this is my question. By default the transaction is set to auto commit, but if I would like to some think like having more then one command executed in one transaction I need to have Transaction Scope ?(but I need to insert all Commands in side of it's body right ?) Is there any mode settings to change the auto commit option to manual? 

I understand that this approach maybe not the perfect one(I'm waiting for the user actions-inserting parameter values, etc. ). 

   

解决方案

Hope below code guides you in some way.

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;
cmd.Transaction = transaction
dbReader = cmd.ExecuteReader();
...
}
}
transaction.Commit();
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

return errorMessage;

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/lib...tionscope.aspx

这篇关于通过事务管理多个Insert \ Update \ Delete SqlCommand的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！