none
SqlParameter で datetime型がNULLか判定するには RRS feed

  • 質問

  • C#で作成したプログラムからSQL Server2000のデータを編集しようとしているのですが、

    SqlParameterにNULLを設定してもdatetime型のNULLの列を判定することができません

     

    具体的には

    SqlParameterにSqlDateTime.Nullを設定してdatetime型の列がNULLなら現在の時間で上書きする

    といった挙動を得たく、下のようなコードを書きました

     

                    System.Data.SqlClient.SqlCommand CMD = new System.Data.SqlClient.SqlCommand("", CON);
                    CMD.CommandText = "UPDATE テーブル SET 日時=@日時 WHERE 日時=@日時NULL";
                    CMD.Parameters.Add(new SqlParameter("@日時", DateTime.Now));
                    CMD.Parameters.Add(new SqlParameter("@日時NULL", System.Data.SqlTypes.SqlDateTime.Null));
                    CMD.ExecuteNonQuery();

     

    しかし実行すると値がNULLの行が上書きされません

    日時=@日時NULLを 日時 Is Null にすれば必要な挙動を得られますが、

    そうなると日時の値がNULLか否かでSQLコマンド自体を書き直さなければならず、応用が利きません

     

    SqlParameterだけでdatetime型がNULLか判定することはできないのでしょうか?

     

     

     


     

    2008年11月13日 7:37

回答

  •  loup247400 さんからの引用

    C#で作成したプログラムからSQL Server2000のデータを編集しようとしているのですが、

    SqlParameterにNULLを設定してもdatetime型のNULLの列を判定することができません

     

    具体的には

    SqlParameterにSqlDateTime.Nullを設定してdatetime型の列がNULLなら現在の時間で上書きする

    といった挙動を得たく、下のようなコードを書きました

     

                    System.Data.SqlClient.SqlCommand CMD = new System.Data.SqlClient.SqlCommand("", CON);
                    CMD.CommandText = "UPDATE テーブル SET 日時=@日時 WHERE 日時=@日時NULL";
                    CMD.Parameters.Add(new SqlParameter("@日時", DateTime.Now));
                    CMD.Parameters.Add(new SqlParameter("@日時NULL", System.Data.SqlTypes.SqlDateTime.Null));
                    CMD.ExecuteNonQuery();

     

    しかし実行すると値がNULLの行が上書きされません

    日時=@日時NULLを 日時 Is Null にすれば必要な挙動を得られますが、

    そうなると日時の値がNULLか否かでSQLコマンド自体を書き直さなければならず、応用が利きません

     

    SqlParameterだけでdatetime型がNULLか判定することはできないのでしょうか?

     

     

    基本的にはNULLかどうかはIS NULL(もしくはIS NOT NULL)で行う必要がありますが、ANSI_NULLSオプションをOFFにすることで、比較演算子(=、<>)で判断できます。

     

    (例)

    Code Snippet
    CMD.CommandText = "SET ANSI_NULLS OFF; UPDATE テーブル SET 日時=@日時 WHERE 日時=@日時NULL";

    この例ではUPDATEの前で行っていますが、データベースのオープン時に"SET ANSI_NULLS OFF"を実行、

    もしくはデータベースのプロパティ自体を変更する方法もあります。

     

    ※ただしANSIの規格に従うには、IS NULL/IS NOT NULLで比較すべきですけど。

     

    詳しくは以下のURLを参照してください。

    http://technet.microsoft.com/ja-jp/library/cc707367.aspx#XSLTsection125121120120

    2008年11月13日 9:56
  • where句を以下のようにされたらいかがでしょうか。

     

    where (日時= @日時NULL and (1 = case when @日時NULL is null then 0 else 1 end)) or
             (日時 is null and (0 = case when @日時NULL is null then 0 else 1 end))

    2008年11月13日 14:21
    モデレータ

すべての返信

  •  loup247400 さんからの引用

    C#で作成したプログラムからSQL Server2000のデータを編集しようとしているのですが、

    SqlParameterにNULLを設定してもdatetime型のNULLの列を判定することができません

     

    具体的には

    SqlParameterにSqlDateTime.Nullを設定してdatetime型の列がNULLなら現在の時間で上書きする

    といった挙動を得たく、下のようなコードを書きました

     

                    System.Data.SqlClient.SqlCommand CMD = new System.Data.SqlClient.SqlCommand("", CON);
                    CMD.CommandText = "UPDATE テーブル SET 日時=@日時 WHERE 日時=@日時NULL";
                    CMD.Parameters.Add(new SqlParameter("@日時", DateTime.Now));
                    CMD.Parameters.Add(new SqlParameter("@日時NULL", System.Data.SqlTypes.SqlDateTime.Null));
                    CMD.ExecuteNonQuery();

     

    しかし実行すると値がNULLの行が上書きされません

    日時=@日時NULLを 日時 Is Null にすれば必要な挙動を得られますが、

    そうなると日時の値がNULLか否かでSQLコマンド自体を書き直さなければならず、応用が利きません

     

    SqlParameterだけでdatetime型がNULLか判定することはできないのでしょうか?

     

     

    基本的にはNULLかどうかはIS NULL(もしくはIS NOT NULL)で行う必要がありますが、ANSI_NULLSオプションをOFFにすることで、比較演算子(=、<>)で判断できます。

     

    (例)

    Code Snippet
    CMD.CommandText = "SET ANSI_NULLS OFF; UPDATE テーブル SET 日時=@日時 WHERE 日時=@日時NULL";

    この例ではUPDATEの前で行っていますが、データベースのオープン時に"SET ANSI_NULLS OFF"を実行、

    もしくはデータベースのプロパティ自体を変更する方法もあります。

     

    ※ただしANSIの規格に従うには、IS NULL/IS NOT NULLで比較すべきですけど。

     

    詳しくは以下のURLを参照してください。

    http://technet.microsoft.com/ja-jp/library/cc707367.aspx#XSLTsection125121120120

    2008年11月13日 9:56
  • where句を以下のようにされたらいかがでしょうか。

     

    where (日時= @日時NULL and (1 = case when @日時NULL is null then 0 else 1 end)) or
             (日時 is null and (0 = case when @日時NULL is null then 0 else 1 end))

    2008年11月13日 14:21
    モデレータ
  • ありがとうございます。

     

    SqlParameterを利用することによってint型等はDBNull.ValueでNullをセットできたので

    datetime型も普通にNullを使用できると勘違いしていました。

     

    NULL値をANSI_NULLSをOFFにすることで比較できることは聞いていましたが、

    データベースのプロパティ自体を変更する方法しかないと思っていたので使用を避けていました。

    コマンドごとにプロパティを設定できるようなので、状況に応じて使い分けていきたいと思います。

    2008年11月14日 0:28
  • ご指摘の通りのコマンドで確かに期待した挙動が得られました。

    case文は使用したことがなかったので調べてみようと思います。

    ありがとうございました。

    2008年11月14日 0:31