NULL 値の処理 (ADO.NET)

[アーティクル]
09/17/2008

更新 : November 2007

列の値が不明または欠落している場合は、リレーショナルデータベースの NULL 値が使用されます。NULL は空文字列 (文字または日付時刻データ型) でもゼロ値 (数値データ型) でもありません。ANSI SQL-92 の規格では、すべてのデータ型について NULL は同一でなければならないと規定されているため、すべての NULL が一貫して処理されます。System.Data.SqlTypes 名前空間では、INullable インターフェイスを実装することで NULL セマンティクスが提供されます。System.Data.SqlTypes 内の各データ型には、それぞれ独自に IsNull プロパティと Null 値があり、データ型のインスタンスに割り当てることができます。

メモ :
.NET Framework 2.0 では、NULL 許容型がサポートされました。この型を使用することで、値型を拡張して基になる型のすべての値を表すことができます。これらの CLR NULL 許容型は、Nullable 構造体のインスタンスを表します。この機能は、値の型がボックスまたはアンボックスされるときに特に有効であり、オブジェクト型との互換性が強化されます。ANSI SQL の NULL は null 参照 (Visual Basic では Nothing) と動作が異なるため、CLR NULL 許容型は NULL のデータベースへの格納を意図したものではありません。データベースの ANSI SQL NULL 値を操作するには、Nullable ではなく System.Data.SqlTypes NULL を使用します。CLR NULL 許容型の操作の詳細については、Visual Basic の場合は「null 許容値型」、C# の場合は「Null 許容型の使用 (C# プログラミングガイド)」を参照してください。

.NET Framework 2.0 では、NULL 許容型がサポートされました。この型を使用することで、値型を拡張して基になる型のすべての値を表すことができます。これらの CLR NULL 許容型は、Nullable 構造体のインスタンスを表します。この機能は、値の型がボックスまたはアンボックスされるときに特に有効であり、オブジェクト型との互換性が強化されます。ANSI SQL の NULL は null 参照 (Visual Basic では Nothing) と動作が異なるため、CLR NULL 許容型は NULL のデータベースへの格納を意図したものではありません。データベースの ANSI SQL NULL 値を操作するには、Nullable ではなく System.Data.SqlTypes NULL を使用します。CLR NULL 許容型の操作の詳細については、Visual Basic の場合は「null 許容値型」、C# の場合は「Null 許容型の使用 (C# プログラミングガイド)」を参照してください。

NULL および 3 つの値を持つロジック

列定義に NULL 値を許可することで、3 つの値を持つロジックをアプリケーションに定義できます。比較によって、次の 3 つの条件のうちの 1 つを評価できます。

True
False
不明

NULL は不明であるとされるため、2 つの NULL 値を相互に比較した場合、同等であるとは見なされません。算術演算子を使用する式では、オペランドのいずれかが NULL である場合は結果も NULL になります。

NULL および SqlBoolean

System.Data.SqlTypes 間の比較により、SqlBoolean が返されます。各 SqlType の IsNull 関数により、SqlBoolean が返され、NULL 値の確認に使用できます。次の truth テーブルは、NULL 値がある場合の AND、OR、および NOT 演算子の機能を示します (T=true、F=false、U=不明または NULL)。

真理値表

ANSI_NULLS オプションについて

System.Data.SqlTypes では、ANSI_NULLS オプションが SQL Server で設定された場合と同じセマンティクスになります。すべての算術演算子 (+、-、*、/、%)、ビット演算子 (~、&、|)、およびほとんどの関数では、プロパティ IsNull を除き、オペランドまたは引数が NULL であった場合に NULL を返します。

ANSI SQL-92 標準では、WHERE 句で columnName = NULL とすることは認められていません。SQL Server では、ANSI_NULLS オプションによって、データベース内の既定の NULL 値と、NULL 値に対する比較の評価の両方が制御されます。ANSI_NULLS がオン (既定) である場合、IS NULL 演算子を NULL 値のテストを行う式で使用する必要があります。たとえば次の比較では、ANSI_NULLS がオンである場合、常に不明となります。

            colname > NULL

NULL 値を含む変数との比較でも不明となります。

            colname > @MyVariable

NULL 値をテストするには、IS NULL または IS NOT NULL 述語を使用します。これにより WHERE 句が複雑になる場合があります。たとえば、AdventureWorks Customer テーブル内の TerritoryID 列では、NULL 値が許可されています。SELECT ステートメントによってその他の値と合わせて NULL 値もテストされる場合は、IS NULL 述語を含める必要があります。

SELECT CustomerID, AccountNumber, TerritoryID
FROM AdventureWorks.Sales.Customer
WHERE TerritoryID IN (1, 2, 3)
   OR TerritoryID IS NULL

SQL Server で ANSI_NULLS をオフに設定すると、等値演算子を使用する式を作成し、NULL 値と比較できます。ただし、別の接続からその接続に対して NULL オプションを設定することを防ぐことはできません。IS NULL を使用した NULL 値のテストは、接続の ANSI_NULLS 設定にかかわらず、常に動作します。

DataSet では、ANSI_NULLS をオフに設定することはできません。System.Data.SqlTypes における NULL 値の処理については、常に ANSI SQL-92 標準に準拠します。

NULL 値の割り当て

NULL 値は特殊であり、ストレージおよび割り当てのセマンティクスは、システムおよびストレージシステムの種類によって異なります。Dataset は、異なる種類のシステムおよびストレージシステムで使用できるように設計されています。

このセクションでは、異なる種類のシステム上にある DataRow の DataColumn に NULL 値を割り当てるための NULL セマンティクスについて説明します。

DBNull.Value
この割り当ては、任意の型の DataColumn で有効です。その型が INullable を実装している場合は、DBNull.Value が厳密に型指定された適切な NULL 値に強制的に変換されます。
SqlType.Null
すべての System.Data.SqlTypes データ型で INullable を実装します。暗黙的なキャスト演算子を使用して、厳密に型指定された NULL 値を列のデータ型に変換できる場合は、割り当てが行われます。変換できない場合は、無効なキャスト例外がスローされます。
null
特定の DataColumn データ型について 'null' が有効であった場合、INullable 型 (SqlType.Null) に関連付けられている、適切な DbNull.Value または Null に強制的に変換されます。
derivedUdt.Null
UDT 列の場合、NULL 値は常に DataColumn に関連付けられている型に基づいて格納されます。DataColumn に関連付けられている UDT が INullable を実装せず、サブクラスで実装される場合を考えます。この場合、派生クラスに関連付けられた厳密に型指定された NULL 値が割り当てられていれば、NULL ストレージが常に DataColumn のデータ型と一致するため、型指定されていない DbNull.Value として格納されます。

メモ :
Nullable<T> または Nullable 構造体は、現在 DataSet ではサポートされていません。

複数列 (行) の割り当て

ItemArray を行にマップできる、DataTable.Add や DataTable.LoadDataRow などの API については、DataColumn の既定値に 'null' をマップします。配列内のオブジェクトに DbNull.Value またはその厳密に型指定された値が含まれる場合は、上記と同じ規則が適用されます。

さらに、DataRow.["columnName"] の NULL 値割り当てのインスタンスには、次の規則が適用されます。

既定の default 値は DbNull.Value です。ただし、それが厳密に型指定された適切な NULL 値となる、厳密に型指定された NULL 列は例外です。
XML ファイルへのシリアル化中に NULL 値が書き出されることはありません ("xsi:nil" と同じ)。
既定値を含む NULL 以外のすべての値は、常に XML へのシリアル化中に書き出されます。これは、NULL 値 (xsi:nil) が明示的で既定値が暗黙的である、XSD/XML セマンティクスとは異なります (XML にない場合は、検証パーサーが関連付けられた XSD スキーマから取得します)。逆に DataTable では、NULL 値が暗黙的で、既定値が明示的になります。
XML 入力から読み取られた各行の欠落している列値にはすべて、NULL が割り当てられます。NewRow または類似のメソッドを使用して作成された行には、DataColumn の既定値が割り当てられます。
IsNull メソッドは、DbNull.Value と INullable.Null のどちらに対しても true を返します。

NULL 値の割り当て

任意の System.Data.SqlTypes インスタンスの既定値は NULL です。

System.Data.SqlTypes の NULL 値は型固有であり、DbNull などの 1 つの値で表すことはできません。NULL 値の確認には、IsNull プロパティを使用します。

NULL 値は、次のコードサンプルに示すように DataColumn に割り当てることができます。NULL 値は、例外をトリガすることなく SqlTypes 変数に直接割り当てることができます。

例

次のコードサンプルでは、SqlInt32 および SqlString として定義される 2 つの列を持つ DataTable が作成されます。このコードでは既知の値の行が 1 行、NULL 値の行が 1 行追加され、DataTable を通じて反復処理されます。これにより値が変数に割り当てられ、コンソールウィンドウに出力が表示されます。

Private Sub WorkWithSqlNulls()
    Dim table As New DataTable()

    ' Specify the SqlType for each column.
    Dim idColumn As DataColumn = _
      table.Columns.Add("ID", GetType(SqlInt32))
    Dim descColumn As DataColumn = _
      table.Columns.Add("Description", GetType(SqlString))

    ' Add some data.
    Dim row As DataRow = table.NewRow()
    row("ID") = 123
    row("Description") = "Side Mirror"
    table.Rows.Add(row)

    ' Add null values.
    row = table.NewRow()
    row("ID") = SqlInt32.Null
    row("Description") = SqlString.Null
    table.Rows.Add(row)

    ' Initialize variables to use when
    ' extracting the data.
    Dim isColumnNull As SqlBoolean = False
    Dim idValue As SqlInt32 = SqlInt32.Zero
    Dim descriptionValue As SqlString = SqlString.Null

    ' Iterate through the DataTable and display the values.
    For Each row In table.Rows
        ' Assign values to variables. Note that you 
        ' do not have to test for null values.
        idValue = CType(row("ID"), SqlInt32)
        descriptionValue = CType(row("Description"), SqlString)

        ' Test for null value with ID column
        isColumnNull = idValue.IsNull

        ' Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}", _
          isColumnNull, idValue, descriptionValue)
        Console.WriteLine()
    Next row
End Sub

static private void WorkWithSqlNulls()
{
    DataTable table = new DataTable();

    // Specify the SqlType for each column.
    DataColumn idColumn =
        table.Columns.Add("ID", typeof(SqlInt32));
    DataColumn descColumn =
        table.Columns.Add("Description", typeof(SqlString));

    // Add some data.
    DataRow nRow = table.NewRow();
    nRow["ID"] = 123;
    nRow["Description"] = "Side Mirror";
    table.Rows.Add(nRow);

    // Add null values.
    nRow = table.NewRow();
    nRow["ID"] = SqlInt32.Null;
    nRow["Description"] = SqlString.Null;
    table.Rows.Add(nRow);

    // Initialize variables to use when
    // extracting the data.
    SqlBoolean isColumnNull = false;
    SqlInt32 idValue = SqlInt32.Zero;
    SqlString descriptionValue = SqlString.Null;

    // Iterate through the DataTable and display the values.
    foreach (DataRow row in table.Rows)
    {
        // Assign values to variables. Note that you 
        // do not have to test for null values.
        idValue = (SqlInt32)row["ID"];
        descriptionValue = (SqlString)row["Description"];

        // Test for null value in ID column.
        isColumnNull = idValue.IsNull;

        // Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}",
            isColumnNull, idValue, descriptionValue);
        Console.WriteLine();
    }

この例を実行すると、次の結果が表示されます。

isColumnNull=False, ID=123, Description=Side Mirror
isColumnNull=True, ID=Null, Description=Null

NULL 値と SqlTypes および CLR 型との比較

NULL 値を比較する場合は、Equals メソッドによって System.Data.SqlTypes で NULL 値を評価する方法と、CLR 型を使用する方法との違いを理解することが重要です。System.Data.SqlTypesEquals メソッドではすべて、NULL 値の評価にデータベースセマンティクスが使用されます。一方または両方の値が NULL である場合は、比較によって NULL が得られます。その一方で、2 つの System.Data.SqlTypes に対して CLR Equals メソッドを使用した場合は、両方が NULL であれば true が得られます。これは、CLR String.Equals メソッドなどのインスタンスメソッドを使用した場合と、SqlString.Equals などの静的/共有メソッドを使用した場合の違いを反映しています。

次のコードサンプルでは、SqlString.Equals メソッドと String.Equals メソッドにそれぞれ NULL 値のペアを渡し、次に空の文字列のペアを渡した場合の、各メソッドの結果の違いを示します。

Private Sub CompareNulls()
    ' Create two new null strings.
    Dim a As New SqlString
    Dim b As New SqlString

    ' Compare nulls using static/shared SqlString.Equals.
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b))

    ' Compare nulls using instance method String.Equals.
    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two nulls={0}", StringEquals(a, b))

    ' Make them empty strings.
    a = ""
    b = ""

    ' When comparing two empty strings (""), both the shared/static and
    ' the instance Equals methods evaluate to true.
    Console.WriteLine()
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b))

    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two empty strings={0}", StringEquals(a, b))
End Sub

Private Function SqlStringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' SqlString.Equals uses database semantics for evaluating nulls.
    Dim returnValue As String = SqlString.Equals(string1, string2).ToString()
    Return returnValue
End Function

Private Function StringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' String.Equals uses CLR type semantics for evaluating nulls.
    Dim returnValue As String = string1.Equals(string2).ToString()
    Return returnValue
End Function

    private static void CompareNulls()
    {
        // Create two new null strings.
        SqlString a = new SqlString();
        SqlString b = new SqlString();

        // Compare nulls using static/shared SqlString.Equals.
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b));

        // Compare nulls using instance method String.Equals.
        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two nulls={0}", StringEquals(a, b));

        // Make them empty strings.
        a = "";
        b = "";

        // When comparing two empty strings (""), both the shared/static and
        // the instance Equals methods evaluate to true.
        Console.WriteLine();
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b));

        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two empty strings={0}", StringEquals(a, b));
    }

    private static string SqlStringEquals(SqlString string1, SqlString string2)
    {
        // SqlString.Equals uses database semantics for evaluating nulls.
        string returnValue = SqlString.Equals(string1, string2).ToString();
        return returnValue;
    }

    private static string StringEquals(SqlString string1, SqlString string2)
    {
        // String.Equals uses CLR type semantics for evaluating nulls.
        string returnValue = string1.Equals(string2).ToString();
        return returnValue;
    }
}

このコードを実行すると、次の出力が生成されます。

SqlString.Equals shared/static method:
  Two nulls=Null

String.Equals instance method:
  Two nulls=True

SqlString.Equals shared/static method:
  Two empty strings=True

String.Equals instance method:
  Two empty strings=True

参照

その他の技術情報

SQL Server データ型と ADO.NET

NULL 値の処理 (ADO.NET)

NULL および 3 つの値を持つロジック

NULL および SqlBoolean

ANSI_NULLS オプションについて

NULL 値の割り当て

複数列 (行) の割り当て

NULL 値の割り当て

例

NULL 値と SqlTypes および CLR 型との比較

参照

その他の技術情報

その他のリソース