Inserting an image into a sqlserver table and displaying it in the webpage

I have to insert a image file into sqlserver 2005 in asp.net with c sharp.
please provide me with the detailed code

Junior Poster in Training

85 posts since Aug 2009

Reputation Points: 6

Solved Threads: 0

The table:

Create Table Picture
(
  ID int identity(1000, 1) PRIMARY KEY,
  Picture image
)

Code behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;

namespace daniweb.web
{
  public partial class PagePost : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      //Getting the main page
      if (Request.QueryString["image"] == null)
      {
        Image1.ImageUrl = "PagePost.aspx?image=blah.bmp";
        //Make sure it exists before we link to it
        //This is inefficient because it calls the database twice for one image
        byte[] picture = GetImage();
        if (picture == null)
        {
          Image1.Visible = false;
        }
      }
      else
      {
        string imageStr = Convert.ToString(Request.QueryString["image"]);
        if (string.Compare(imageStr, "blah.bmp", true) == 0)
        {
          byte[] picture = GetImage();
          Response.ContentType = "image/bmp";
          Response.BinaryWrite(picture);
        }
      }
    }

    
    private static byte[] GetImage()
    {
      byte[] result = null;

      const string query = @"Select Top 1 Picture From Picture";
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            using (DataTable dt = new DataTable())
            {
              dt.Load(dr);
              if (dt.Rows.Count > 0)
              {
                DataRow row = dt.Rows[0];
                if (row["Picture"] != DBNull.Value)
                {
                  try
                  {
                    result = (byte[])row["Picture"];
                  }
                  catch { }
                }
              }
            }
          }
        }
      }
      return result;
    }
    private static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
      byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
      const string query = @"Insert Into Picture (Picture) Values (@Picture)";
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
          parm.Value = bytes;
          cmd.Parameters.Add(parm);
          cmd.ExecuteNonQuery();
        }
      }
    }
  }
}

The page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PagePost.aspx.cs" Inherits="daniweb.web.PagePost" %>

<!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>
			<asp:Image ID="Image1" runat="server" />
			<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />    
			</form>
</body>
</html>

sknake

Industrious Poster

4,954 posts since Feb 2009

Reputation Points: 1,764

Solved Threads: 735

2 Years Ago

Take a look at the PhotoHandler here: http://www.asp.net/downloads/starter-kits/personal/

achristov

Junior Poster in Training

68 posts since Mar 2008

Reputation Points: 23

Solved Threads: 6

2 Years Ago

sir i saw the code but i couldnt figure out how it works.
Please can u post comments .
I am a newbie .
One more doubt are u converting it into binary before storing into sql table . Thanks in advance

coollife

Junior Poster in Training

85 posts since Aug 2009

Reputation Points: 6

Solved Threads: 0

2 Years Ago

The File.ReadAllBytes() takes the image as binary and includes it in the database as binary.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;

namespace daniweb.web
{
  public partial class PagePost : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      //Getting the main page
      if (Request.QueryString["image"] == null)
      {
        //since the query string is null this means they did a GET /PagePost.aspx

        Image1.ImageUrl = "PagePost.aspx?image=blah.bmp";
        //Make sure it exists before we link to it
        //This is inefficient because it calls the database twice for one image
        byte[] picture = GetImage();
        if (picture == null)
        {
          //no pictures exist in the database so make the image invisible instead of displaying
          //a broken image
          Image1.Visible = false;
        }
      }
      else
      {
        //This means they did GET /PagePost.aspx?image=blah.jpg -- so instead of serving a webpage
        //we actually need to serve an image. We send a binary image down the response stream
        //instead of this pages' HTML markup
        string imageStr = Convert.ToString(Request.QueryString["image"]);
        if (string.Compare(imageStr, "blah.bmp", true) == 0)
        {
          byte[] picture = GetImage();
          Response.ContentType = "image/bmp";
          Response.BinaryWrite(picture);
        }
      }
    }

    
    private static byte[] GetImage()
    {
      byte[] result = null;

      const string query = @"Select Top 1 Picture From Picture";
      //creating a connection to the server
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
        //using our select query with an open connection to fetch an image
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            using (DataTable dt = new DataTable())
            {
              //load the query results to get at the byte[] array
              dt.Load(dr);
              if (dt.Rows.Count > 0)
              {
                DataRow row = dt.Rows[0];
                //Make sure the image isn't a NULL value in the database
                if (row["Picture"] != DBNull.Value)
                {
                  try
                  {
                    //read the binary image from the database. 
                    result = (byte[])row["Picture"];
                  }
                  catch { }
                }
              }
            }
          }
        }
      }
      return result;
    }
    private static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
      //This reads the image file as BINARY to store it in the database
      byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
      const string query = @"Insert Into Picture (Picture) Values (@Picture)";
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          //include the binary data in  the DB
          SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
          parm.Value = bytes;
          cmd.Parameters.Add(parm);
          cmd.ExecuteNonQuery();
        }
      }
    }
  }
}

sknake

Industrious Poster

4,954 posts since Feb 2009

Reputation Points: 1,764

Solved Threads: 735

2 Years Ago

u no need to store image in sql.
Just strore the URL of the image that u added in your project as just like a normal string.. it ll works

riyas_26

Light Poster

30 posts since Jul 2009

Reputation Points: 9

Solved Threads: 0

2 Years Ago

1. This is a VS template. Install it first.
2. Open VS and create a web site using the newly installed template.
3. The main folder of your new web site contains (amongst the others) the file Handler.ashx. It is responsible for handling requests for fetching images from your site. (In fact the SQL Server as an underlying image storage.)
4. In the App_Code folder you'll find the support this handler needs (and not only!) - all the image manipulation methods you do and you also might need: fetching, inserting and deleting of albums and images. It also contains a couple of helper routines you may find useful.
Look at the code carefuly, try to understand it, then modify it to suit your needs.
And as usual, "use the source, Luke!"
Good Luck.

achristov

Junior Poster in Training

68 posts since Mar 2008

Reputation Points: 23

Solved Threads: 6

2 Years Ago

Show Comments

OK, lets review the code. First I have c:\picture.bmp. It is a bitmap image.

At this point lets answer a few questions?
* Is this image in "image format"? Yes, it is a Bitmap.
* Is this image binary? Yes, bitmaps are stored as logical pixels in binary on disk.

Now we need to read the image in to C# using code.

byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");

Now we have a byte array.
* Is this byte array binary? Yes it is
* Is this byte array in "image format"? Yes, binary image format.

OK, now at this point we have an image as binary. Lets stuff it in the SQL Server!

{
//our much discussed binary data
      byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
//out query to insert the data
      const string query = @"Insert Into Picture (Picture) Values (@Picture)";
//lets open a connection to the sql server
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
//next we need a command to fire off the insert
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
//next we need a "binary sql" variable. You can't very well embed binary in a plaintext TSQL query. So lets use parameters.
          SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
//Give the parameter the binary "image format" byte array.
          parm.Value = bytes;
          cmd.Parameters.Add(parm);
//go go gadget
          cmd.ExecuteNonQuery();
        }
      }
    }

OK at this point if you open notepad.exe and do a "File -- Open" on c:\picture.bmp you will see the exact same data as you do in the SQL Server.

At this point lets review where we are:
* Is the image inside of the sql server binary? Yes. It is in the binary "image format", the exact same as you would see on your harddrive.

So now we need to get the image out so we can display it!

private static byte[] GetImage()
    {
//this is a place holder for the binary image so we can convert it to an image
      byte[] result = null;
//we need a query to fetch the image from the database
      const string query = @"Select Top 1 Picture From Picture";
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
//need a select command
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
//datareader to load the results of the select
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
//load in to a datatable so its easy to work with. You could call dr.Read() if you want to.
            using (DataTable dt = new DataTable())
            {
              dt.Load(dr);
//make sure the query returned at least one result
              if (dt.Rows.Count > 0)
              {
                DataRow row = dt.Rows[0];
//make sure the value isn't "null" inside the database
                if (row["Picture"] != DBNull.Value)
                {
                  try
                  {
//Moves the binary image from the SQL Server to our local byte array. 
                    result = (byte[])row["Picture"];
//at this point we have the binary image in memory
                  }
                  catch { }
                }
              }
            }
          }
        }
      }
      return result;
    }

Now we have the binary image in memory and you want to display it on the web page. You have a few ways to go about this. What I chose to do is write the image back down the response stream:

//get our binary image
          byte[] picture = GetImage();
          Response.ContentType = "image/bmp";
//send the binary image down stream
          Response.BinaryWrite(picture);

I have a feeling you're concerned that image was never saved to the hard drive as binary. So here is how you would do that:

byte[] picture = GetImage();
      Image img;
      using (System.IO.MemoryStream ms = new System.IO.MemoryStream(picture))
      {
        ms.Position = 0;
        ms.Seek(0, System.IO.SeekOrigin.Begin);
        img = Image.FromStream(ms);
      }
      img.Save("C:\whatever.bmp");

Now you could link to this image on your website. When the webserver gets a request a for an image it reads the image in to a byte[] array and sends it down stream. There is no reason to write the image to disk in what you asked.

sknake

Industrious Poster

4,954 posts since Feb 2009

Reputation Points: 1,764

Solved Threads: 735

2 Years Ago

Yeah, just create a directory called 'images' and store a string in the database which would be the path to the image. On the save command then upload the image to the directory. Probably should append a datetime to the filename so there isnt a chance of duplicate filenames.

textbox

Light Poster

38 posts since Jul 2009

Reputation Points: 10

Solved Threads: 2

2 Years Ago

Show Comments

-1

thanks sknake,its a v good reply for the asked help.i also read it & found useful.but what i've problem is i want to first show the image name that is stored in database, and when user clicks in the image name,it would be opened in new web page.actually i am doing with pdf files.with same way i could upload pdf files in db too.but don't have idea to display it. when user clicks on the file name, pdf file should open in browser.do u know how to do so.i'm new to web development ,seek help to do so. how can we pass image id while opening in new page.
thanks anyway.

OK, lets review the code. First I have c:\picture.bmp. It is a bitmap image.

At this point lets answer a few questions? * Is this image in "image format"? Yes, it is a Bitmap. * Is this image binary? Yes, bitmaps are stored as logical pixels in binary on disk.

Now we need to read the image in to C# using code.
byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
Now we have a byte array. * Is this byte array binary? Yes it is * Is this byte array in "image format"? Yes, binary image format.

OK, now at this point we have an image as binary. Lets stuff it in the SQL Server!
{
//our much discussed binary data
      byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
//out query to insert the data
      const string query = @"Insert Into Picture (Picture) Values (@Picture)";
//lets open a connection to the sql server
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
//next we need a command to fire off the insert
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
//next we need a "binary sql" variable. You can't very well embed binary in a plaintext TSQL query. So lets use parameters.
          SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
//Give the parameter the binary "image format" byte array.
          parm.Value = bytes;
          cmd.Parameters.Add(parm);
//go go gadget
          cmd.ExecuteNonQuery();
        }
      }
    }
OK at this point if you open notepad.exe and do a "File -- Open" on c:\picture.bmp you will see the exact same data as you do in the SQL Server.

At this point lets review where we are: * Is the image inside of the sql server binary? Yes. It is in the binary "image format", the exact same as you would see on your harddrive.

So now we need to get the image out so we can display it!
private static byte[] GetImage()
    {
//this is a place holder for the binary image so we can convert it to an image
      byte[] result = null;
//we need a query to fetch the image from the database
      const string query = @"Select Top 1 Picture From Picture";
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
//need a select command
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
//datareader to load the results of the select
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
//load in to a datatable so its easy to work with. You could call dr.Read() if you want to.
            using (DataTable dt = new DataTable())
            {
              dt.Load(dr);
//make sure the query returned at least one result
              if (dt.Rows.Count > 0)
              {
                DataRow row = dt.Rows[0];
//make sure the value isn't "null" inside the database
                if (row["Picture"] != DBNull.Value)
                {
                  try
                  {
//Moves the binary image from the SQL Server to our local byte array. 
                    result = (byte[])row["Picture"];
//at this point we have the binary image in memory
                  }
                  catch { }
                }
              }
            }
          }
        }
      }
      return result;
    }
Now we have the binary image in memory and you want to display it on the web page. You have a few ways to go about this. What I chose to do is write the image back down the response stream:
//get our binary image
          byte[] picture = GetImage();
          Response.ContentType = "image/bmp";
//send the binary image down stream
          Response.BinaryWrite(picture);
I have a feeling you're concerned that image was never saved to the hard drive as binary. So here is how you would do that:
byte[] picture = GetImage();
      Image img;
      using (System.IO.MemoryStream ms = new System.IO.MemoryStream(picture))
      {
        ms.Position = 0;
        ms.Seek(0, System.IO.SeekOrigin.Begin);
        img = Image.FromStream(ms);
      }
      img.Save("C:\whatever.bmp");
Now you could link to this image on your website. When the webserver gets a request a for an image it reads the image in to a byte[] array and sends it down stream. There is no reason to write the image to disk in what you asked.

deepas

Newbie Poster

16 posts since Sep 2008

Reputation Points: 10

Solved Threads: 0

2 Years Ago

deepas: You should create new threads for asking your own questions. The code provided serves as an example to get binary from the database down to a web browser.

Regarding having the PDF display instead of download -- are you wanting to use a browser plugin to have the PDF rendered inside part of your page or are you wanting to open a new tab that shows only the PDF with the adobe plugin? If you want to show the document in its' own tab then use the HTTP header's content-disposition to control the browser behavior for known MIME types:

using (RptFullPage rpt = new RptFullPage(acct, prof))
        {
          rpt.CreateDocument();
          rpt.ExportOptions.Pdf.DocumentOptions.Application = rptName;
          rpt.ExportOptions.Pdf.DocumentOptions.Author = rptName;
          rpt.ExportOptions.Pdf.DocumentOptions.Keywords = rptName;
          rpt.ExportOptions.Pdf.DocumentOptions.Subject = rptName;
          rpt.ExportOptions.Pdf.DocumentOptions.Title = rptName;
          using (MemoryStream ms = new MemoryStream())
          {
            rpt.ExportToPdf(ms);
            ms.Position = 0;
            ms.Seek(0, SeekOrigin.Begin);
            Response.ContentType = "application/pdf";
            Response.AddHeader("Content-Disposition", "inline; filename = " + FileName);
            Response.AddHeader("Content-Length", ms.Length.ToString());
            Response.BinaryWrite(ms.ToArray());
            Response.Flush();
          }
        }

sknake

Industrious Poster

4,954 posts since Feb 2009

Reputation Points: 1,764

Solved Threads: 735

2 Years Ago

thanks sknake,its a v good reply for the asked help.i also read it & found useful.but what i've problem is i want to first show the image name that is stored in database, and when user clicks in the image name,it would be opened in new web page.actually i am doing with pdf files.with same way i could upload pdf files in db too.but don't have idea to display it. when user clicks on the file name, pdf file should open in browser.do u know how to do so.i'm new to web development ,seek help to do so. how can we pass image id while opening in new page. thanks anyway.

Please do not hijack old post with your own question but rather create new thread for your own question.
Unfortunately in this case I cannot move your post because it is dependant on earlier reply to original question therefore I have to close this thread.

peter_budo

Code tags enforcer

Moderator

15,436 posts since Dec 2004

Reputation Points: 2,806

Solved Threads: 902

This article has been dead for over three months

You