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

パラメータ化クエリと sp_executesql 変換

by WebSurfer 11. December 2019 16:05

SqlParameter クラスを利用してクエリをパラメータ化すると sp_executesql に変換されて SQL Server で実行されるのですが、変換によって予期せぬ問題が出ることがあるという話を書きます。

sp_executesql に変換

元の話は MSDN Forum のスレッド「パラメータを利用したLikeで欲しい結果が得られない 」です。

具体的にどういうことかは以下の通りです。

SQL Server のデータベースに以下の画像の通り tbl_Item テーブルがあり、Item_CD 列で LIKE 句による検索をかけるとします。型の nvarchar(6) を覚えておいてください。

tbl_Item テーブル

この tbl_Item テーブルをベースに、Visual Studio のデーターソース構成ウィザードで型付 DataSet + TableAdapter を作成します。Item_CD 列で検索できる TableAdapter のメソッドを追加します。その SELECT クエリは以下の画像の通りです。

TableAdapter のメソッド

上の画像の通り型付 DataSet + TableAdapter が作成できたら「データのプレビュー」画面を開きバラメータ ICD に %A00001% を代入して[プレビュー]ボタンをクリックします。3 レコードヒットすると期待しますが、結果は一件もヒットしません。

データのプレビュー

ICD に代入した %A00001% を %00001% に代えた場合は 3 件のレコードがヒットします。上に紹介した MSDN Forum のスレッドの最初の質問に書いてあった通りです。

データのプレビュー

何故 %A00001% では一件もヒットしないかですが、上に紹介した MSDN Forum のスレッドで質問者さんがアップしてくれたプロファイラの画像を見ると分かるので、借用して下に貼っておきます。

プロファイラの画像

理由は Item_CD 列の型 nvarchar(6) の 6 で制限を受けるからです。具体的には以下の 2 つです。

(1) SqlParameter の Size

上の画像の通り SELECT クエリをベースに Visual Studio のデータソース構成ウィザードで FillByICD, GetDataByICD メソッドを生成していますが、その際 @ICD には SqlParameter が以下のように初期化されパラメータとして設定されます。

this._commandCollection[1].Parameters.Add(
  new SqlParameter("@ICD", SqlDbType.NVarChar, 6,
  ParameterDirection.Input, 0, 0, "Item_CD", 
  DataRowVersion.Current, false, null, "", "", ""));

SqlParameter コンストラクタの第 3 引数がデータベースの Item_CD 列の型 nvarchar(6) の 6 となり、結果 SQL Server に送信するデータの最大量が 6 文字に制限されます。

上のプロファイラの画像で @CID=N'%A0000' と 6 文字になっているところに注目してください。この時は C# のコードでは "%A00001%" という文字列を代入したとのことですが、SQL Server では先頭から 6 文字だけしか受け取れていません。

(2) sp_executesql の N'@ICD nvarchar(6)'

SqlParameter を使った ADO.NET のパラメータ化クエリは、上のプロファイラ画像のとおり sp_executesql に変換されます。その中の N'@ICD nvarchar(6)' の 6 でも制限を受けます。

一番上の画像を見てください。SSMS で sp_executesql を実行したもので、そこでは @CID=N'%A00001%' としていますが、先頭から 6 文字しか SQL Server には渡せないようで、一件もヒットしていません。(@ICD には %A0000 が代入されると思われる)

ちなみに、N'@ICD nvarchar(6)', の 6 を 7 に変えると同じく @CID=N'%A00001%' で 3 件ヒットします。(@ICD には %A00001 が代入されると思われる)

何が N'@ICD nvarchar(6)', の 6 を決めているのかという疑問は残っていますが、この 6 が制限の一つであるのは結果から間違いなさそうです。

回避策は WHERE 句を WHERE Item_CD LIKE N'%' + @ICD + N'%' のように変更して、@ICD には A00001 と入力するのがよさそうです。

と言うか、普通は最初から WHERE Item_CD LIKE N'%' + @ICD + N'%' とすると思います。だから、今回の問題のような話は初耳だったのかもしれません。

LIKE 句を使った時の問題は、自分が知る限りですが先の記事「固定長パラメータの LIKE 比較」に書いた話がありました。

加えて、LIKE 句を使うと今回のような問題も起こり得るということを知ることができて、MSDN Forum のおかげで一つ利口になったような気がします。

Tags: , ,

ADO.NET

Linq to Entities / Objects

by WebSurfer 13. February 2019 15:17

Linq to Entities は、Linq to Objects とは違って、そのクエリ式が SQL Server などの DB で使われる SQL に変換できる必要があるという話を書きます。CodeZine の記事「LINQにも色々 ~SQLに変換されるモノと変換されないモノ」を見てください。図だけ借用して以下にも貼っておきます。

Linq to Objects / Enitities

その図を見れば一目瞭然だと思いますし、詳しいことは CodeZine の記事を読めば分かるのですが、それで終わってしまってはブログの記事としては面白くないので、どういう事例があったか(要するに失敗談)を書いておきます。(笑)

まず以下の例。これを実行すると ToList() のところで "System.NotSupportedException: LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression." というエラーが出ます。

public class Filter
{
    public int Id { get; set; }
    public DateTime? Date { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        // ADO.NET Entity Data Model
        TestDatabaseEntities ctx = new TestDatabaseEntities();
            
        var list = (from c in ctx.TestTable
                    select new Filter
                    {
                        Id = c.ID,
                        Date = DateTime.Parse(c.Date)
                    }).ToList();
    }
}

上の図の右側に「SQL に変換してデータベース上で実行」とありますが、DateTime.Parse メソッドが SQL に変換できないということでエラーになったということです。

上のコードで Filter クラスを初期化して Id, Date に代入するところからは C# に戻ってきて行うのかと期待してましたが、そうではなくて ToList の前まで全部 SQL に変換して SQL Server で実行しようとするようです。

ちなみに、Filter クラスの Date プロパティを string 型に変更して、DateTime.Parse なしで直接 c.Data を代入するようにすれば SQL に変換出来るようで、エラーなく期待した結果が得られます。

他の解決策としては、LINQ to Entities クエリで使用する SQL Server 関数を公開する SqlFunctions クラスのメソッドに該当するものがあれば、それの利用を検討するのがよさそうです。具体例は、Microsoft のドキュメント「方法: データベース関数を呼び出す」のサンプルコードを見てください。

上のコードの DateTime.Parse は該当する SqlFunctions クラスのメソッドは見つかりませんでしたが、「方法: カスタム データベース関数を呼び出す」にある方法も考えれば対応可能かもしれません。(未検証・未確認です)

なお、自分の環境 (Windows 10, VS2015, .NET 4.6.1, EF 6.2) では、上記の記事のリンク先の名前空間: System.Data.Objects.SqlClient の SqlFunctions クラスのメソッドでは NotSupportedException となり、名前空間: System.Data.Entity.SqlServer の SqlFunctions Class のメソッドを使う必要がありました。他の環境でどうかは分かりませんが注意してください。

もう一つは以下の例。これはコードのコメントにも書いてある通り delivery も test も Linq to Entities のクエリ式で、両方を合体して SQL に変換でき、foreach で DB に SQL を投げることができるので問題なく期待した結果が得られます。(Northwind の Order Details, Products テーブルから ADO.NET Entity Data Model を作成して使っています)

// これは Linq to Entities
var delivery = 
    from d in context.Order_Details
    group d by d.ProductID into g
    orderby g.Key
    select new
    {
       ItemCode = g.Key,
        Count = g.Sum(x => x.Quantity),
        SumAmount = g.Sum(x => x.UnitPrice * x.Quantity)
    };

// これも Linq to Entities
var test = from p in context.Products
           join d in delivery
           on p.ProductID equals d.ItemCode into dGroup
           from item in dGroup.DefaultIfEmpty()
           select new
           {
               ItemCode = p.ProductID,
               Name = p.ProductName,
               Count = item.Count,
               SumAmount = item.SumAmount
           };

// delivery を含めた test のコード全体を Linq to Entities と
// して SQL に変換することができ、foreach で DB に SQL を投げ
// ることができるので問題ない。
foreach (var x in test)
{
    Console.WriteLine(
      $"Name: {x.Name}, Count: {x.Count}, Sum: {x.SumAmount}");
}

上のような複雑なクエリ式が SQL に変換できるというのが驚きですが、それはちょっと置いといて、失敗事例はどういうことだったのかを書きます。

それは、DataTable から Linq to Objects のクエリ式を使って匿名型のオブジェクトのコレクションを取得し、それを Linq to Entities のクエリ式に組み合わせたことです。

具体的には、上のコードの delivery を以下のように DataTable(コードの table がそれ)から取得するようにしました。

// これは Linq to Object
var delivery = 
    from d in table.AsEnumerable()
    group d by d.Field<int>("ProductID") into g
    orderby g.Key
    select new
    {
        ItemCode = g.Key,
        Count = g.Sum(x => x.Field<Int16>("Quantity")),
        SumAmount = g.Sum(x => x.Field<decimal>("UnitPrice") * 
                               x.Field<Int16>("Quantity"))
    };

そうすると foreach のところで " System.NotSupportedException: Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context." というエラーになります。

エラーメッセージが前者の例とは違っていてため、最初、原因が分からなかったのですが、匿名型のオブジェクトのコレクションを Linq to Entities のクエリに組み込むと SQL に変換できないということが問題のようです。

なお、匿名型でなく、別にクラスとプロパティを定義し、それを初期化して各プロパティに代入するようにしても、上のエラーメッセージの 'Anonymous type' が定義したクラス名に変わるだけで同じエラーになります。

解決策は test のクエリ式も Linq to Objects にすることで、具体的には context.Products を context.Products.ToList() にすれば期待した結果が得られます。

Tags:

ADO.NET

About this blog

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

Calendar

<<  October 2020  >>
MoTuWeThFrSaSu
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

View posts in large calendar