ADO.NET の Large-Value (最大) データの変更

ラージオブジェクト (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 句のほか、集計関数、結合、サブクエリでも使用できます。

詳細については、「 Large-Value データ型の使用」を参照してください。

大きい値型の制限事項

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

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

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

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

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

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

{ <オブジェクト> }

SET

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

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

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

注

@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 での大きい値型の使用

SqlCommandオブジェクトをSqlParameterと共に使用して、大きな値を送信します。 SqlDataReader を使用して結果を取得するか、DataSet または DataTable オブジェクトに SqlDataAdapter を設定します。大きな値型を、関連するより小さい値データ型と同様に処理します。

GetSqlBytes を使用したデータ取得

varbinary(max) 列の内容を SqlBytes として取得するには、GetSqlBytes を呼び出します。次のフラグメントは、 cmd がバイナリデータを選択する SqlCommand であり、 reader がバイナリデータを取得する SqlDataReader であることを前提としています。

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 を使用してデータを取得する

varchar(max)またはnvarchar(max)列の内容を取得するには、GetSqlCharsを呼び出します。次のフラグメントは、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 を使用したデータの取得

varbinary(max) 列の内容を SqlBinary の値として取得するには、GetSqlBinary を呼び出します。次のフラグメントは、 cmd がバイナリデータを選択する SqlCommand であり、 reader がバイナリデータを取得する SqlDataReader であることを前提としています。

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 メソッドは、指定した列オフセットから、指定した配列オフセットのバイト配列にバイトを読み取ります。次のフラグメントは、 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 メソッドは、列の値をオブジェクトとして返します。次のフラグメントは、 reader が SqlDataReaderであることを前提としています。 C# では、バイナリ列は byte[] を返し、文字列の列は string を返します。 Visual Basicでは、対応する型はByte()およびStringです。

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

    ' 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);
}

例

次のコードは、LargePhoto データベースの ProductPhoto テーブルから名前と AdventureWorks 値を取得し、ファイルに保存します。このイメージ変換の例では、WindowsとSystem.Drawing.Common NuGet パッケージが必要です。この制限は、プロバイダーのバイナリデータ取得ではなく、画像変換に適用されます。 GetSqlBytes メソッドは Stream を返します。この Stream は SqlBytes プロパティを公開します。このコードでは、ストリームを使用して Bitmap オブジェクトを作成し、GIF 形式で保存します。

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($"{photoName} is unavailable.");
                    }
                    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 {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 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 値として取得することができます。このコードでは、AdventureWorks サンプルデータベースに次の GetDocumentSummary ストアドプロシージャが存在することを前提としています。ストアドプロシージャでは @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
        {
            // Set up 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

こちらも参照ください

最終更新: 2026-10-27