WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

try - catch を書かないでロールバック

by WebSurfer 29. July 2020 13:45

ADO.NET + SqlClient を使ってトランザクション処理を行う場合、明示的に Rollback メソッドを書かなくてもロールバックできるという話を書きます。

ロールバックを行うコードで自分がよく目にするのは、try - catch 文を使って try 句の中に処理をまとめ、catch 句に Rollback メソッドを書いて、処理の途中で例外が発生したらロールバックできるようにするというものです。

具体例は Microsoft のドキュメント「SqlTransaction.Rollback メソッド」にありますので見てください。

そのドキュメントにある catch 句で Rollback するというコードは、一見して例外発生でロールバックされるということが分かって良いと思うのですが、Exception を catch してそのままにしてしまっている所が気になります。

そこは、catch するのを SqlException のみにするとか Rollback した後で再 throw することで対応できるのですが、意地でも try - catch 文は書きたくないという場合もあるかもしれません。ないかもしれませんが。(笑)

Microsoft の Blog に書いてあったことですが(今はリンク切れで読めません)、未コミットのトランザクションは SqlConnection を Dispose(Close と同じ)する際ロールバックされるそうです。

ということは、catch 句に Rollback メソッドを書くというようなことはしなくても、try - finally または using 句を使って確実に SqlConnection が Dispose / Close されるようにしておけば例外発生でロールバックされるはずです。

実際「Microsoft Visual Studio 2005 による Web アプリケーション構築技法」という本にも Rollback メソッドを使わないコード例は紹介されていました。

具体例を以下の画像の SQL Server のテーブルと ADO.NET + SqlClient のコードを使って説明します。

SQL Server のテーブル

テーブルは「エラー発生時のトランザクションのロールバック - SET XACT_ABORT ON と TRY...CATCH」という記事から借用しました。フィールド StudentID には FK 制約が設定してあり 1 ~ 6 または NULL 以外を入力しようとするとエラーになります。

コード例は下の方にアップします。using 句を使って確実に SqlConnection が Dispose されるようにしています。Rollback メソッドは書いていません。例外が発生するとアプリは終了しますが、その前に using 句により SqlConnection が Dispose され、Commit メソッドは実行されませんのでロールバックされるはずです。

コード例では、上に紹介した記事にならって、3 つの UPDATE 文を実行しようとしています。最後の UPDATE 文で StudentID を 7 に設定し(1 ~ 6 または NULL 以外は FK 制約違反)そこで FK 制約違反となるようにして試してみました。エラーメッセージは以下の通りです。

ハンドルされていない例外: System.Data.SqlClient.SqlException: UPDATE ステートメントは FOREIGN KEY 制約 "FK__TestResul__Stude__22AA2996" と競合しています。競合が発生したのは、データベース "TestDatabase"、テーブル "dbo.Student", column 'StudentID' です。

static void Main(string[] args)
{
    string connString = @"接続文字列";
    string query = "UPDATE [TestDatabase].[dbo].[TestResult] " +
                   "SET [StudentID]=@StudentID " +
                   "WHERE [TestResultID]=@TestResultID";

    using (var connection = new SqlConnection(connString))
    {
        using (var command = new SqlCommand(query, connection))
        {
            command.Parameters.Add(
                new SqlParameter("@StudentID", SqlDbType.Int));
            command.Parameters.Add(
                new SqlParameter("@TestResultID", SqlDbType.Int));
            connection.Open();
            var sqltx = connection.BeginTransaction();
            command.Transaction = sqltx;

            command.Parameters["@StudentID"].Value = 5;
            command.Parameters["@TestResultID"].Value = 1;
            command.ExecuteNonQuery();

            command.Parameters["@StudentID"].Value = 6;
            command.Parameters["@TestResultID"].Value = 2;
            command.ExecuteNonQuery();

            // FK 制約違反
            command.Parameters["@StudentID"].Value = 7;
            command.Parameters["@TestResultID"].Value = 3;
            command.ExecuteNonQuery();

            sqltx.Commit();
        }
    }
}

結果、期待通りロールバックされて TestResult テーブルには 3 つの UPDATE 文のいずれも反映されないことを確認しました。

上に紹介した記事の Transact-SQL を使った場合の例では、SET XACT_ABORT ON を設定しない場合、TRY...CATCH を使って CATCH で明示的に ROLLBACK する必要があるそうです。しかし、ADO.NET + SqlClient では上のコード例のように SqlConnection を Dispose / Close すればロールバックされるようです。

Tags: , , ,

ADO.NET

TableAdapter と Transaction

by WebSurfer 18. December 2011 20:33

Visual Studio を利用してウィザードベースで自動生成する型付 DataSet + TableAdapter を使ってデータベースを更新する際、手動トランザクション処理するにはどうしたらいいでしょうか?(MS-DTC を使う自動トランザクションではなくて)

Visual Studio 2008 から TableAdapter に Transaction プロパティが追加されたので、それと、それ以前からある Connection プロパティを使えば、Visual Studio 2005 以前と比較して手動トランザクションが容易に設定できます。

でも、Visual Studio 2008 以降を使っているなら、そもそも手動トランザクションをかけるコードを自力で書く必要はなくて、自動生成される TableAdapterManager クラスの UpdateAll メソッドを使用すればいいです。

実は知らなかったのですが、TableAdapterManager.UpdateAll メソッドのコードをよく見てみると、そのメソッドの中でトランザクションがかかるようになってました。

もともと、TableAdapterManagerは 階層更新 を実現する ため(即ち、複数のテーブルを、データベース内の制約によって定義される一貫性規則を守って関連レコードの挿入、更新、および削除を制御するため)のものということらしいですが、単一テーブルの場合もトランザクションは有効に働きます。

という訳で、Visual Studio 2008 以降なら TableAdapterManager の UpdateAll メソッドを使うのが正解だと思います。

ちなみに、Visual Studio 2005 以前の場合は Transaction プロパティは自動生成されたコードには定義されません。TableAdapter は partial クラスとして定義されているので、自動生成されたファイルとは別に、Visual Studio 2008 以降で生成されるコードを参考に、自力で Transaction プロパティを定義して使うのがいいと思います。SqlClient の場合は以下のような感じです。

private System.Data.SqlClient.SqlTransaction _transaction;

internal System.Data.SqlClient.SqlTransaction Transaction {
  get {
    return this._transaction;
  }
  set {
    this._transaction = value;
    for (int i = 0; i < this.CommandCollection.Length; i++) {
      this.CommandCollection[i].Transaction = this._transaction;
    }
    if (((this.Adapter != null) 
        && (this.Adapter.DeleteCommand != null))) {
      this.Adapter.DeleteCommand.Transaction = this._transaction;
    }
    if (((this.Adapter != null) 
        && (this.Adapter.InsertCommand != null))) {
      this.Adapter.InsertCommand.Transaction = this._transaction;
    }
    if (((this.Adapter != null) 
        && (this.Adapter.UpdateCommand != null))) {
      this.Adapter.UpdateCommand.Transaction = this._transaction;
    }
  }
}

どうも、Visual Studio 2008 から TableAdapter に Transaction プロパティが追加された理由は、TableAdapterManager.UpdateAll メソッドで手動トランザクションをかけるためのようです。

Tags: ,

ADO.NET

SqlDataSource とトランザクション

by WebSurfer 16. November 2010 22:52

SqlDataSource + DetailsView による更新が成功したら、変更されたフィールド名、更新前/後の値、変更日時をログとして SQL Server データベースに残す処置をトランザクションを切って行う方法です。

更新前/後の値の取得と、SqlDataSorce コントロールによる UPDATE 操作と ADO.NET のコードによる INSERT 操作のトランザクションをどのように設定するかがポイントだと思います。

まず、更新前/後の値ですが、これらは DetailsView.ItemUpdating イベントのハンドラにおいて、引数の DetailsViewUpdateEventArgs オブジェクトから Keys, OldValues, NewValues プロパティによって取得できます。

トランザクションの開始および SqlTranscation オブジェクトの取得、SqlCommand.Transaction プロパティのへの SqlTranscation オブジェクトの設定は、SqlDataSource.Updating イベントで行います。イベントハンドラの引数 SqlDataSourceCommandEventArgs から SqlConnection, SqlCommand オブジェクトへの参照を取得できますので、それらを用いて設定します。

ログを残すための INSERT 操作は、SqlDataSource.Updated イベントのハンドラで行います。イベントハンドラの引数 SqlDataSourceStatusEventArgs オブジェクトの Exception, AffectedRows プロパティを用いて、UPDATE 操作で例外が発生しなかったことと更新が行われたことが判定できます。

例外の発生がなく更新が行われたと判定されたら、ログを残すための操作(データベースへの INSERT 操作)を行います。INSERT 操作は、更新されたフィールドが複数の場合、同じ数だけ行うことになります。 INSERT 操作の途中で失敗した場合は、SqlDataSource による UPDATE 操作まで巻き戻すことになります。

そのために、引数 SqlDataSourceStatusEventArgs オブジェクトから、SqlDataSource が UPDATE 操作に用いた SqlConnection, SqlCommand, SqlTransaction オブジェクトへの参照を取得し、それらを用いて、SqlDataSource が行う UPDATE 操作と同じコネクション/トランザクションで INSERT 操作を行うように設定します。

サンプルコードは以下のとおりです。

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

  IOrderedDictionary keys;
  IOrderedDictionary oldValues;
  IOrderedDictionary newValues;
    
  protected void SqlDataSource2_Updating(object sender, 
    SqlDataSourceCommandEventArgs e)
  {
    SqlCommand command = (SqlCommand)e.Command;
    SqlConnection connection = command.Connection;
    connection.Open();
    SqlTransaction tx = connection.BeginTransaction();
    command.Transaction = tx;
  }
    
  protected void SqlDataSource2_Updated(object sender, 
    SqlDataSourceStatusEventArgs e)
  {
    SqlCommand command = (SqlCommand)e.Command;
    SqlConnection connection = command.Connection;
    SqlTransaction tx = command.Transaction;
    if (e.Exception == null && e.AffectedRows == 1)
    {
      string insertQuery = 
        "INSERT INTO History " + 
        "(FieldName, NewValue, OldValue, DateAndTime) " +
        "VALUES (@FieldName, @NewValue, @OldValue, @DateAndTime)";
      SqlCommand sqlCom = 
       new SqlCommand(insertQuery, (SqlConnection)connection);
      sqlCom.Transaction = (SqlTransaction)tx;
      sqlCom.Parameters.Add("@FieldName", SqlDbType.NVarChar);
      sqlCom.Parameters.Add("@NewValue", SqlDbType.NVarChar);
      sqlCom.Parameters.Add("@OldValue", SqlDbType.NVarChar);
      sqlCom.Parameters.Add("@DateAndTime", SqlDbType.DateTime);
      try
      {
        foreach (DictionaryEntry entry in oldValues)
        {
          if ((string)entry.Value != 
            (string)newValues[entry.Key])
          {
            sqlCom.Parameters["@FieldName"].Value = 
              (string)entry.Key;
            if (newValues[entry.Key] == null)
            {
              sqlCom.Parameters["@NewValue"].Value = DBNull.Value;
            }
            else
            {
              sqlCom.Parameters["@NewValue"].Value = 
                (string)newValues[entry.Key];
            }
            if (entry.Value == null)
            {
              sqlCom.Parameters["@OldValue"].Value = DBNull.Value;
            }
            else
            {
              sqlCom.Parameters["@OldValue"].Value = 
                (string)entry.Value;
            }
            sqlCom.Parameters["@DateAndTime"].Value = DateTime.Now;
            sqlCom.ExecuteNonQuery();
          }
        }
        tx.Commit();
      }
      catch (Exception)
      {
        if (tx != null)
        {
          tx.Rollback();
        }
        throw;
      }
      finally
      {
        connection.Close();
      }            
    }
    else
    {
      if (tx != null)
      {
        tx.Rollback();
      }
      connection.Close();
    }

    DropDownList1.DataBind();
  }

  protected void DetailsView1_ItemUpdating(object sender, 
    DetailsViewUpdateEventArgs e)
  {
    keys = e.Keys;
    oldValues = e.OldValues;
    newValues = e.NewValues;
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:SqlDataSource ID="SqlDataSource1" 
      runat="server" 
      ConnectionString="<%$ ConnectionStrings:MyDB %>" 
      SelectCommand="SELECT [id], [name] FROM [table]">
    </asp:SqlDataSource>
    <asp:DropDownList ID="DropDownList1" 
      runat="server" 
      DataSourceID="SqlDataSource1" 
      DataTextField="name" 
      DataValueField="id" 
      AutoPostBack="True">
    </asp:DropDownList>
    <hr />
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
      ConnectionString="<%$ ConnectionStrings:MyDB %>" 
      SelectCommand=
        "SELECT [id], [name], [price], [memo] 
        FROM [table] 
        WHERE ([id] = @id)"             
      UpdateCommand=
        "UPDATE [table] 
        SET [name] = @name, [price] = @price, [memo] = @memo 
        WHERE [id] = @original_id" 
      OnUpdated="SqlDataSource2_Updated" 
      OnUpdating="SqlDataSource2_Updating" 
      OldValuesParameterFormatString="original_{0}">
      <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" 
          Name="id" 
          PropertyName="SelectedValue" 
          Type="Int32" />
      </SelectParameters>
      <UpdateParameters>
        <asp:Parameter Name="name" Type="String" />
        <asp:Parameter Name="price" Type="Decimal" />
        <asp:Parameter Name="memo" Type="String" />
        <asp:Parameter Name="original_id" Type="Int32" />
      </UpdateParameters>
    </asp:SqlDataSource>
    <asp:DetailsView ID="DetailsView1" 
      runat="server" 
      AutoGenerateRows="False" 
      DataKeyNames="id" 
      DataSourceID="SqlDataSource2" 
      OnItemUpdating="DetailsView1_ItemUpdating" 
      EnableModelValidation="True">
      <Fields>
        <asp:BoundField DataField="id" 
          HeaderText="id" 
          InsertVisible="False" 
          ReadOnly="True" 
          SortExpression="id" />
        <asp:BoundField DataField="name" 
          HeaderText="name" 
          SortExpression="name" />
        <asp:BoundField DataField="price" 
          HeaderText="price" 
          SortExpression="price" />
        <asp:BoundField DataField="memo" 
          HeaderText="memo" 
          SortExpression="memo" />
        <asp:CommandField ShowEditButton="True" />
      </Fields>
    </asp:DetailsView>    
  </div>
  </form>
</body>
</html>

Tags: ,

ASP.NET

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  August 2020  >>
MoTuWeThFrSaSu
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar