save retrieve image in SQL SERVER database c sharp

mainiabhay — Tue, 23 Jun 2009 01:47:04 GMT

Hi All,

My this post is for people who are relatively new to ASP.NET/c-sharp programming and are learning new tricks.

This trick tells about how to save / retrieve image in database table. i must credit the link below which was of lot of help.

http://www.aspdotnetcodes.com/Insert_Images_Database.aspx

PLEASE FOLLOW STEPS BELOW:

STEP 1. prepare database

Create a field in your table named "AssetImage" with the type Image.

and field name scanId as type integer, this will be the unique id in the table to refer to that image as well.

STEP 2. How to SAVE the image

You must also have default.aspx file in your project if not then add one.

add 3 controls in your aspx file in design view.

1. FileUploader named FileUpload2

2. Image named ImgScanID

3. textbox named txtScanId to enter the id to retrieve the image

go to code behind aspx called default.cs

and paste the code below, this code will upload the file in database from fileuploader

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

   byte[] myimage = new byte[FileUpload2.PostedFile.ContentLength];
   HttpPostedFile Image = FileUpload2.PostedFile;
   Image.InputStream.Read(myimage, 0, (int)FileUpload2.PostedFile.ContentLength);

     string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
           SqlConnection conn = new SqlConnection(strConnection);

//below code will update the existing entries in table, you can use insert statement instead to add new data.

//i assumed that you have existing data and are saving pictures

           string strSQLW = "UPDATE [Tbl_Name] " +
                        "SET [assetimage] = @image " +
                        "Where Scanid='" + LblScanID.Text + "'";
           SqlCommand cmdSQLWU = new SqlCommand(strSQLW);
           cmdSQLWU.Parameters.Add("@image", SqlDbType.Image, myimage.Length).Value = myimage;
           cmdSQLWU.Connection = conn;
           cmdSQLWU.Connection.Open();
           cmdSQLWU.ExecuteNonQuery();

STEP 3. How to RETRIEVE image in field named AssetImage.

 Add the following line of code behind default.cs to show the image control named ImgScanID behind click of any button or Page_Load event.

ImgScanID.ImageUrl = "~/Handler.ashx?id=" + TxtValue.Text;

Now Add add new file into your project called GenericHandler and name it as Handler.ashx

(Why you have to add this type of file is something you can learn from many sources available, but for now just do as i say  )

Erase the existing code and Copy and paste the code below in this handler.ashx file, below code will read the saved image in binary format.

    <%@ WebHandler Language="C#" Class="Handler" %>
    
    using System;
    using System.Web;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    
    public class Handler : IHttpHandler {
       
       public void ProcessRequest (HttpContext context) {
           string strConnection = ConfigurationManager.ConnectionStrings["RevisionConnectionString"].ToString();
           SqlConnection conn = new SqlConnection(strConnection);
           conn.Open();
   
           string sql = "SELECT AssetImage FROM [AssetConfigurationDetails] " +
                                                "WHERE [ScanID]=@ScanID";
           SqlCommand cmd = new SqlCommand(sql, conn);
           cmd.Parameters.Add("@ScanID", SqlDbType.Int).Value = context.Request.QueryString["id"];
           cmd.Prepare();
           SqlDataReader dr = cmd.ExecuteReader();
           if (dr.Read()) //yup we found our image
           {
               context.Response.ContentType = dr["AssetImage"].ToString();
               context.Response.BinaryWrite((byte[])dr["AssetImage"]);
           }
           conn.Close();
          
       }
   
       public bool IsReusable {
           get {
               return false;
           }
       }
   
   }

Folkes i have tried my best to keep it simple, still if you have any questions you may post  them, also dont forget to appreciate

Abhay.

Tips & Tricks

save retrieve image in SQL SERVER database c sharp