SQL事务的写法例子
BEGIN TRANSACTION DeleteSupv
-- SQL语句
IF @@error <> 0
BEGIN
ROLLBACK TRAN DeleteSupv
GOTO on_error
END
-- SQL语句
IF @@error <> 0
BEGIN
ROLLBACK TRAN DeleteSupv
GOTO on_error
END
COMMIT TRAN DeleteSupv
RETURN 1
on_error:
print ' Error occured '
BEGIN TRANSACTION DeleteSupv
-- SQL语句
IF @@error <> 0
BEGIN
ROLLBACK TRAN DeleteSupv
GOTO on_error
END
-- SQL语句
IF @@error <> 0
BEGIN
ROLLBACK TRAN DeleteSupv
GOTO on_error
END
COMMIT TRAN DeleteSupv
RETURN 1
on_error:
print ' Error occured '
C# 执行SQL事务实例代码
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();
// 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();
}
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();
}
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();
// 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();
}
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();
}