バイナリデータでの UPDATETEXT の使用 (ADO.NET)

[アーティクル]
05/20/2011

SQL Server 2005 より前のバージョンの SQL Server では、バイナリラージオブジェクト (BLOB) を操作するためのオプションは限られています。 BLOB をデータベースに書き込むには、文字列値またはバイト配列用に、フィールドを挿入または更新します。どちらの種類のデータを使用するかは、データベースのフィールドの種類によって決まります。ただし、BLOB が非常に大きい場合は、単一の値として書き込むと大量のシステムメモリが使用され、アプリケーションのパフォーマンスが低下する場合があります。

BLOB 値の書き込み時に使用されるメモリ量を減らすために、一般的には、BLOB を "チャンク" 単位でデータベースに書き込む方法が使われます。この方法で BLOB をデータベースに書き込む処理は、使用しているデータベースの機能によって異なります。

Transact-SQL の UPDATETEXT の例

SQL Server に BLOB をチャンク単位で書き込む方法を次の例に示します。このサンプルでは、BLOB である従業員のイメージを含む新しいレコードを Northwind データベースの Employees テーブルに追加します。このサンプルでは SQL Server の UPDATETEXT 関数を使用して、新しく追加された従業員のイメージを、指定されたサイズのチャンク単位で Photo フィールドに書き込みます。

UPDATETEXT 関数では、更新する BLOB フィールドへのポインターが必要です。このサンプルでは、新しい従業員レコードが追加されると、SQL Server の TEXTPTR 関数を呼び出して、新しいレコードの Photo フィールドへのポインターを取得します。このポインター値は出力パラメーターとして戻されます。サンプルのコードはこのポインターを保持して、データのチャンクを追加するときに、UPDATETEXT に渡します。

新しい従業員レコードを挿入し、Photo フィールドへのポインターを保持するために使用される Transact-SQL の例を次に示します。ここで、@Identity と @Pointer は、SqlCommand の出力パラメーターとして指定されています。

INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) 
  Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0)
SELECT @Identity = SCOPE_IDENTITY()
SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity

初期値 0x0 (null) が Photo フィールドに挿入されていることに注意してください。これにより、新しく挿入されたレコードの Photo フィールドのポインター値を取得することができます。また、null 値は、追加されるデータのチャンクには影響しません。

このサンプルは、新しく挿入されたレコードの Photo フィールドへのポインターを保持しているため、SQL Server の UPDATETEXT 関数を使用して、BLOB フィールドにデータのチャンクを追加できます。 UPDATETEXT 関数は、フィールド識別子 (Employees.Photo)、BLOB フィールドへのポインター、現在のチャンクが書き込まれる BLOB 内の場所を表すオフセット値、および追加されるデータのチャンクを、入力として受け取ります。 UPDATETEXT 関数の構文を記述したコードサンプルを次に示します。ここで、@Pointer、@Offset、および @Bytes は SqlCommand の入力パラメーターとして指定されています。

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

オフセット値はメモリバッファーのサイズによって決まります。メモリバッファーのサイズは、アプリケーション側の条件に基づいて、プログラマが決定します。バッファーサイズを大きくすると、BLOB の書き込み速度は速くなりますが、使用するシステムメモリが増えます。このサンプルでは、比較的小さな 128 バイトのバッファーサイズを使用します。オフセット値は、データの最初のチャンクが 0 で、その後はチャンクごとにバッファーサイズ分がインクリメントされた値になります。

ADO.NET の更新の例

この例では、指定したファイルパスから、チャンク単位で従業員の写真を取得します。各チャンクは、指定したバッファーサイズ単位で、バイト配列に読み込まれます。次に、バイト配列は、SqlCommand の @Bytes 入力パラメーターの値として設定されます。 @Offset パラメーター値が更新され、SqlCommand が実行されて、現在のチャンクが従業員レコードの Photo フィールドに追加されます。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class EmployeeData
    Public Shared Sub Main()
        Dim hireDate As DateTime = DateTime.Parse("4/27/98")
        Dim newID As Integer = _
            AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp")
        Console.WriteLine("New Employee added. EmployeeID = " & newID)
    End Sub

    Public Shared Function AddEmployee(ByVal lastName As String, _
        ByVal firstName As String, ByVal title As String, ByVal hireDate As DateTime, _
        ByVal reportsTo As Integer, ByVal photoFilePath As String) As Integer

        Using connection As SqlConnection = New SqlConnection( _
        "Data Source=(local);Integrated Security=true;Initial Catalog=Northwind;")

            Dim addEmp As SqlCommand = New SqlCommand( _
                "INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " & _
            "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" & _
            "SELECT @Identity = SCOPE_IDENTITY();" & _
            "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", _
            connection)

            addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName
            addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName
            addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title
            addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate
            addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo

            Dim idParm As SqlParameter = addEmp.Parameters.Add("@Identity", SqlDbType.Int)
            idParm.Direction = ParameterDirection.Output
            Dim ptrParm As SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
            ptrParm.Direction = ParameterDirection.Output

            connection.Open()

            addEmp.ExecuteNonQuery()

            Dim newEmpID As Integer = CType(idParm.Value, Integer)

            StorePhoto(photoFilePath, ptrParm.Value, connection)

            Return newEmpID
        End Using
    End Function

    Public Shared Sub StorePhoto(ByVal fileName As String, _
        ByVal pointer As Byte(), ByVal connection As SqlConnection)

        ' The size of the "chunks" of the image.
        Dim bufferLen As Integer = 128

        Dim appendToPhoto As SqlCommand = New SqlCommand( _
            "UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", _
            connection)

        Dim ptrParm As SqlParameter = appendToPhoto.Parameters.Add( _
            "@Pointer", SqlDbType.Binary, 16)
        ptrParm.Value = pointer
        Dim photoParm As SqlParameter = appendToPhoto.Parameters.Add( _
            "@Bytes", SqlDbType.Image, bufferLen)
        Dim offsetParm As SqlParameter = appendToPhoto.Parameters.Add( _
            "@Offset", SqlDbType.Int)
        offsetParm.Value = 0

        '' Read the image in and write it to the database 128 (bufferLen) bytes 
        '' at a time. Tune bufferLen for best performance. Larger values 
        '' write faster, but use more system resources.
        Dim fs As FileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read)
        Dim br As BinaryReader = New BinaryReader(fs)

        Dim buffer() As Byte = br.ReadBytes(bufferLen)
        Dim offset_ctr As Integer = 0

        Do While buffer.Length > 0
            photoParm.Value = buffer
            appendToPhoto.ExecuteNonQuery()
            offset_ctr += bufferLen
            offsetParm.Value = offset_ctr
            buffer = br.ReadBytes(bufferLen)
        Loop

        br.Close()
        fs.Close()
    End Sub
End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class EmployeeData
{
    public static void Main()
    {
        DateTime hireDate = DateTime.Parse("4/27/98");
        int newID = AddEmployee("Smith", "John", "Sales Representative",
            hireDate, 5, "smith.bmp");
        Console.WriteLine("New Employee added. EmployeeID = " + newID);
    }

    public static int AddEmployee(string lastName, string firstName,
        string title, DateTime hireDate, int reportsTo, string photoFilePath)
    {
        using (SqlConnection connection = new SqlConnection(
            "Data Source=(local);Integrated Security=true;Initial Catalog=Northwind;"))
        {

            SqlCommand addEmp = new SqlCommand(
                "INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
                "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
                "SELECT @Identity = SCOPE_IDENTITY();" +
                "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", 
                connection);

            addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName;
            addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
            addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title;
            addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate;
            addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo;

            SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
            idParm.Direction = ParameterDirection.Output;
            SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
            ptrParm.Direction = ParameterDirection.Output;

            connection.Open();

            addEmp.ExecuteNonQuery();

            int newEmpID = (int)idParm.Value;

            StorePhoto(photoFilePath, (byte[])ptrParm.Value, connection);

            return newEmpID;
        }
    }

    public static void StorePhoto(string fileName, byte[] pointer, 
        SqlConnection connection)
    {
        // The size of the "chunks" of the image.
        int bufferLen = 128;  

        SqlCommand appendToPhoto = new SqlCommand(
            "UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", 
            connection);

        SqlParameter ptrParm = appendToPhoto.Parameters.Add(
            "@Pointer", SqlDbType.Binary, 16);
        ptrParm.Value = pointer;
        SqlParameter photoParm = appendToPhoto.Parameters.Add(
            "@Bytes", SqlDbType.Image, bufferLen);
        SqlParameter offsetParm = appendToPhoto.Parameters.Add(
            "@Offset", SqlDbType.Int);
        offsetParm.Value = 0;

        // Read the image in and write it to the database 128 (bufferLen) bytes at a time.
        // Tune bufferLen for best performance. Larger values write faster, but
        // use more system resources.
        FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
        BinaryReader br = new BinaryReader(fs);

        byte[] buffer = br.ReadBytes(bufferLen);
        int offset_ctr = 0;

        while (buffer.Length > 0)
        {
            photoParm.Value = buffer;
            appendToPhoto.ExecuteNonQuery();
            offset_ctr += bufferLen;
            offsetParm.Value = offset_ctr;
            buffer = br.ReadBytes(bufferLen);
        }

        br.Close();
        fs.Close();
    }
}

参照

概念

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

その他の技術情報

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

ADO.NET でのデータの取得および変更

次の方法で共有

バイナリデータでの UPDATETEXT の使用 (ADO.NET)

Transact-SQL の UPDATETEXT の例

ADO.NET の更新の例

参照

概念

その他の技術情報

その他のリソース

次の方法で共有

バイナリ データでの UPDATETEXT の使用 (ADO.NET)

Transact-SQL の UPDATETEXT の例

ADO.NET の更新の例

参照

概念

その他の技術情報

その他のリソース

バイナリデータでの UPDATETEXT の使用 (ADO.NET)