ADO.NET での大きい値 (max) データの変更

2008-09-17

更新 : November 2007

SQL Server 2005 より前のバージョンの SQL Server では、ラージオブジェクト (LOB) データ型を扱う場合に特殊な処理が必要でした。LOB データ型は、最大行サイズが 8 KB を超えるデータ型です。SQL Server 2005 では、varchar データ型、nvarchar データ型、および varbinary データ型に max 指定子が導入され、2^32 バイトもの大きさの値を格納できるようになりました。テーブル列および Transact-SQL 変数により、varchar(max)、nvarchar(max)、または varbinary(max) データ型を指定できます。ADO.NET では、DataReader によって新しい max データ型をフェッチでき、特殊な処理を行うことなく入力および出力両方のパラメータ値として指定することもできます。サイズの大きい varchar データ型の場合は、データを段階的に取得および更新できます。

max データ型は、Transact-SQL 変数として比較に使用したり、連結に使用したりできます。これらは SELECT ステートメントの DISTINCT 句、ORDER BY 句、GROUP BY 句で使用できるほか、集約、結合、サブクエリでも使用できます。

次の表は、SQL Server オンラインブックのドキュメントへのリンクを提供します。

SQL Server 2000	SQL Server 2005	SQL Server 2008
データ型	大きな値のデータ型の使用	大きな値のデータ型の使用

大きい値型の制限事項

max データ型には、小さいデータ型にはない、次の制限事項が適用されます。

sql_variant に大きい varchar データ型を含めることはできません。
大きい varchar 列はインデックス内のキー列として指定することはできません。非クラスタ化インデックスに含まれる列で使用できます。
大きい varchar 列はパーティション分割のキー列として使用できません。

Transact-SQL での大きい値型の使用

Transact-SQL の OPENROWSET 関数は、リモートデータへの接続およびアクセスに 1 回だけ使用できます。この関数には、OLE DB データソースからリモートデータにアクセスするために必要な、すべての接続情報が含まれています。OPENROWSET は、クエリの FROM 句でテーブル名と同様に参照できます。OLE DB プロバイダの機能に従って、INSERT、UPDATE、または DELETE ステートメントのターゲットテーブルとして参照することもできます。

OPENROWSET 関数は、BULK 行セットプロバイダの追加によって SQL Server 2005 で拡張されており、データをターゲットテーブルに読み込むことなく、データをファイルから直接読み取ることができます。これにより、OPENROWSET を単純な INSERT SELECT ステートメントで使用できます。

OPENROWSETBULK オプション引数により、データの読み取りの開始位置および終了位置、エラーの処理、データの解釈の制御が大幅に容易になります。たとえば、データファイルを 1 行として、あるいは varbinary、varchar、または nvarchar 型の 1 列の行セットとして読み取るように指定できます。完全な構文とオプションについては、SQL Server オンラインブックを参照してください。

次の例では、AdventureWorks サンプルデータベースの ProductPhoto テーブルに写真を挿入しています。BULKOPENROWSET プロバイダを使用する場合は、すべての列に値を挿入しない場合でも、列名を挙げてリストを作成する必要があります。この場合の主キーは ID 列として定義され、列リストから省略することもできます。ただし、OPENROWSET ステートメントの最後に相関関係名 (この場合は ThumbnailPhoto) を指定する必要があります。これにより、ファイルが読み込まれる ProductPhoto テーブルの列との相関関係が定義されます。

INSERT Production.ProductPhoto (
    ThumbnailPhoto, 
    ThumbnailPhotoFilePath, 
    LargePhoto, 
    LargePhotoFilePath)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET 
    (BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto

UPDATE .WRITE を使用したデータの更新

Transact-SQL の UPDATE ステートメントでは、varchar(max)、nvarchar(max)、または varbinary(max) 列の内容を変更するための、新しい WRITE 構文を使用できます。これにより、データの部分的な更新が可能になります。次に UPDATE .WRITE 構文を省略形で示します。

UPDATE

{ <object> }

SET

{ column_name = { .WRITE ( expression , @Offset , @Length ) }

WRITE メソッドは、column_name の値のセクションが変更されることを指定します。この式は column_name にコピーされる値で、@Offset は式が記述される開始位置であり、@Length 引数は列のセクションの長さを示します。

条件	結果
expression が NULL	@Length は無視され、column_name の値は指定された @Offset で切り捨てられます。
@Offset が NULL	更新操作によって既存の column_name の値の最後に式が追加され、@Length が無視されます。
@Offset が column_name の値の長さより長い	SQL Server からエラーが返されます。
@Length が NULL	更新操作により column_name の値の @Offset から最後までのすべてのデータが削除されます。

メモ :
@Offset または @Length のいずれにも負数を指定することはできません。

例

この Transact-SQL の例では、AdventureWorks データベースの Document テーブルの nvarchar(max) 列である DocumentSummary の値の一部が更新されます。置換後の単語、置換する単語の既存データ内での開始位置 (オフセット)、置換する文字数 (長さ) を指定することで、'components' という単語が 'features' という単語に置換されます。この例では、結果を比較するために、UPDATE ステートメントの前後に SELECT ステートメントを指定しています。

USE AdventureWorks;
GO
--View the existing value.
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 3;
GO
-- The first sentence of the results will be:
-- Reflectors are vital safety components of your bicycle.

--Modify a single word in the DocumentSummary column
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
WHERE DocumentID = 3 ;
GO 
--View the modified value.
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 3;
GO
-- The first sentence of the results will be:
-- Reflectors are vital safety features of your bicycle.

ADO.NET での大きい値型の使用

ADO.NET で大きい値型を使用するには、大きい値型を SqlDataReader で SqlParameterオブジェクトとして指定して結果セットを返すようにするか、または SqlDataAdapter を使用して DataSet/DataTable に入力します。大きい値型と、関連する小さい値型の使用方法に違いはありません。

GetSqlBytes を使用したデータの取得

SqlDataReader の GetSqlBytes メソッドを使用して、varbinary(max) 列の内容を取得できます。次のコードフラグメントでは、cmd という名前の SqlCommand オブジェクトによってテーブルから varbinary(max) データが選択され、reader という名前の SqlDataReader オブジェクトによってデータが SqlBytes として取得されることを想定しています。

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
    Dim bytes As SqlBytes = reader.GetSqlBytes(0)
End While

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
    {
        SqlBytes bytes = reader.GetSqlBytes(0);
    }

GetSqlChars を使用したデータの取得

SqlDataReader の GetSqlChars メソッドを使用して、varchar(max) または nvarchar(max) 列の内容を取得できます。次のコードフラグメントでは、cmd という名前の SqlCommand オブジェクトによってテーブルから nvarchar(max) データが選択され、reader という名前の SqlDataReader オブジェクトによってデータが取得されることを想定しています。

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
    Dim buffer As SqlChars = reader.GetSqlChars(0)
End While

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
    SqlChars buffer = reader.GetSqlChars(0);
}

GetSqlBinary を使用したデータの取得

SqlDataReader の GetSqlBinary メソッドを使用して、varbinary(max) 列の内容を取得できます。次のコードフラグメントでは、cmd という名前の SqlCommand オブジェクトによってテーブルから varbinary(max) データが選択され、reader という名前の SqlDataReader オブジェクトによってデータが SqlBinary ストリームとして取得されることを想定しています。

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
    Dim binaryStream As SqlBinary = reader.GetSqlBinary(0)
End While

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
    {
        SqlBinary binaryStream = reader.GetSqlBinary(0);
    }

GetBytes を使用したデータの取得

SqlDataReader の GetBytes メソッドにより、指定された配列のオフセットから開始するバイト配列に、指定された列のオフセットからバイトのストリームが読み込まれます。次のコードフラグメントでは、reader という名前の SqlDataReader オブジェクトによってバイトがバイト配列に読み込まれることが想定されています。ただし GetSqlBytes とは異なり、GetBytes では配列バッファのサイズを指定する必要があります。

While reader.Read()
    Dim buffer(4000) As Byte
    Dim byteCount As Integer = _
    CInt(reader.GetBytes(1, 0, buffer, 0, 4000))
End While

while (reader.Read())
{
    byte[] buffer = new byte[4000];
    long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);
}

GetValue を使用したデータの取得

SqlDataReader の GetValue メソッドにより、指定した列オフセットから値が配列に読み込まれます。次のコードフラグメントでは、reader という名前の SqlDataReader オブジェクトによって、最初の列のオフセットからバイナリデータが取得され、2 番目の列のオフセットから文字列データが取得されることが想定されています。

While reader.Read()
    ' Read the data from varbinary(max) column
    Dim binaryData() As Byte = CByte(reader.GetValue(0))

    ' Read the data from varchar(max) or nvarchar(max) column
    Dim stringData() As String = Cstr((reader.GetValue(1))
End While

while (reader.Read())
{
    // Read the data from varbinary(max) column
    byte[] binaryData = (byte[])reader.GetValue(0);

    // Read the data from varchar(max) or nvarchar(max) column
    String stringData = (String)reader.GetValue(1);
}

大きい値型から CLR 型への変換

ToString などの任意の文字列変換メソッドを使用して、varchar(max) または nvarchar(max) 列の内容を変換できます。次のコードフラグメントでは、reader という名前の SqlDataReader オブジェクトによってデータが取得されることが想定されています。

While reader.Read()
    Dim str as String = reader(0).ToString()
    Console.WriteLine(str)
End While

while (reader.Read())
{
     string str = reader[0].ToString();
     Console.WriteLine(str);
}

例

次のコードでは、AdventureWorks データベースの ProductPhoto テーブルから名前と LargePhoto オブジェクトが取得され、ファイルに保存されます。このアセンブリは、System.Drawing 名前空間への参照を指定してコンパイルする必要があります。SqlDataReader の GetSqlBytes メソッドにより、Stream プロパティを公開する SqlBytes オブジェクトが返されます。コードではこのオブジェクトを使用して新しい Bitmap オブジェクトが作成され、GifImageFormat に保存されます。

Private Sub GetPhoto( _
  ByVal documentID As Integer, ByVal filePath As String)
    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        Dim command As SqlCommand = connection.CreateCommand()
        Dim reader As SqlDataReader
        Try
            ' Setup the command
            command.CommandText = _
              "SELECT LargePhotoFileName, LargePhoto FROM" _
                & " Production.ProductPhoto" _
                & " WHERE ProductPhotoID=@ProductPhotoID"
            command.CommandType = CommandType.Text

            ' Declare the parameter
            Dim paramID As SqlParameter = _
                New SqlParameter("@ProductPhotoID", SqlDbType.Int)
            paramID.Value = documentID
            command.Parameters.Add(paramID)
            connection.Open()

            Dim photoName As String

            reader = _
             command.ExecuteReader(CommandBehavior.CloseConnection)

            If reader.HasRows Then
                While reader.Read()
                    ' Get the name of the file
                    photoName = reader.GetString(0)

                    ' Ensure that the column isn't null
                    If (reader.IsDBNull(1)) Then
                        Console.WriteLine("{0} is unavailable.", photoName)
                    Else
                        Dim bytes As SqlBytes = reader.GetSqlBytes(1)
                        Using productImage As Bitmap = _
                          New Bitmap(bytes.Stream)
                            Dim fileName As String = filePath & photoName

                            ' Save in gif format.
                            productImage.Save( _
                              fileName, ImageFormat.Gif)
                            Console.WriteLine("Successfully created {0}.", fileName)
                        End Using
                    End If
                End While
            Else
                Console.WriteLine("No records returned.")
            End If
        Catch ex As Exception
            Console.WriteLine("Exception: {0}", ex.Message)
        End Try
    End Using
End Sub

static private void TestGetSqlBytes(int documentID, string filePath)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new SqlConnection(GetConnectionString()))
    {
        SqlCommand command = connection.CreateCommand();
        SqlDataReader reader = null;
        try
        {
            // Setup the command
            command.CommandText =
                "SELECT LargePhotoFileName, LargePhoto "
                + "FROM Production.ProductPhoto "
                + "WHERE ProductPhotoID=@ProductPhotoID";
            command.CommandType = CommandType.Text;

            // Declare the parameter
            SqlParameter paramID =
                new SqlParameter("@ProductPhotoID", SqlDbType.Int);
            paramID.Value = documentID;
            command.Parameters.Add(paramID);
            connection.Open();

            string photoName = null;

            reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    // Get the name of the file.
                    photoName = reader.GetString(0);

                    // Ensure that the column isn't null
                    if (reader.IsDBNull(1))
                    {
                        Console.WriteLine("{0} is unavailable.", photoName);
                    }
                    else
                    {
                        SqlBytes bytes = reader.GetSqlBytes(1);
                        using (Bitmap productImage = new Bitmap(bytes.Stream))
                        {
                            String fileName = filePath + photoName;

                            // Save in gif format.
                            productImage.Save(fileName, ImageFormat.Gif);
                            Console.WriteLine("Successfully created {0}.", fileName);
                        }
                    }
                }
            }
            else
            {
                Console.WriteLine("No records returned.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
        }
    }
}

大きな値型パラメータの使用

大きい値型は、SqlParameter オブジェクト内の小さい値型と同じ方法で、SqlParameter オブジェクト内で使用できます。次の例に示すように、大きい値型は SqlParameter 値として取得することができます。このコードでは、次の GetDocumentSummary ストアドプロシージャが、AdventureWorks サンプルデータベースに存在することが想定されています。ストアドプロシージャでは @DocumentID という名前の入力パラメータを受け取り、@DocumentSummary 出力パラメータの DocumentSummary 列の内容を返します。

CREATE PROCEDURE GetDocumentSummary 
(
    @DocumentID int,
    @DocumentSummary nvarchar(MAX) OUTPUT
)
AS
SET NOCOUNT ON
SELECT  @DocumentSummary=Convert(nvarchar(MAX), DocumentSummary)
FROM    Production.Document
WHERE   DocumentID=@DocumentID

例

ADO.NET コードにより、SqlConnection および SqlCommand オブジェクトが作成され、GetDocumentSummary ストアドプロシージャが実行されることで、ドキュメントの概要が取得され、大きい値型として格納されます。このコードによって @DocumentID 入力パラメータの値が渡され、@DocumentSummary 出力パラメータに戻された結果がコンソールウィンドウに表示されます。

Private Function GetDocumentSummary( _
  ByVal documentID As Integer) As String

    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()

        ' Setup the command to execute the stored procedure.
        command.CommandText = "GetDocumentSummary"
        command.CommandType = CommandType.StoredProcedure

        ' Set up the input parameter for the DocumentID.
        Dim paramID As SqlParameter = _
            New SqlParameter("@DocumentID", SqlDbType.Int)
        paramID.Value = documentID
        command.Parameters.Add(paramID)

        ' Set up the output parameter to retrieve the summary.
        Dim paramSummary As SqlParameter = _
            New SqlParameter("@DocumentSummary", _
               SqlDbType.NVarChar, -1)
        paramSummary.Direction = ParameterDirection.Output
        command.Parameters.Add(paramSummary)

        ' Execute the stored procedure.
        command.ExecuteNonQuery()
        Console.WriteLine(paramSummary.Value)
        Return paramSummary.Value.ToString
    End Using
End Function

static private string GetDocumentSummary(int documentID)
{
    //Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new SqlConnection(GetConnectionString()))
    {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        try
        {
            // Setup the command to execute the stored procedure.
            command.CommandText = "GetDocumentSummary";
            command.CommandType = CommandType.StoredProcedure;

            // Set up the input parameter for the DocumentID.
            SqlParameter paramID =
                new SqlParameter("@DocumentID", SqlDbType.Int);
            paramID.Value = documentID;
            command.Parameters.Add(paramID);

            // Set up the output parameter to retrieve the summary.
            SqlParameter paramSummary =
                new SqlParameter("@DocumentSummary",
                SqlDbType.NVarChar, -1);
            paramSummary.Direction = ParameterDirection.Output;
            command.Parameters.Add(paramSummary);

            // Execute the stored procedure.
            command.ExecuteNonQuery();
            Console.WriteLine((String)(paramSummary.Value));
            return (String)(paramSummary.Value);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return null;
        }
    }
}

参照

概念

SQL Server データ型のマッピング (ADO.NET)

その他の技術情報

SQL Server のバイナリデータと大きな値のデータ (ADO.NET)

ADO.NET における SQL Server データ操作