I didn't write it, and I know this is a VB.NET forum, but...
One of my coworkers wrote a dll for me to store binary data (images,
documents, etc) IN the database as an attachment. It's not that hard
to do. We wanted our users to be able to store these documents and not
have to worry about their storage on some network file server. It
works great for us. Here is the code snippet (in C#) for storing the
binary data (BLOB):
int BufferSize = 1024; // The size of the "chunks" of the image.
SqlCommand appendToBLOB = new SqlCommand("UPDATETEXT " + Table + "." +
BLOBColumn + " @BLOBPtr @Offset 0 @Bytes", DBConn);
SqlParameter ptrParm = appendToBLOB.Parameters.Add("@BLOBPtr",
SqlDbType.Binary, 16);
ptrParm.Value = BLOBDataPtr;
SqlParameter BLOBParm = appendToBLOB.Parameters.Add("@Bytes",
SqlDbType.Image, BufferSize);
SqlParameter offsetParm = appendToBLOB.Parameters.Add("@Offset",
SqlDbType.Int);
offsetParm.Value = 0;
//''''''''''''''''''''''''''''''''''
// Read the file data in and write it to the database 1024 (BufferSize)
bytes at a time.
// Tune BufferSize 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[] FileBuffer;
FileBuffer = br.ReadBytes(BufferSize);
int offset_ctr = 0;
bool FileFound = false;
while (FileBuffer.Length > 0)
{
FileFound = true;
BLOBParm.Value = FileBuffer;
appendToBLOB.ExecuteNonQuery();
offset_ctr += BufferSize;
offsetParm.Value = offset_ctr;
FileBuffer = br.ReadBytes(BufferSize);
}
br.Close();
fs.Close();
To let the user view the attachment, we read the binary data stored in
SQL to a temporary file, then ask the OS the open the file up with the
associated application. The users love it because they don't have to
worry about elaborate naming schemes for the file names, just attach it
to the appropriate record and later (years later in some cases), they
find that record and easily open the attachment.
HTH
Bill
