Save and Retrieve Images from the Database using ASP.NET 2.0 and ASP.NET 3.5
me point or the other, we as ASP.NET developers, have faced the requirement of reading and writing images to the database. We have seen loads of articles floating on the internet which discusses about storing and retrieving images from the database. Some of them are good. However, I have personally found that the solutions offered are those, where images are displayed in a ‘standalone fashion’; that is on a separate page containing only the image. What if we have to show an online form, with the person’s details and his photo along with it, or for that case, display the image in an ASP.NET server control along with other controls? In this article, we will explore how to store images in the database and then display those images along with the other server controls.
To keep the article simple and easy to understand, we will place only a few controls on the page. I have also not covered any validations associated with image control. In this article, we will only discuss how to read and write images into the database, and that would be the focus for this article. If you are interested in discussing validation and other stuff, I would suggest you to browse through the ASP.NET section of this website to view an article that discusses that.
So let us get started. We will first see how to upload an image and then display the uploaded image on the same page. You can extend this sample to create a photo album as well!! I assume you have some knowledge of creating ASP.NET 2.0 websites.
Let us start off by first creating a sample database and adding a table to it. We will call the database ‘Employee’ and the table will be called ‘EmpDetails’. This table will contain an image column along with some other columns. Run the following script in your SQL 2005 Query window (or server explorer) to construct the database and the table.
Database Script
CREATE DATABASE [Employee]
GO
USE [Employee]
GO
CREATE TABLE EmpDetails
(
empid int IDENTITY NOT NULL,
empname varchar(20),
empimg image
)
Step 1: Create a new asp.net website. In the code-behind, add the following namespace
C#
using System.Data.SqlClient;
VB.NET
Imports System.Data.SqlClient
Step 2: Drag and drop two label and one textbox control. Also drag drop a FileUpload control and a button control to upload the selected image on button click. As mentioned earlier, there are no validations performed. The source would look similar to the following:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Save Retrieve Images</title>
</head>
<body>
<form runat="server">
<div>
<asp:Label runat="server" Text="Employee Name"></asp:Label>
<asp:TextBox runat="server"></asp:TextBox>
<br />
<asp:Label runat="server" Text="Employee Image"></asp:Label>
<asp:FileUpload runat="server" />
<br />
<br />
<asp:Button runat="server" onclick="btnSubmit_Click"
Text="Submit" />
  <asp:Label runat="server" ForeColor="#0066FF"></asp:Label>
<br />
<hr />
<asp:Image Runat="server" />
</div>
</form>
</body>
</html>
Step 3: In the button click event, add the following code:
C#
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection connection = null;
try
{
FileUpload img = (FileUpload)imgUpload;
Byte[] imgByte = null;
if (img.HasFile && img.PostedFile != null)
{
//To create a PostedFile
HttpPostedFile File = imgUpload.PostedFile;
//Create byte Array with file len
imgByte = new Byte[File.ContentLength];
//force the control to load data in array
File.InputStream.Read(imgByte, 0, File.ContentLength);
}
// Insert the employee name and image into db
string conn = ConfigurationManager.ConnectionStrings ["EmployeeConnString"].ConnectionString;
connection = new SqlConnection(conn);
connection.Open();
string sql = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg) SELECT @@IDENTITY";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim());
cmd.Parameters.AddWithValue("@eimg", imgByte);
int id = Convert.ToInt32(cmd.ExecuteScalar());
lblResult.Text = String.Format("Employee ID is {0}", id);
}
catch
{
lblResult.Text = "There was an error";
}
finally
{
connection.Close();
}
}
VB.NET
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim connection As SqlConnection = Nothing
Try
Dim img As FileUpload = CType(imgUpload, FileUpload)
Dim imgByte As Byte() = Nothing
If img.HasFile AndAlso Not img.PostedFile Is Nothing Then
'To create a PostedFile
Dim File As HttpPostedFile = imgUpload.PostedFile
'Create byte Array with file len
imgByte = New Byte(File.ContentLength - 1) {}
'force the control to load data in array
File.InputStream.Read(imgByte, 0, File.ContentLength)
End If
' Insert the employee name and image into db
Dim conn As String = ConfigurationManager.ConnectionStrings("EmployeeConnString").ConnectionString
connection = New SqlConnection(conn)
connection.Open()
Dim sql As String = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg) SELECT @@IDENTITY"
Dim cmd As SqlCommand = New SqlCommand(sql, connection)
cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim())
cmd.Parameters.AddWithValue("@eimg", imgByte)
Dim id As Integer = Convert.ToInt32(cmd.ExecuteScalar())
lblResult.Text = String.Format("Employee ID is {0}", id)
Catch
lblResult.Text = "There was an error"
Finally
connection.Close()
End Try
End Sub
In the code above, we are creating a byte array equal to the length of the file. The byte array will store the image. We then load the image data into the array. The record containing the Employee Name and Image is then inserted into the database using the ADO.NET code. The ID inserted is returned back using the @@Identity. We will shortly use this ID and pass it as a query string parameter to the ShowImage handler. The image will then be fetched against the EmployeeID (empid).
Step 4: In order to display the image on the page, we will create an Http handler. To do so, right click project > Add New Item > Generic Handler > ShowImage.ashx. The code shown below, uses the Request.QueryString[“id”] to retrieve the EmployeeID from it. The ID is then passed to the ‘ShowEmpImage()’ method where the image is fetched from the database and returned in a MemoryStream object. We then read the stream into a byte array. Using the OutputStream.Write(), we write the sequence of bytes to the current stream and you get to see your image.
C#
<%@ WebHandler Language="C#" Class="ShowImage" %>
using System;
using System.Configuration;
using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;
public class ShowImage : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
Int32 empno;
if (context.Request.QueryString["id"] != null)
empno = Convert.ToInt32(context.Request.QueryString["id"]);
else
throw new ArgumentException("No parameter specified");
context.Response.ContentType = "image/jpeg";
Stream strm = ShowEmpImage(empno);
byte[] buffer = new byte[4096];
int byteSeq = strm.Read(buffer, 0, 4096);
while (byteSeq > 0)
{
context.Response.OutputStream.Write(buffer, 0, byteSeq);
byteSeq = strm.Read(buffer, 0, 4096);
}
//context.Response.BinaryWrite(buffer);
}
public Stream ShowEmpImage(int empno)
{
string conn = ConfigurationManager.ConnectionStrings ["EmployeeConnString"].ConnectionString;
SqlConnection connection = new SqlConnection(conn);
string sql = "SELECT empimg FROM EmpDetails WHERE empid = @ID";
SqlCommand cmd = new SqlCommand(sql,connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", empno);
connection.Open();
object img = cmd.ExecuteScalar();
try
{
return new MemoryStream((byte[])img);
}
catch
{
return null;
}
finally
{
connection.Close();
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
VB.NET
<%@ WebHandler Language="vb" Class="ShowImage" %>
Imports System
Imports System.Configuration
Imports System.Web
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Class ShowImage
Implements IHttpHandler
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Dim empno As Int32
If Not context.Request.QueryString("id") Is Nothing Then
empno = Convert.ToInt32(context.Request.QueryString("id"))
Else
Throw New ArgumentException("No parameter specified")
End If
context.Response.ContentType = "image/jpeg"
Dim strm As Stream = ShowEmpImage(empno)
Dim buffer As Byte() = New Byte(4095) {}
Dim byteSeq As Integer = strm.Read(buffer, 0, 4096)
Do While byteSeq > 0
context.Response.OutputStream.Write(buffer, 0, byteSeq)
byteSeq = strm.Read(buffer, 0, 4096)
Loop
'context.Response.BinaryWrite(buffer);
End Sub
Public Function ShowEmpImage(ByVal empno As Integer) As Stream
Dim conn As String = ConfigurationManager.ConnectionStrings("EmployeeConnString").ConnectionString
Dim connection As SqlConnection = New SqlConnection(conn)
Dim sql As String = "SELECT empimg FROM EmpDetails WHERE empid = @ID"
Dim cmd As SqlCommand = New SqlCommand(sql, connection)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@ID", empno)
connection.Open()
Dim img As Object = cmd.ExecuteScalar()
Try
Return New MemoryStream(CType(img, Byte()))
Catch
Return Nothing
Finally
connection.Close()
End Try
End Function
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
Step 5: One final step. Add the following code in the button click (just above the catch block) to call the handler and display the newly inserted image from the database. In the code below, we pass the EmployeeID as a query string parameter to the Http Handler.
C#
// Display the image from the database
Image1.ImageUrl = "~/ShowImage.ashx?id=" + id;
VB.NET
' Display the image from the database
Image1.ImageUrl = "~/ShowImage.ashx?id=" & id
That’s it. Run the code and check out the functionality. Just change the connection string in the web.config to point to your database. The code works fine for .jpg, .gif and .bmp images. I would encourage you to extend the sample and include validations in it. Some validations could be to check the size of the image uploaded, make sure that only images are uploaded, check the length of the Employee name, prevent the user from entering numeric and special characters, so on and so forth.
The source code of this article, both in C# and VB.NET, can be downloaded from here.
- 上一篇 »报错--->java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delect from testd
- 下一篇 »数据库连接问题之：Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!