SQL Server 事务处理回滚事务（sqlserver事务回滚语法）

25-02-05 16

对于想了解SQLServer事务处理回滚事务的读者，本文将提供新的信息，我们将详细介绍sqlserver事务回滚语法，并且为您提供关于.Net和SqlServer的事务处理实例、c#实现sqlserv

对于想了解SQL Server 事务处理回滚事务的读者，本文将提供新的信息，我们将详细介绍sqlserver事务回滚语法，并且为您提供关于.Net和SqlServer的事务处理实例、c#实现sqlserver事务处理示例、Jfinal service层事务处理不回滚、PDO 的事务处理事务回滚的有价值信息。

本文目录一览：

SQL Server 事务处理回滚事务（sqlserver事务回滚语法）
.Net和SqlServer的事务处理实例
c#实现sqlserver事务处理示例
Jfinal service层事务处理不回滚
PDO 的事务处理事务回滚

SQL Server 事务处理回滚事务（sqlserver事务回滚语法）

创建表：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t1](
    [Id] [int] NOT NULL,
    [c1] [nvarchar](50) NULL,
    [c2] [datetime] NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_norECOmpuTE  = OFF, IGnorE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

解决方案（一）

declare   @iErrorCount   int
set @iErrorCount = 0
begin tran Tran_2008_10_07

insert into t1(Id, c1) values(1,'1')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values(2,'2')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values('xxxx3','3')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values(4,'4')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values(5,'5')
set @iErrorCount=@iErrorCount+@@error

if @iErrorCount=0
  begin
    COMMIT TRAN Tran_2008_10_07
  end
else
  begin
    ROLLBACK TRAN Tran_2008_10_07
  end

解决方案（二）

begin try
    begin tran Tran_2008_10_07

        insert into t1(Id, c1) values(1,'1')

        insert into t1(Id, c1) values(2,'2')

        insert into t1(Id, c1) values('xxxx3','3')

        insert into t1(Id, c1) values(4,'4')

        insert into t1(Id, c1) values(5,'5')

    COMMIT TRAN Tran_2008_10_07
end try
begin catch
    raiserror 50005N'出错了'
    ROLLBACK TRAN Tran_2008_10_07
end catch

.Net和SqlServer的事务处理实例

1，sqlServer存储过程的事务处理
一种比较通用的出错处理的模式大概如下：
Create procdure prInsertProducts
(
@intProductId int,
@chvProductName varchar(30),
@intProductCount int
)
AS
Declare @intErrorCode int
Select @intErrorCode=@@Error
Begin transaction
if @intErrorCode=0
   begin
     -insert products
     insert products(ProductID,ProductName,ProductCount)
     values(@intProductId,@chvProductName,@intProductCount)
     Select @intErrorCode=@@Error --每执行完一条t-sql语句马上进行检测，并把错误号保存到局部变量中
   end
if @intErrorCode=0
   begin
     -update products
     update products set ProductName=’microcomputer’ where ProductID=5
     Select @intErrorCode=@@Error
   end
if @intErrorCode=0
   commit transaction
else
   rollback transaction

Return @intErrorCode --最好返回错误代号给调用的存储过程或应用程序

2，.Net中使用事务处理
sqlConnection myConnection = new sqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=sspI;");
myConnection.open();

sqlTransaction myTrans = myConnection.BeginTransaction(); //使用New新生成一个事务
sqlCommand myCommand = new sqlCommand();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Update Address set location=’23 rain street’ where userid=’0001’";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Record is udated.");
}
catch(Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Sorry,Record can not be updated.");
}
finally
{
myConnection.Close();
}

说明：在sqlServer中，每条sql语句都作为一个事务来执行，所以无论在存储过程，还是在.net代码中使用，执行单条sql语句没有必要使用事务处理，上面只是为了简化表达而对单条sql语句使用事务处理

c#实现sqlserver事务处理示例

复制代码代码如下:

private static void ExecutesqlTransaction(string connectionString)
    {
        using (sqlConnection connection = new sqlConnection(connectionString))
        {
            connection.open();
            sqlCommand command = connection.CreateCommand();
            sqlTransaction transaction;
            // Start a local transaction.
            transaction = connection.BeginTransaction("SampleTransaction");
            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection = connection;
            command.Transaction = transaction;
            try
            {
                command.CommandText = "Insert into Region (RegionID,RegionDescription) VALUES (100,'Description')";
                command.ExecuteNonQuery();
                command.CommandText = "Insert into Region (RegionID,RegionDescription) VALUES (101,'Description')";
                command.ExecuteNonQuery();
                // Attempt to commit the transaction.
                transaction.Commit();
                Console.WriteLine("Both records are written to database.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}",ex.GetType());
                Console.WriteLine(" Message: {0}",ex.Message);
                // Attempt to roll back the transaction.
                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred
                    // on the server that would cause the rollback to fail,such as
                    // a closed connection.
                    Console.WriteLine("Rollback Exception Type: {0}",ex2.GetType());
                    Console.WriteLine(" Message: {0}",ex2.Message);
                }
            }
        }
    }

Jfinal service层事务处理不回滚

@JFinal 你好，想跟你请教个问题：

你好我在service层用事务处理

不会滚想问下怎么回事

我的数据库是mysql的

配置和代码如上谢谢波总@JFinal

PDO 的事务处理事务回滚

<?php
header(''content-type:text/html;charset=utf-8'');
include ''PdoClass.php'';
$objPdo=new PdoClass();
//事务
try{
    $dsn = "mysql:host=localhost;dbname=xxx";
    $username = "root";
    $password = ''root'';
    $pdo = new PDO($dsn, $username, $password,array(PDO::ATTR_AUTOCOMMIT=>0,PDO::ATTR_DEFAULT_FETCH_MODE=> PDO::FETCH_ASSOC,PDO::ATTR_CASE=>PDO::CASE_LOWER)) ;
    $pdo->exec(''set names utf8'');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
    echo "connect fail：".$e->getMessage();
    exit;
}
try{
    $pdo->beginTransaction();
    $price = 500;
    $list=$objPdo->getAll(''user'');
    $goods = $objPdo->getAll(''goods'');
    //print_r($goods);die;
    $res=$list[0][''money''];
    $goods_count=$goods[0][''g_count''];
    if($res<500){
        throw new PDOException(''从A帐号中扣出失败'');
    }else if($goods_count==0){
        throw new PDOException(''从商品表中扣出失败'');
    }else {
        $sql = "update user set money=money-{$price} where u_id=1";
        $pdo->exec($sql);
        $sql1 = "update user set money=money+{$price} where u_id=2";
        $pdo->exec($sql1);
        $sql2 = "update goods set g_count=g_count-1 where g_id=1";
        $pdo->exec($sql2);
    }
    $pdo->commit();
} catch (PDOException $ex) {
    $pdo->rollBack();
    $title = isset($_POST[''title''])?$_POST[''title'']:'''';
    $list = $objPdo->getAll(''user'',$title);
    $goods = $objPdo->getAll(''goods'',$title);
    include ''form.html'';
    echo $ex->getMessage();
}

?>

今天的关于SQL Server 事务处理回滚事务和sqlserver事务回滚语法的分享已经结束，谢谢您的关注，如果想了解更多关于.Net和SqlServer的事务处理实例、c#实现sqlserver事务处理示例、Jfinal service层事务处理不回滚、PDO 的事务处理事务回滚的相关知识，请在本站进行查询。

本文标签：