이진 데이터에 UPDATETEXT 사용(ADO.NET)
SQL Server 2005보다 이전 버전의 SQL Server에서는 BLOB(Binary Large Object)로 작업할 때 사용할 수 있는 옵션이 매우 제한됩니다. 데이터베이스의 필드 형식에 따라 필드에 문자열 값이나 바이트 배열을 삽입하거나 업데이트하여 데이터베이스에 BLOB(Binary Large Object)를 쓸 수 있습니다. 그러나 BLOB가 너무 크면 단일 값으로 쓸 때 시스템 메모리를 너무 많이 차지하여 응용 프로그램 성능이 저하될 수 있습니다.
BLOB 값을 쓸 때 사용되는 메모리 양을 줄이는 일반적인 방법은 데이터베이스에 BLOB를 "청크"로 쓰는 것입니다. 이 방식으로 데이터베이스에 BLOB를 쓰는 과정은 데이터베이스의 성능에 따라 달라집니다.
Transact-SQL UPDATETEXT 예제
다음 샘플에서는 SQL Server에 BLOB를 청크로 쓰는 방법을 보여 줍니다. 이 샘플에서는 Northwind 데이터베이스의 Employees 테이블에 BLOB인 직원 이미지를 비롯한 새 레코드를 추가합니다. 여기서는 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
Photo 필드에 초기값 0x0(null)이 삽입됩니다. 새로 삽입하는 레코드의 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();
}
}