Store and Retrieve Images in Sql Server ASP.NET

Intruduction

In 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:
<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>
&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID=”txtEName” runat=”server”></asp:TextBox>
        <br />
        <asp:Label ID=”lblImage” runat=”server” Text=”Employee Image”></asp:Label>
&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:FileUpload ID=”imgUpload” runat=”server” />
        <br />
        <br />
        <asp:Button ID=”btnSubmit” runat=”server” onclick=”btnSubmit_Click”
            Text=”Submit” />
   
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp        <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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s