如何使用 Transaction 来使 SQL SERVER 进行 COMMIT 或 ROLLBACK

[walter_lee68]

我要执行以下2个 SQL 语句,成功后才 COMMIT
1.UPDATE TABLE1 SET FIELD1="AAA" WHERE ...
2.UPDATE TABLE2 SET FIELD1="AAA" WHERE ...


在 C# 中的写法是怎样的:

Conn1.Open();
SqlCommand CM1=new SqlCommand("Begin Transcation", Conn1);
:
:
CM1.ExecuteScalar();
Conn1.Close();

请帮忙指出

[charles_y]

http://blog.csdn.net/charles_y/archive/2005/12/13/550833.aspx

[nate_liu]

SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();

// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();

// Enlist the command in the current transaction.
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

Console.WriteLine("An exception of type " + e.GetType() +
"was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}

請參閱

[seaonce]

using (SqlConnection Conn = new SqlConnection(数据库连接字符串))
{
int upd1 = 0;
int upd2 = 0;
Conn.Open();
SqlTransaction trans = Conn.BeginTransaction();
using (SqlCommand Cmd = new SqlCommand())
{
Cmd.CommandText = "update....";
upd1 = Cmd.ExecuteNonQuery();
Cmd.CommandText = "update ....";
upd2 = Cmd.ExecuteNonQuery();
Cmd.Dispose();
}
if( upd1==1 && upd2==1)
{
trans.Commit();
}
else
{
trans.Rollback();
}

Conn.Dispose();
Conn.Close();
}

[alexlimingjun]

public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open();

SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;

// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback("SampleTransaction");
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}
MSND里的

[diablo1003]

mark