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

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

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

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

大きな値のデータ型の詳細については、SQL Server オンラインブックの「大きな値のデータ型の使用」を参照してください。

大きい値型の制限事項

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

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

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

Transact-SQL の OPENROWSET 関数は、リモートデータへの接続およびアクセスに 1 回だけ使用できます。 OPENROWSET は、クエリの FROM 句でテーブル名と同様に参照できます。 INSERT、UPDATE、または DELETE ステートメントの対象のテーブルとしても参照できます。

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

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

次の例は、写真を、AdventureWorks2022 サンプルデータベースの 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 AdventureWorks2022;
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())  
    {  
        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())  
{  
    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())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }

GetBytes を使用したデータ取得

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

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  
    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())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}

例

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

using Microsoft.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

class Program
{
    static void Main()
    {
        // Supply any valid DocumentID value and file path.
        // The value 3 is supplied for DocumentID, and a literal
        // string for the file path where the image will be saved. 1, 60
        TestGetSqlBytes(7, @"c:\temp\");
        Console.ReadLine();
    }
    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();
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property 
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

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

大きい値型は、SqlParameter オブジェクト内の小さい値型と同じ方法で、SqlParameter オブジェクト内で使用できます。次の例に示すように、大きい値型は SqlParameter 値として取得することができます。このコードは、次の GetDocumentSummary ストアドプロシージャが AdventureWorks2022 サンプルデータベースに存在することを前提としています。ストアドプロシージャでは @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 出力パラメーターに戻された結果がコンソールウィンドウに表示されます。

using Microsoft.Data.SqlClient;
class Program
{
    static void Main()
    {
        // Supply any valid Document ID value.
        // The value 7 is supplied for demonstration purposes.
        string summaryString = GetDocumentSummary(7);
        Console.ReadLine();
    }
    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;
            }
        }
    }
    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}