Resize Images Stored in a SQL Database and Store Them Back into it

• Download project - 31.46 KB

Introduction

Recently, I've encountered a situation where many sources used a single database for storing images. The design of the database unfortunately contained the images stored as blobs inside a table, so it fell on to the external applications to make sure all image data inserted to conform to the standards. Unfortunately not all of the applications successfully did this, and the result was an unmanageable set of differently sized images. This shouldn't be a problem when internally used, but when streaming a large number of images via the webservices, it could result in extremely large amounts of data returned with all problems that presented (parsing 100MB+ of XML for instance).

To create a central solution for this, without having to resort to 'repairing' all other applications, it was decided to make a small application that could quickly check for large images, and reduce them to a manageable size.

This resulted in this small application, which can quickly grab all images, check if they need resizing based on the maximum dimensions the image should have and return the resized images to the database.

Using the Code

The code essentially consists of the routines. One that checks for all images within the table and a second one that actually resizes the found images.

The resizer follows a simple flow: First it retrieves the image form the DB, secondly it checks whether or not to resize the image and thirdly it resizes the image and puts it back into the DB.

First,, we'll take a look at the main sub which retrieves the ids of the images from the database to pass to the resizer sub.

Sub Main()
Dim photo As Int32
Dim conntotal As New SqlClient.SqlConnection
Dim cmdtotal As New SqlClient.SqlCommand
Dim readertotal As SqlClient.SqlDataReader
'connection string for the database that contains the images
conntotal.ConnectionString = "Data Source=sql; Initial Catalog=database; _
				User Id=user; Password=password;"
conntotal.Open()
cmdtotal = conntotal.CreateCommand()
'query to retrieve the rows from the table that contain images 
'(only the actual column that contains the id is retrieved for all rows 
'that contain a non-empty blob)
cmdtotal.CommandText = "SELECT id FROM table WHERE image is not null"
readertotal = cmdtotal.ExecuteReader
'for each result in the dataset, run the resizer and pass it the id of the specific image
Do Until readertotal.Read = False
photo = readertotal.GetInt32(0)
Resizer(photo)
Loop
'dispose of all used objects
readertotal.Close()
conntotal.Close()
cmdtotal.Dispose()
conntotal.Dispose()
'Report done when finished
Console.WriteLine("{0} ", "done...")
End Sub

After the above sub passes an image to the resizer sub, we start by retrieving this specific image from the database and make it into a workable bitmap:

Sub Resizer(ByVal photo As Int32)
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim reader As SqlClient.SqlDataReader
'again the connection string for the database that contains the images 
conn.ConnectionString = "Data Source=sql; Initial Catalog=database; _
	User Id=user; Password=password;"
conn.Open()
cmd = conn.CreateCommand()
'query that retrieves a single image on basis of the passed id
cmd.CommandText = "SELECT image FROM table WHERE id =" & photo
reader = cmd.ExecuteReader
If reader.Read Then
Dim imgByteArray() As Byte
'try to resize the image, else fail with error and resume to next
Try
'read the image as a stream and make a bitmap out of it
imgByteArray = CType(reader(0), Byte())
Dim stream As New MemoryStream(imgByteArray)
Dim bmp As New Bitmap(stream)
stream.Close()

After this, we're going to check if the image needs resizing. For this, we check the dimensions of the current image and see if it exceeds the configured size limits.

'start resizing the retrieved image. First the current dimensions are checked.
Dim Width As Integer = bmp.Width
Dim Height As Integer = bmp.Height
'next we declare the maximum size of the resized image. 
'In this case, all resized images need to be constrained to a 173x173 square.
Dim Heightmax As Integer = 173
Dim Widthmax As Integer = 173
'declare the minimum value af the resizing factor before proceeding. 
'All images with a lower factor than this will actually be resized
Dim Factorlimit As Decimal = 1
'determine if it is a portrait or landscape image
Dim Relative As Decimal = Height / Width
Dim Factor As Decimal
'if the image is a portrait image, calculate the resizing factor based on its height. 
'else the image is a landscape image, 
'and we calculate the resizing factor based on its width.
If relative > 1 Then
If Height < (Heightmax + 1) Then
factor = 1
Else
factor = Heightmax / Height
End If
'
Else
If Width < (Widthmax + 1) Then
Factor = 1
Else
Factor = Widthmax / Width
End If
End If

If the image exceeds our limits, start resizing:

'if the resizing factor is lower than the set limit, start processing the image, 
'else proceed to the next image
If Factor < Factorlimit Then
'draw a new image with the dimensions that result from the resizing
Dim bmpnew As New Bitmap(bmp.Width * Factor, bmp.Height * Factor, _
	Imaging.PixelFormat.Format24bppRgb)
Dim g As Graphics = Graphics.FromImage(bmpnew)
g.InterpolationMode = Drawing.Drawing2D.InterpolationMode.HighQualityBicubic
'and paste the resized image into it
g.DrawImage(bmp, 0, 0, bmpnew.Width, bmpnew.Height)
reader.Close()

Next, we store the resized image back into the database:

'Now we can store the image back into the table
Dim cmdstore As SqlClient.SqlCommand
cmdstore = conn.CreateCommand()
'run an update query to set the image back to its original tablerow. 
'Effectively reversing the retrieval mechanism, using the image stream 
'as a variable in the query.
cmdstore.CommandText = "Update table SET image=@image WHERE id=" & photo
Dim streamstore As New MemoryStream
bmpnew.Save(streamstore, Imaging.ImageFormat.Jpeg)
imgByteArray = streamstore.ToArray()
streamstore.Close()
cmdstore.Parameters.AddWithValue("@Image", imgByteArray)
'Execute the query and report a success if succeeded, else give the error.
If DirectCast(cmdstore.ExecuteNonQuery(), Integer) > 0 Then
Console.WriteLine("{0} ", photo & " stored")
End If
End If

Now that's done. We can end the try section, throw the error if needed and close the used objects.

' if the processing fails, give the id of the image and the error
Catch ex As Exception
Console.WriteLine("{0} ", photo & ": " & ex.Message)
End Try
End If
'Close and dispose the objects used. Ready to proceed to the next image.
reader.Close()
conn.Close()
cmd.Dispose()
conn.Dispose()
End Sub

Points of Interest

The application itself isn't really complex. In my searches though, I found that this kind of thing isn't often done in VB.NET, which made the existing examples hard to read, very outdated or just plain unworkable. I used this as a basis for many image manipulations on the same DB, which made it a lot easier to keep the DB under control.

History

• 03/05/2010 - Posted first (basic) version