ADO.NET での大きい値 (max) データの変更
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)