C# BLOB 다루기

출처 : http://www.devpia.com/MAEUL/Contents/Detail.aspx?BoardID=6127&MAEULNO=769&no=26392&page=9

파일을 바이너리로 바로 DB에 저장하고 불러오는 데모 예제 입니다.

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
namespace BlobTest{
public class Test{
public static void Main(){
string fileName = @"D:\lee.jpg";
Blob blob = new Blob();
//blob.Insert(fileName);
blob.Select();
}
}

public class Blob{
string connectionStr = "Server=127.0.0.1;user id=아이디;password=패스워드";
SqlConnection conn;
FileStream fs;
BinaryReader br;
BinaryWriter bw;

private byte[] GetImageByte(string filePath){
fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
br = new BinaryReader(fs);

byte[] imageBytes = br.ReadBytes((int)fs.Length);

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

return imageBytes;
}

public void Insert(string fileName){
conn = new SqlConnection(connectionStr);
byte[] imageBytes = this.GetImageByte(fileName);
string sql = "insert into Tbl_BLOB_TEST(Image) values(@Image)";
SqlCommand myCommand = new SqlCommand(sql,conn);
SqlParameter parm = new SqlParameter("@Image",SqlDbType.Image, imageBytes.Length);
parm.Value = imageBytes;
myCommand.Parameters.Add(parm);

conn.Open();
myCommand.ExecuteNonQuery();
conn.Close();
}

public void Select(){
int bufferSize = 100; // Size of the BLOB buffer.
byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.
long retval; // The bytes returned from GetBytes.
long startIndex = 0; // The starting position in the BLOB output.

conn = new SqlConnection(connectionStr);
conn.Open();
SqlCommand myCommand = new SqlCommand("SELECT Image FROM Tbl_BLOB_TEST", conn);
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())
{
// Create a file to hold the output.
fs = new FileStream(@"D:\downLoad.jpg", FileMode.OpenOrCreate, FileAccess.Write);
bw = new BinaryWriter(fs);

// Reset the starting byte for the new BLOB.
startIndex = 0;

// Read the bytes into outbyte[] and retain the number of bytes returned.
retval = myReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);

// Continue reading and writing while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
bw.Write(outbyte);
bw.Flush();

// Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize;
retval = myReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);
}
// Write the remaining buffer.
bw.Write(outbyte, 0, (int)retval - 1);
bw.Flush();

// Close the output file.
bw.Close();
fs.Close();
}

// Close the reader and the connection.
myReader.Close();
conn.Close();

}
}
}