Expertize Essentials Innovation through knowledge

Saving Image in Database (Sql Server) using .NET

I shared and done few efforts to save image in database.

Following the the function to save image to the databse.
Database Table will look like this:

create table userphoto(
photoid int,
photo image
);

The following code will be written in ASPX page.
<asp:FileUpload ID="UploadPhoto" runat="server" CssClass="fileupload" />
<asp:Button ID="btnUpload" runat="server" CssClass="buttonOrangeLight" OnClick="btnUpload_Click" Text="Upload" />

The following method is the code for uploading an image which will be invoked on btnUpload click event.

protected void btnUpload_Click(object sender, EventArgs e)
{
  //Get the extension of your file to be uploaded
  string _fileExtension = UploadPhoto.PostedFile.FileName.Substring( UploadPhoto.PostedFile.FileName.LastIndexOf('.'));
  if (_fileExtension != ".jpg" && _fileExtension != ".gif" && _fileExtension != ".bmp" && _fileExtension !=   ".png")
  {
    ltScript.Text = "Please upload images(jpeg/gif/bmp/png) files";
    return;
  }

  int _filesize = UploadPhoto.FileBytes.Length;
  // Checking file length
  if(_filesize>200000)
  {
    ltScript.Text="Size exeeds the limit of 200 KB, please upload a file with size of maximum 200 KB";
    return;
  }

  FileUpload userImage = (FileUpload)UploadPhoto;
  Byte[] imgByte = null;
  if (userImage.HasFile && userImage.PostedFile != null)
  {
    //To create a PostedFile
    HttpPostedFile File = UploadPhoto.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);
    AddUserPhoto(ID, imgByte); // calling database method
  }
  ltScript.Text = "File has been uploaded...";
}

// Database Method in C#
internal bool AddUserPhoto(string ID, Byte[] Photo)
{
  string conn = "server=yourservername; database=yourdatabasename; uid=userid; pwd=yourpassword;";
  SqlConnection _sqlConnection= new SqlConnection(conn);

  bool _isInserted = false;
  _sqlCommand = new SqlCommand();
  _sqlCommand.Connection = _sqlConnection;
  _sqlCommand.CommandText = "insert into phototable values(@ID, @Photo)";
  _sqlCommand.CommandType = CommandType.StoredProcedure;
  _sqlCommand.Parameters.Add(new SqlParameter("@ID", ID));
  _sqlCommand.Parameters.Add(new SqlParameter("@Photo", Photo ));
  try
  {
    if(_sqlCommand.Connection.State != ConnectionState.Open)
    _sqlCommand.Connection.Open();

    int _result = _sqlCommand.ExecuteNonQuery();
    if (_result > 0)
    {
      _isInserted = true;
    }
  }
  catch (SqlException ex) {
  // Your Error code goes here for exception
  }
  finally
  {
    _sqlCommand.Connection.Close();
    _sqlCommand = null;
  }
  return _isInserted;
}

Showing Image from Database in ASP.NET

Add the following lines in ASP.NET file

<asp:image id="imgUserPhoto" imageurl="image.ashx?id=xvd21w54" runat="server"/>
or
<img src="http://www.blogger.com/image.ashx?id=xvd21w54" />

Create an image.ashx file and add the following code in it.

<%@ WebHandler Language="C#" %>
using System;
using System.Configuration;
using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;

public class image : IHttpHandler
{
  public void ProcessRequest(HttpContext context)
  {
    string id;
    if (context.Request.QueryString["id"] != null)
    {
      id = Convert.ToString(context.Request.QueryString["pid"]);
    }
    else
      throw new ArgumentException("No parameter specified");

    context.Response.ContentType = "image/jpeg";
    Stream Photo = GetPhoto(id, photonumber);
    if (Photo != null)
    {
      byte[] buffer = new byte[4096];
      int byteSeq = Photo.Read(buffer, 0, 4096);

      while (byteSeq > 0)
      {
        context.Response.OutputStream.Write(buffer, 0, byteSeq);
        byteSeq = Photo.Read(buffer, 0, 4096);
      }
    }
    else
    {
      //Loading default Image
      Byte[] imgByte = File.ReadAllBytes(context.Server.MapPath("upload_photo.JPG"));

      Photo = new MemoryStream(imgByte);
      byte[] buffer = new byte[4096];
      int byteSeq = Photo.Read(buffer, 0, 4096);

      while (byteSeq > 0)
      {
        context.Response.OutputStream.Write(buffer, 0, byteSeq);
        byteSeq = Photo.Read(buffer, 0, 4096);
      }

    }
    //context.Response.BinaryWrite(buffer);
  }
  public Stream GetPhoto(string Id, string PhotoNumber)
  {
    string conn = "server=yourservername; database=yourdatabasename; uid=userid; pwd=yourpassword;";
    SqlConnection connection = new SqlConnection(conn);

    string sqlQuery = "SELECT Photo FROM userphoto WHERE id=@id";

    SqlCommand cmd = new SqlCommand(sqlQuery, connection);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@id", Id);
    connection.Open();
    try
    {
      object img = cmd.ExecuteScalar();
      return new MemoryStream((byte[])img);
    }
    catch
    {
      return null;
    }
    finally
    {
      connection.Close();
    }
  }
  public bool IsReusable
  {
    get
    {
      return false;
    }
  }
}