Best Practices and Patterns on Pictures and Images?

superkuton posted on Wednesday, July 29, 2009

What are the existing practices and patterns on using pictures and images in CSLA?

I need to keep a picture for my Person object.

The SQL Server has an image column, but there are recommendations now to use the varbinary(max) data type.

There are also recommendations on just storing the picture in the file system and store the file name reference in a varchar column in the sql db.

Which is better to implement in a security and performance point of view?

Thank you.

RockfordLhotka replied on Wednesday, July 29, 2009

Hopefully a SQL Server expert can answer your database related questions.

I'll point out that your property type (or at least your field type) needs to be Serializable in your business class, or your object won't work. I don't know if the Image type is Serializable, and you should check that. You might need to store the image data as a byte[] field, and convert it to an Image in the property get.

Vinodonly replied on Thursday, July 30, 2009

image type is not the recommended approach from m$, use varbinary(max)...

I'm using it for storing excel templates, doc files and zip files..

Below is the code for reading from db

fileinbytes = (byte[]) cm.ExecuteScalar();

basically this is defined as byte array like this

byte[] fileinbytes;

and your stored proc returns that varbinary field in select..

Once you have the bytes in byte array. you can write it on disk like this.

File.WriteAllBytes(FileNamewithPath, fileinbytes);

Suppose this is a excel file then you can use Process.Start to auto start/open this file..

If you want to upload this on db then do the reverse i.e.

byte[] fileinbytes = File.ReadAllBytes(FileName);

and pass it to your stored proc which accepts varbinary(max) as a parameter..

cash_pat replied on Thursday, July 30, 2009

Private Shared VisitorPhotoProperty As PropertyInfo(Of Byte()) = RegisterProperty(Of Byte())(Function(o) o.VisitorPhoto, "Visitor Photo")

Public Property VisitorPhoto() As Byte()
    Get
      Return GetProperty(VisitorPhotoProperty)
    End Get
    Set(ByVal value As Byte())
      SetProperty(VisitorPhotoProperty, value)
    End Set
End Property

Public Property VisitorPhotoImage() As Drawing.Image
    Get
      If VisitorPhoto IsNot Nothing Then
        'Convert byte array in variable to memory stream and the convert to image
        Return System.Drawing.Image.FromStream(New System.IO.MemoryStream(ReadProperty(VisitorPhotoProperty)))
      Else
        Return Nothing
      End If
    End Get
    Set(ByVal value As Drawing.Image)

      Dim tempMemStream As New System.IO.MemoryStream

      'Convert Image to MemoryStream (Format JPEG)
      value.Save(tempMemStream, System.Drawing.Imaging.ImageFormat.Jpeg)

      'Set Start position to read from
      tempMemStream.Position = 0

      'Create byte array large enough to hold stream
      Dim photoBytes(CType(tempMemStream.Length, Integer) - 1) As Byte

      'Convert memorystream to byte array
      tempMemStream.Read(photoBytes, 0, CType(tempMemStream.Length, Integer))

      'Add byte array to the member variable

      If VisitorPhoto Is Nothing OrElse Not VisitorPhoto.Equals(photoBytes) Then

        VisitorPhoto = photoBytes

        'Storing Thumbnail
        VisitorThumbImage = value.GetThumbnailImage(100, 100, Nothing, New System.IntPtr())
      End If

      'Close the Memory Stream
      tempMemStream.Close()
    End Set
End Property

For Fetching
              LoadProperty(Of Byte())(VisitorPhotoProperty, CType(dr("VisitorPhoto"), Byte()))

tmg4340 replied on Thursday, July 30, 2009

I'm not a "database expert", but I've been through this a few times. Here's what I've been told.

(By the way, these "best practices" are not CSLA-specific, but they certainly won't hurt your CSLA implementation):

1. Don't use "image" anymore - use VARBINARY(MAX). The "max var" types (VARCHAR(MAX) and VARBINARY(MAX)) are more efficient storage-wise in the database in comparison to their older counterparts (text and image, respectively).

2. Storing images in the database is a long-running run-and-gun battle. What they seem to have settled on where I've worked is a standard based on image size - if the images are fairly small, then go ahead and store them in the database. But if the images are large, store a filename in the database. There is a point where the size of the "max" fields will force SQL Server to store the column data on a separate data page, thus adding more I/O to the image retrieval. But I'm not sure what that size break is, and it may be somewhat dependent on how the database was initially built. Large images can usually be pulled faster through direct network I/O rather than as a byte stream from the database, especially when you consider the buffering techniques you may have to employ when pulling the image from the database.

3. Technically, images stored in a database are easier to secure, because there's only one place to secure them (which is already being secured). Storing images on a share somewhere means you have a separate set of security to manage. However, depending on your situation, that's not necessarily that big an issue, and it can largely be a one-time thing.

HTH

- Scott

RockfordLhotka replied on Thursday, July 30, 2009

It is my understanding that SQL Server 2008 has some new way of storing large data in actual files, but files that are managed as (and appear as part of) the database.

Basically a supported hybrid approach to provide filesystem level performance for large files, but to also provide database security, backups and overall management.

ajj3085 replied on Thursday, July 30, 2009

If that's true, that would be really awesome. I get that storing large binary data in databases can bloat the size... but the alternative (store the file path to the file) leads to everything becoming more complex; you have to backup that location in addition to your sql database, the location can easily get out of sync, you STILL need a way to get the binary data from the server to the client, which could mean a simple file share (prone to easy corruption if its writable and users find it), or reading the file into memory and storing in the BO anyway..

So if this is a new feature of 2008, it would make my day!

pondosinat replied on Thursday, July 30, 2009

I think Rocky is referring to FILESTREAM which is new to sql 2008:

http://msdn.microsoft.com/en-us/library/cc949109.aspx

We're using sql 08 but haven't tapped into this feature yet - looks perfect for your scenario...

Paul Czywczynski replied on Thursday, July 30, 2009

I thought I read somewhere that text, ntext, and image are going to be obsolete in the next major version of SQL Server (sometime after SQL 2008 R2). They recommend using varbinary instead. I am going to switch to varbinary in our next version which will only support SQL2008 but I haven't decided if I am going to use the filestream feature yet. I need to do more analysis on our customer document sizes.

http://coolthingoftheday.blogspot.com/2009/01/sql-server-varbinary-vs-filestream-code.html

-Paul