Storing image in database and retriving image from database
Here is the code for storing image in database (ms sql server 2005):
- Default.aspx
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Upload" />
- Default.aspx.vb
Imports System.Data.SqlClient
Partial Class _DefaultInherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click' getting uploaded file name extension to lower case letters.
Dim extension As String = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower()Dim MIMEType As String = String.Empty
' checking the extension for allowed file types for upload (only image files)
Select Case extensionCase ".gif"
MIMEType = "image/gif"
Case ".jpg", ".jpeg", ".jpe"
MIMEType = "image/jpeg"
Case ".png"
MIMEType = "image/png"
Case Else
' you can set an allert to the user here like "This type of files are to allowed for uploading!"
Exit Sub
End Select
'getting connection string from web.config file and opening sql connection
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("tConnectionString").ConnectionString)Dim SQL As String = "INSERT INTO [Images] ([Title], [MIMEType], [DateAdded], [Image]) VALUES (@Title, @MIMEType, @DateAdded, @ImageData)"
Dim myCommand As New SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@Title", "default")
myCommand.Parameters.AddWithValue("@MIMEType", MIMEType)myCommand.Parameters.AddWithValue("@DateAdded", DateTime.Now)
'getting file stream to byte data typeDim imageBytes(FileUpload1.PostedFile.InputStream.Length) As Byte
FileUpload1.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
'inserting the byte array into database
myCommand.Parameters.AddWithValue("@ImageData", imageBytes)myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Using
End SubEnd Class
P.S. Image and DateAdded database fields are of type image and datetime the other filed are ID int autoincrement primary key, and what has left is nvarchar(255) for exmple.
=======================================================================================================================
Reading, manipulating and rendering file from database:
- View.aspx
the query string is hardcoded for this example to value 1
<asp:Image ID="Image1" runat="server" ImageUrl="View.aspx?id=1" AlternateText="" />
- View.aspx.vb
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging
Imports System.IO
Partial Class ViewInherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim ID As Integer = Convert.ToInt32(Request.QueryString("id"))
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("tConnectionString").ConnectionString)Dim SQL As String = "SELECT [MIMEType], [Image] FROM [Images] WHERE [ID] = @ID"
Dim myCommand As New SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@ID", ID)myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader
If myReader.Read Then
Dim bit As Byte() = myReader("Image")Dim ms As New MemoryStream(bit)
Dim bitImage As Bitmap = New Bitmap(System.Drawing.Image.FromStream(ms), 300, 300)bitImage.Save(Response.OutputStream, ImageFormat.Jpeg)
End If
myReader.Close()
myConnection.Close()
End Using
End SubEnd Class