달력

32024  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

'sqlserver'에 해당되는 글 1건

  1. 2007.02.15 [MSDN 펌] SQL Server에 BLOB 값을 쓸 때 리소스 절약
출처 : http://msdn2.microsoft.com/ko-kr/library/3517w44b(VS.80).aspx


.NET Framework 개발자 가이드 
SQL Server에 BLOB 값을 쓸 때 리소스 절약 

데이터베이스의 필드 형식에 따라 필드에 문자열 값이나 바이트 배열을 삽입하거나 업데이트하여 데이터베이스에 BLOB(Binary Large Object)를 쓸 수 있습니다(데이터 소스에 BLOB 값 쓰기 참조). 그러나 BLOB가 너무 크면 단일 값으로 쓸 때 시스템 메모리를 너무 많이 차지하여 응용 프로그램 성능이 낮아질 수 있습니다.

BLOB 값을 쓸 때 사용되는 메모리 양을 줄이는 일반적인 방법은 데이터베이스에 BLOB를 "청크"로 쓰는 것입니다. 이 방식으로 데이터베이스에 BLOB를 쓰는 과정은 데이터베이스의 성능에 따라 달라집니다.

다음 샘플에서는 SQL Server에 BLOB를 청크로 쓰는 방법을 보여 줍니다. 이 샘플에서는 Northwind 데이터베이스의 Employees 테이블에 BLOB인 직원 이미지를 비롯한 새 레코드를 추가합니다. 즉, SQL Server의 UPDATETEXT 함수를 사용하여 Photo 필드에 새로 추가한 직원의 이미지를 지정된 크기의 청크 단위로 씁니다.

UPDATETEXT 함수를 사용하려면 업데이트되는 BLOB 필드에 대한 포인터가 있어야 합니다. 이 샘플에서는 새 직원 레코드를 추가할 때 SQL Server TEXTPTR 함수가 호출되어 새 레코드의 Photo 필드에 대한 포인터가 반환됩니다. 반환된 포인터 값은 출력 매개 변수로 다시 전달됩니다. 이 샘플 코드에서는 이 포인터가 유지되며 데이터의 청크를 추가할 때 UPDATETEXT로 전달됩니다.

다음은 새 직원 레코드를 삽입하고 Photo 필드에 대한 포인터를 유지하는 데 사용되는 Transact-SQL의 예제입니다. 여기서 @Identity@PointerSqlCommand의 출력 매개 변수로 인식됩니다.

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,@BytesSqlCommand의 입력 매개 변수로 식별됩니다.

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

오프셋 값은 응용 프로그램의 필요에 따라 사용자가 정하는 메모리 버퍼 크기에 의해 결정됩니다. 버퍼 크기가 크면 BLOB 쓰기 속도는 빠르지만 시스템 메모리가 더 많이 사용됩니다. 이 샘플에서는 비교적 적은 128바이트의 버퍼 크기를 사용합니다. 오프셋 값은 데이터의 첫 번째 청크에 대해 0부터 시작하여 각 연속 청크에 대해 버퍼 크기만큼 늘어납니다.

이 예제의 경우 제시한 파일 경로에서 직원 사진을 청크 단위로 검색합니다. 각 청크를 지정된 버퍼 크기당 바이트 배열로 읽어옵니다. 그러면 바이트 배열은 SqlCommand@Bytes 입력 매개 변수 값으로 설정됩니다. 그런 다음 @Offset 매개 변수 값이 업데이트되고 SqlCommand가 실행되어 직원 레코드의 Photo 필드에 현재 청크 바이트가 추가됩니다.

Visual Basic
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(lastName As String, firstName As String, title As String, hireDate As DateTime, _
       reportsTo As Integer, photoFilePath As String) As Integer

    Dim connection As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;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)

    connection.Close()

    Return newEmpID
  End Function

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

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

    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)
  {
    SqlConnection connection = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;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);

    connection.Close();

    return newEmpID;
  }

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

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

참고 항목

Posted by tornado
|