다음은 microsoft.public.dotnet.framework.adonet 에 올라왔던 질문과 대답입니다.
BLOB 데이터를 읽고 쓰는 것에 대한 내용을 달고 있습니다.
Here is some code that uses a buffer for reading and writing blobs to
SQL Server (only the reading code is in the docs). This was posted by
Doug Rothaus a couple of months ago.
-- Andy
From: "Doug Rothaus \(MSFT\)" <douglasr@nospam.com>
References: <0ba101c17ed1$329f4d50$a4e62ecf@tkmsftngxa06>
Subject: Re: Image Column Data In SQL Server
Date: Fri, 7 Dec 2001 13:48:01 -0800
Message-ID: <umw0rj2fBHA.848@tkmsftngp03>
Here's some sample code that is included in the release documentation
for the .NET Frameworks in a topic called "Obtaining BLOB Values from
a Database". The code reads bitmaps from the pubs database and writes
them to disk. I did a quick test on an Excel file and no problems
occurred. See if it works for your situation.
As far as writing BLOBs are concerned, there is no topic for the
release docs, but I have some working code that writes to SQL Server
2000 that you can review. I'll include it as well. Again, it's hard
coded to read a bitmap file. I tested it with Excel as well and no
problems occurred.
I gathered from your previous posts that you prefer VB.NET. Let me
know if you want the C# version.
Doug Rothaus
douglasr@microsoft.com
------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no
rights.
--- obtaining BLOB values ---
Dim pubsConn As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;")
Dim logoCMD As SqlCommand = New SqlCommand("SELECT pub_id, logo FROM
pub_info", pubsConn)
Dim fs As FileStream ' Writes the BLOB to a file
(*.bmp).
Dim bw As BinaryWriter ' Streams the binary data to the
FileStream object.
Dim bufferSize As Integer = 100 ' The size of the BLOB buffer.
Dim outbyte(bufferSize - 1) As Byte ' The BLOB byte() buffer to be
filled by GetBytes.
Dim retval As Long ' The bytes returned from
GetBytes.
Dim startIndex As Long = 0 ' The starting position in the
BLOB output.
Dim pub_id As String = "" ' The publisher id to use in the
file name.
' Open the connection and read data into the DataReader.
pubsConn.Open()
Dim myReader As SqlDataReader =
logoCMD.ExecuteReader(CommandBehavior.SequentialAccess)
Do While myReader.Read()
' Get the publisher id, which must occur before getting the logo.
pub_id = myReader.GetString(0)
' Create a file to hold the output.
fs = New FileStream("logo" & pub_id & ".bmp", FileMode.OpenOrCreate,
FileAccess.Write)
bw = New BinaryWriter(fs)
' Reset the starting byte for a new BLOB.
startIndex = 0
' Read bytes into outbyte() and retain the number of bytes returned.
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
' Continue reading and writing while there are bytes beyond the size
of the buffer.
Do While retval = bufferSize
bw.Write(outbyte)
bw.Flush()
' Reposition the start index to the end of the last buffer and
fill the buffer.
startIndex = startIndex + bufferSize
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
Loop
' Write the remaining buffer.
bw.Write(outbyte)
bw.Flush()
' Close the output file.
bw.Close()
fs.Close()
Loop
' Close the reader and the connection.
myReader.Close()
pubsConn.Close()
--- end obtaining BLOB values ---
--- writing BLOB values ---
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Public Class ChunkImage
Public Shared Sub Main()
'*
'* CREATE TABLE Images (
'* ImageID int IDENTITY (1, 1) NOT NULL ,
'* Picture image NULL
'* ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
'* GO
'*
'* ALTER TABLE Images WITH NOCHECK ADD
'* CONSTRAINT PK_Images PRIMARY KEY CLUSTERED
'* (
'* ImageID
'* ) ON [PRIMARY]
'* GO
'*
''''''''''''''''''''''''''''''''''''''
'' Command to insert new image record
Dim conn As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=Test;")
Dim AddImage As SqlCommand = New SqlCommand("INSERT INTO Images
(Picture) Values(0x0);" & _
"SELECT @Identity =
SCOPE_IDENTITY();" & _
"SELECT @Pointer =
TEXTPTR(Picture) FROM Images WHERE ImageID = @Identity", conn)
Dim pointer As SqlParameter = AddImage.Parameters.Add("@Pointer",
SqlDbType.Binary, 16)
pointer.Direction = ParameterDirection.Output
Dim id As SqlParameter = AddImage.Parameters.Add("@Identity",
SqlDbType.Int)
id.Direction = ParameterDirection.Output
''''''''''''''''''''''''''''''''''''''
'' Command to append image data
Dim bufferLen As Integer = 128
Dim AppendToImage As SqlCommand = New SqlCommand("UPDATETEXT
Images.Picture @Pointer @Offset 0 @Bytes", conn)
Dim ptr As SqlParameter = AppendToImage.Parameters.Add("@Pointer",
SqlDbType.Binary, 16)
Dim img_data As SqlParameter =
AppendToImage.Parameters.Add("@Bytes", SqlDbType.Binary, bufferLen)
Dim offset As SqlParameter = AppendToImage.Parameters.Add("@Offset",
SqlDbType.Int)
offset.Value = 0
'''''''''''''''''''''''''''''''''''''
'' Add Image record and retrieve image idenity and pointer
conn.Open()
AddImage.ExecuteNonQuery()
Console.WriteLine("Image added. ImageID = " & id.Value)
ptr.Value = pointer.Value 'image pointer
''''''''''''''''''''''''''''''''''''
'' Read image in and write to database 128 bytes at a time
Dim fileName As string = "logoimage.bmp"
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
img_data.Value = buffer
AppendToImage.ExecuteNonQuery()
offset_ctr += bufferLen
offset.Value = offset_ctr
buffer = br.ReadBytes(bufferLen)
Loop
conn.Close()
br.Close()
fs.Close()
End Sub
End Class
--- end writing BLOB values ---
[최초 등록일: ]
[최종 수정일: 6/30/2004]