files as image datatypes in VB and SQL

[capella07]

Hello, everyone

This is kind of a big project (what I'm posting here is only a very small part), so any suggestions on different approaches in what I need to do would be greatly appreciated!

I have a Sub in a VB.NET 2005 Windows form that, among other things, copies an email's attachment(s) to a drive folder. Then the location of the attachment is saved as text (x:\Attachments\filename.doc) in a SQL Server 2000 database table. This table is then shown in a DataGridView.

This is currently done using Access and VBA. The file is saved as an OLE image datatype, which I currently have absolutely no experience working with (but that's about to change..) in the SQL table. I'm converting this over to VB.NET and all SQL tables.

My supervisor now wants me to provide a way for the user to view that attachment (image datatype that is saved in the table) from within a Windows form.

Regardless of how this is done, the end result must be that the file is of the image data type in the table. Due to the current database structure this cannot be changed (however I will have the opportunity to change that in the future). Also the user has to be able to view the attachments from within a Windows form.

Can anyone provide any suggestions on where to start? As I said, I don't know anything about image datatypes, and the little reading I've done on them so far reveals that images are dealt with differently.

Thanks for your help!
__________________

 

[jmcilhinney]

Are you saying that the image itself is going to be stored in the database or the path of the image file? If it's the latter then you're not using an image column, you're just using a text column.

 

[capella07]

Thanks jmc,

Actually, I'm going to include both columns. Currently it has to be saved as an image due to the way it's displayed in another Access module. However, eventually I'll be able to change that to just providing a link to the file itself.

So, to answer your question, my question is about working with an image column and saving pdfs, docs, etc. as images in the column, and then displaying those in a Windows form.

Thanks!

 

[cjard]

Youre talking about a BLOB column. Binary Large OBject.
Please see the relevant documentation of whatever database system you are/not going to be using for more information on BLOBs - I cant work out from youre posts whether your on Access, SQLS, both, etc..

 

[capella07]

cjard, sorry I wasn't clear enough.

I'm creating a Windows form in VB.NET 2005 and working with a database table in SQL Server 2000.

 

[jmcilhinney]

You save images and other binary objects to an image column in SQL Server. The fields of an image column contain binary data and binary data is handled in VB using an array of Bytes. That means that you need to convert your Image to a Byte array to save it to the database:

Dim imageData As Byte()

Using imageStream As New IO.MemoryStream
    myImage.Save(imageStream, Imaging.ImageFormat.Jpeg)
    imageData = imageStream.GetBuffer()
End Using

and convert from a Byte array to an Image to retrieve it:

Dim imageStream As New IO.MemoryStream

imageStream.Write(imageData, 0, imageData.Length)
myImage = Image.FromStream(imageStream)

Note that GDI+, which handles images in .NET, will defer reading some information for an image from a stream, so depending on what you are doing you may not be able to close the stream from which you read the binary data until you are ready to destroy the Image object created from it. I have encountered this situation myself, where if I closed the stream there were certain circumstances where closing the stream after reading the image data would cause an exception to be thrown at a later point, when GDI+ tried to read the stream again. If you want to close the stream then make sure you test rigorously to ensure that you're not doing anything that will cause an exception to be thrown. If in doubt, leave the stream open until you have destroyed the Image.

 

[cjard]

Using BLOBs in SQLS2K:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx

 

[capella07]

Thanks for the explaination, jmc.

Currently my code iterates through an Oulook folder and determines if an email has an attachment (and how many). Then, if it does, it writes that email's information to the SQL table.

'Get attachment's file name to write to db table
Dim mailAtmtName As String
mailAtmtName = redempMailItem.Attachments.Item(atmtCounter).FileName
 
'Add filename to datarow
[SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE] row [COLOR=#0000ff]As[/COLOR] DataRow = EtavDataSet.Tables(0).NewRow
row.Item([COLOR=#800000]"FileName"[/COLOR]) = mailAtmtName

I determine elsewhere in the code what type of attachment it is, so I can add that to this sub if necessary (do you think that would be required - especially if I have to display it eventually?). I guess I just need to adjust your code to also write to the datarow for insertion into the table, right?

 

[jmcilhinney]

You don't need to ADJUST my code. You need to ADD the code to write the data to your DataRow or parameter. The data you assign to the DataRow field or the parameter Value is the Byte array, and that's the data you get back when you read it from the database too.