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
conntotal.ConnectionString = "Data Source=sql; Initial Catalog=database; _
User Id=user; Password=password;"
conntotal.Open()
cmdtotal = conntotal.CreateCommand()
cmdtotal.CommandText = "SELECT id FROM table WHERE image is not null"
readertotal = cmdtotal.ExecuteReader
Do Until readertotal.Read = False
photo = readertotal.GetInt32(0)
Resizer(photo)
Loop
readertotal.Close()
conntotal.Close()
cmdtotal.Dispose()
conntotal.Dispose()
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
conn.ConnectionString = "Data Source=sql; Initial Catalog=database; _
User Id=user; Password=password;"
conn.Open()
cmd = conn.CreateCommand()
cmd.CommandText = "SELECT image FROM table WHERE id =" & photo
reader = cmd.ExecuteReader
If reader.Read Then
Dim imgByteArray() As Byte
Try
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.
Dim Width As Integer = bmp.Width
Dim Height As Integer = bmp.Height
Dim Heightmax As Integer = 173
Dim Widthmax As Integer = 173
Dim Factorlimit As Decimal = 1
Dim Relative As Decimal = Height / Width
Dim Factor As Decimal
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 Factor < Factorlimit Then
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
g.DrawImage(bmp, 0, 0, bmpnew.Width, bmpnew.Height)
reader.Close()
Next, we store the resized image back into the database:
Dim cmdstore As SqlClient.SqlCommand
cmdstore = conn.CreateCommand()
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)
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.
Catch ex As Exception
Console.WriteLine("{0} ", photo & ": " & ex.Message)
End Try
End If
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