IntruductionIn this article i am going to write that how to store and retrieve images in our Sql Server Database.
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 id="form1" runat="server">
<div>
<asp:Label ID="lblEmpName" runat="server" Text="Employee Name"></asp:Label>
<asp:TextBox ID="txtEName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="lblImage" runat="server" Text="Employee Image"></asp:Label>
<asp:FileUpload ID="imgUpload" runat="server" />
<br />
<br />
<asp:Button ID="btnSubmit" runat="server" onclick="btnSubmit_Click"
Text="Submit" />
  <asp:Label ID="lblResult" runat="server" ForeColor="#0066FF"></asp:Label>
<br />
<hr />
<asp:Image ID="Image1" style="width:200px" 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, i have created a byte array equal to the length of the file. The byte array will store the image. 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.
I hope this article was useful and I thank you for viewing it.If you like this post , i request you to rate this article.
The Kindle Fire, Nexus 7, and Nook Tablet all retail for $199.
