在数据库设计中，将图片以二进制大对象（BLOB）的形式存储在表中是一种常见的做法。然而，这种方式可能会导致数据管理上的困难，尤其是当需要通过Web服务传输大量图片数据时。为了解决这个问题，开发了一个小型应用程序，它可以快速检查数据库中的图片，并将其调整到合适的大小，以便更有效地管理。
这个应用程序的核心是两个主要的例程：一个用于检查表中的所有图片，另一个用于实际调整找到的图片的大小。调整大小的过程相对简单：首先从数据库中检索图片，然后检查是否需要调整大小，最后调整图片大小并将其放回数据库。
主例程负责从数据库中检索图片的ID，并将这些ID传递给调整大小的子例程。以下是VB.NET代码示例：
Public 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
' 对于数据集中的每个结果，运行调整大小的子例程并传递特定图片的ID
Do Until readertotal.Read = False
photo = readertotal.GetInt32(0)
Resizer(photo)
Loop
' 释放所有使用的对象
readertotal.Close()
conntotal.Close()
cmdtotal.Dispose()
conntotal.Dispose()
' 完成后报告
Console.WriteLine("done...")
End Sub
当主例程将图片传递给调整大小的子例程后，首先从数据库中检索这张特定的图片，并将其转换为可操作的位图：
Public 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()
' 根据传递的ID检索单个图片
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()
' 检查图片是否需要调整大小
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 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()
' 现在可以将图片存储回表中
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(photo & " stored")
End If
End If
If processing fails, give the id of the image and the error
Catch ex As Exception
Console.WriteLine(photo & ": " & ex.Message)
End Try
End If
' 关闭并释放使用的对象。准备处理下一张图片。
reader.Close()
conn.Close()
cmd.Dispose()
conn.Dispose()
End Sub