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

[アーティクル]
05/10/2023

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

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

詳細については、「大きな値のデータ型の使用」を参照してください。

大きい値型の制限事項

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

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

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

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

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

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

次の例は、写真を、AdventureWorks サンプルデータベースの ProductPhoto テーブルに挿入しています。 BULK OPENROWSET プロバイダーを使用する場合は、すべての列に値を挿入しない場合でも、列名を挙げてリストを作成する必要があります。この場合の主キーは 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 引数は列のセクションの長さを示します。

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

Note

@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) 列の内容を取得できます。次のコードフラグメントでは、SqlCommand という名前の cmd オブジェクトによってテーブルから varbinary(max) データが選択され、SqlDataReader という名前の reader オブジェクトによってデータが 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 を使用したデータの取得

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

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 を使用したデータの取得

GetSqlBinary の SqlDataReader メソッドを使用して、varbinary(max) 列の内容を取得できます。次のコードフラグメントでは、SqlCommand という名前の cmd オブジェクトによってテーブルから varbinary(max) データが選択され、SqlDataReader という名前の reader オブジェクトによってデータが 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 を使用したデータの取得

GetBytes の SqlDataReader メソッドにより、指定された配列のオフセットから開始するバイト配列に、指定された列のオフセットからバイトのストリームが読み込まれます。次のコードフラグメントは、バイト配列に対するバイトを取得する 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 を使用したデータの取得

GetValue の SqlDataReader メソッドにより、指定した列オフセットから値が配列に読み込まれます。次のコードフラグメントでは、SqlDataReader という名前の reader オブジェクトによって、最初の列のオフセットからバイナリデータが取得され、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 型への変換

varchar(max) などの任意の文字列変換メソッドを使用して、nvarchar(max) または ToString 列の内容を変換できます。次のコードフラグメントは、データを取得する 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 オブジェクトが作成され、Gif ImageFormat に保存されます。

static void TestGetSqlBytes(int documentID, string filePath)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(GetConnectionString()))
    {
        SqlCommand command = connection.CreateCommand();
        SqlDataReader reader = default!;
        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("@ProductPhotoID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);
            connection.Open();

            string photoName = default!;

            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(bytes.Stream))
                        {
                            var 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
        {
            reader?.Dispose();
        }
    }
}

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

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

大きい値型は、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 出力パラメーターに戻された結果がコンソールウィンドウに表示されます。

static string? GetDocumentSummary(int documentID)
{
    //Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(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("@DocumentID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);

            // Set up the output parameter to retrieve the summary.
            SqlParameter paramSummary =
                new("@DocumentSummary",
                SqlDbType.NVarChar, -1)
                {
                    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;
        }
    }
}

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

次の方法で共有