Server Intellect
 
Home > Articles > ASP.NET Articles > How to store and retrieve images from SQL server database using asp.net?

How to store and retrieve images from SQL server database using asp.net?

by Thamilselvan Jagadeesan   on Aug 09, 2013   Category: ASP.NET  | Level: Intermediate  |  Views: 91282    |  Points: 100   
Like this article? Bookmark and Share:
In this article, I will show you how to store images into sql server table and display in gridview control in the page using asp.net and sql server stored procedure.




Introduction

In general, we store images into application root folder and save the image path into database table but here I’m going to show how to store images into sql server database table and display in the page using asp.net and sql server stored procedure. For this sample project I’m using visual studio 2008 and sql server 2008. Follow the below steps to achieve this functionality.

Prepare Database Table and store procedure:


First design the database table and stored procedure to store and retrieve image information from database table. Execute the below table and store procedure script into your test database before design your asp.net page.

CREATE TABLE [dbo].[tblUploadedImagedetails](
 [ImageID] [int] IDENTITY(1,1) NOT NULL,
 [ImageName] [varchar](100) NOT NULL,
 [ImageContent] [image] NOT NULL,
 [Createdby] [varchar](100) NOT NULL,
 [CreatedDt] [datetime] NOT NULL,
 [Updatedby] [varchar](100) NULL,
 [UpdatedDt] [datetime] NULL,
 [Active] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




Below sql server stored procedure will be used to insert the images details, select all images from database and select specific image from table based on image id.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_ImageUpload]
(       
@pvchImageId   int  =0,       
@pvchImageName  varchar(100)=null,       
@pvchImage   image=null,       
@pvchCreatedBy  varchar(100)=null,       
@pvchAction   varchar(50)=null,       
@pIntErrDescOut  int output
)       
AS       
BEGIN       
  if(@pvchAction='select' and @pvchImageId=0)    
  begin
   select ROW_NUMBER()OVER (ORDER BY ImageID) as rowid ,ImageID, ImageName, ImageContent from tblUploadedImagedetails
   ;
  end
   if(@pvchAction='select' and @pvchImageId!=0)    
  begin
   select ROW_NUMBER()OVER (ORDER BY ImageID) as rowid ,ImageID, ImageName, ImageContent from tblUploadedImagedetails
   where ImageID = @pvchImageId;
  end
  else if(@pvchAction='insert')
  begin
 
  INSERT INTO tblUploadedImagedetails(ImageName,ImageContent,Createdby,CreatedDt,active)
  VALUES(@pvchImageName,@pvchImage,@pvchCreatedBy,GETDATE(),1);
  end
    IF (@@ERROR <> 0)        
   BEGIN        
  SET @pIntErrDescOut = 1       
   END       
  ELSE       
   BEGIN       
  SET @pIntErrDescOut = 0       
   END   
END

 

ASP.NET project


   Create an asp.net project and drag the file upload to upload images and gridvew control to display the stored images into the aspx page. Take the below code and place into your aspx page.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="SaveImagesToDB._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2 style="color: #0066FF; font-weight: bold;">
            <u>Save Image to SQL server</u></h2>
    </div>
    <div>
        <asp:FileUpload ID="ImageUploadToDB" Width="300px" runat="server" />
        <asp:Button ID="btnUploadImage" runat="server" Text="Save Image to DB" OnClick="btnUploadImage_Click"
            ValidationGroup="vg" /><br />
        <br />
        <asp:Label ID="lblMsg" runat="server" ForeColor="Green" Text=""></asp:Label>
        <h2 style="text-decoration: underline; font-weight: bold; color: #0066FF;">
            Image list Details
        </h2>
        <asp:GridView ID="GridViewUploadedImageFile" runat="server" EmptyDataText="No files found!"
            AutoGenerateColumns="False" Font-Names="Verdana" AllowPaging="true" PageSize="5"
            Width="40%" OnPageIndexChanging="GridViewUploadedImageFile_PageIndexChanging"
            BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" OnRowDataBound="GridViewUploadedImageFile_RowDataBound"
            DataKeyNames="ImageID,ImageContent">
            <AlternatingRowStyle BackColor="#FFD4BA" />
            <HeaderStyle Height="30px" BackColor="#FF9E66" Font-Size="15px" BorderColor="#CCCCCC"
                BorderStyle="Solid" BorderWidth="1px" />
            <RowStyle Height="20px" Font-Size="13px" HorizontalAlign="Center" BorderColor="#CCCCCC" BorderStyle="Solid"
                BorderWidth="1px" />
            <Columns>
                <asp:BoundField DataField="rowid" HeaderText="#" HeaderStyle-Width="10%" />
                <asp:BoundField DataField="ImageID" HeaderText="#" Visible="false" HeaderStyle-Width="10%" />              
                <asp:BoundField DataField="ImageName" HeaderText="Image Name" HeaderStyle-Width="25%" />
                <asp:TemplateField HeaderText="List of Images" HeaderStyle-Width="40%">
                    <ItemTemplate>
                        <asp:Image ID="UsrImages" runat="server" Height="35px" Width="35px" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

 

Namespace Used:

using System;
using System.Web;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Configuration;

Store image to SQL server table :

In code behind part, place the below code in upload button click event and this will store the uploaded image into database table. First we need to verify whether the image is selected or not when clicks on the btnUploadImage button and convert the image into byte array then store into database table. If image is selected then gets the image name using GetFileName method to store the image name into database table. Then get the size of an uploaded image file and store into byte array and using httppostedfile InputStream read method read the file into the byte array and store into database table. Below I’m using sql server stored procedure SP_ImageUpload to insert the images details into table. Once uploaded images saved into table then display the image into page using LoadImages()method.

protected void btnUploadImage_Click(object sender, EventArgs e)
        {
            string ImageName = string.Empty;
            byte[] Image = null;
            if (ImageUploadToDB.PostedFile != null && ImageUploadToDB.PostedFile.FileName != "")
            {
                ImageName = Path.GetFileName(ImageUploadToDB.FileName);
                Image = new byte[ImageUploadToDB.PostedFile.ContentLength];
                HttpPostedFile UploadedImage = ImageUploadToDB.PostedFile;
                UploadedImage.InputStream.Read(Image, 0, (int)ImageUploadToDB.PostedFile.ContentLength);
            }
            using (SqlConnection Sqlcon = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    Sqlcon.Open();
                    cmd.Connection = Sqlcon;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "SP_ImageUpload";
                    cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
                    cmd.Parameters.Add(new SqlParameter("@pvchImageName", SqlDbType.VarChar, 100));
                    cmd.Parameters.Add(new SqlParameter("@pvchImage", SqlDbType.Image));
                    cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 100));
                    cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cmd.Parameters["@pvchAction"].Value     = "insert";
                    cmd.Parameters["@pvchImageName"].Value  = ImageName;
                    cmd.Parameters["@pvchImage"].Value      = Image;
                    cmd.Parameters["@pvchCreatedBy"].Value  = "Admin";
                    cmd.ExecuteNonQuery();
                    int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;
                }
            }
            LoadImages();
        }

 

Retreive image from SQL server table:

Once uploaded images inserted into table then need to display in the aspx page using gridview control. Below LoadImages() method will be used to display the images in gridview control. Here I’m using stored procedure to get all image record from database. To display this uploaded images we need to use http handler, so right click on project and click Add new item then select Generic handler and changed the name as DisplayImage.ashx then place the below code in httphandler class and using this handler we get the specific image from database and convert as memorystream object then create an image from memory stream and save this image in specific stream and specific format.

string strCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlDataAdapter SqlAda;
        DataSet ds;
protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadImages();
            }
        }
        private void LoadImages()
        {
            using (SqlConnection Sqlcon = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    Sqlcon.Open();
                    cmd.Connection = Sqlcon;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "SP_ImageUpload";
                    cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
                    cmd.Parameters["@pvchAction"].Value = "select";
                    cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
                    SqlAda = new SqlDataAdapter(cmd);
                    ds = new DataSet();
                    SqlAda.Fill(ds);
                    GridViewUploadedImageFile.DataSource = ds;
                    GridViewUploadedImageFile.DataBind();
                }
            }
        }
protected void GridViewUploadedImageFile_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                string ImageID = GridViewUploadedImageFile.DataKeys[e.Row.RowIndex].Values[0].ToString();
                System.Web.UI.WebControls.Image UsrImages = (System.Web.UI.WebControls.Image)e.Row.FindControl("UsrImages");
                UsrImages.ImageUrl = "DisplayImage.ashx?ImgId=" + ImageID;
            }
        }

In the above gridview rowdatabound event, pass the image id to httphandler to display the image from database.

DisplayImage Handler:

public class DisplayImage : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.Clear();
            context.Response.ContentType = "image/jpeg";           
if (context.Request.QueryString["ImgId"] != null)
            {
                int imgId = 0;
                imgId = Convert.ToInt16(context.Request.QueryString["imgId"]);
                MemoryStream memoryStream = new MemoryStream(GetImageFromDB(imgId), false);
                System.Drawing.Image imgFromDataBase = System.Drawing.Image.FromStream(memoryStream);
imgFromDataBase.Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);            }
        }
        private byte[] GetImageFromDB(int ImgId)
        {
            string strCon   = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlDataAdapter SqlAda;
            DataSet ds;
            byte[] btImage  = null;
            using (SqlConnection Sqlcon = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    Sqlcon.Open();
                    cmd.Connection = Sqlcon;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "SP_ImageUpload";
                    cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
                    cmd.Parameters.Add(new SqlParameter("@pvchImageId", SqlDbType.Int));
                    cmd.Parameters["@pvchAction"].Value = "select";
                    cmd.Parameters["@pvchImageId"].Value = ImgId;
                    cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
                    SqlAda = new SqlDataAdapter(cmd);
                    ds = new DataSet();
                    SqlAda.Fill(ds);
                    btImage = (byte[])ds.Tables[0].Rows[0][3];
               }
            }
            return btImage;
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

See the below screen shot, after inserting the images into table, gridview displays the list of saved images in the page.

I have attached the sample project and database script here, download and see how it works, thanks for readung and I hope you enjoyed this article. Please provide your feedback and suggestions on this.

Like this article? Bookmark and Share:

Most viewed Articles

User Comments


  Re :How to store and retrieve images from SQL server database using asp.net?   
Posted by angappan
on 12/6/2013 9:46:48 AM
Points : 10

hi Dear,

When i use this code. i got one error. that is

"Parameter is not a valid"

i got this Error for "when we convert memorystream to image" in ashx file.

for this lines

MemoryStream memoryStream = new MemoryStream(GetImageFromDB(imgId), false);
System.Drawing.Image imgFromDataBase = System.Drawing.Image.FromStream(memoryStream);

pls tell how to solve this.

Thanks and Regards
Angappan.S
  Re :How to store and retrieve images from SQL server database using asp.net?   
Posted by Thamilselvan Jagadeesan
on 12/23/2013 8:48:41 AM
Points : 10

Please make sure that you are passing valid parameter to GetImageFromDB function.
  Re :How to store and retrieve images from SQL server database using asp.net?   
Posted by angappan
on 12/26/2013 10:15:41 AM
Points : 10

Hi Dear Tamil selvan,


Yes I passed correct parameter. but still i am not getting result.
showing same error.

Thanks and regards
Angappan.S
  Re :How to store and retrieve images from SQL server database using asp.net?   
Posted by Gopalakrishnan Venkatachalam
on 1/20/2014 3:16:49 AM
Points : 10

DisplayImage Handler:

"Parameter is not a valid"

i got this Error for "when we convert memorystream to image" in ashx file.

for this lines

MemoryStream memoryStream = new MemoryStream(GetImageFromDB(imgId), false);
System.Drawing.Image imgFromDataBase = System.Drawing.Image.FromStream(memoryStream);
  Re :How to store and retrieve images from SQL server database using asp.net?   
Posted by Muhammad Waqas
on 3/28/2014 2:57:59 AM
Points : 10

How to update and delete each row of the above gridview? plz provide functionality....
  Re :How to store and retrieve images from SQL server database using asp.net?   
Posted by manu paul
on 7/7/2014 4:15:51 AM
Points : 10

images are not displaying..... help

Submit feedback about this article

Please sign in to post feedback