Sunday, June 2, 2013

How to Save Image in Binary Format in ASP.Net in Database

Leave a Comment
In this tutorial I will explain how to save Image in Sql Server Database in Binary format and then displaying the image inside the Grid View control. In asp.net there are two options either you can store images inside the folder and storing its path in the database or second way is store image as binary image inside the database.

In this example I am storing sample student records inside the database along with image in binary format and then bind those records in a Grid View as shown in the diagram below:




Steps to Store and Bind Binary Image in ASP.NET using SQL Server Database:

Step 1: First create table and stored procedure in database using the script given below:



Run the Table Script Given Below:


CREATE TABLE [dbo].[STUDENT_RECORD](
      [STU_ID] [int] IDENTITY(1,1) NOT NULL,
      [STU_NAME] [varchar](100) NULL,
      [CLASS] [varchar](50) NULL,
      [EMAIL] [varchar](50) NULL,
      [NAME] [varchar](50) NULL,
      [ContentType] [varchar](50) NULL,
      [Image_File] [varbinary](max) NULL,
 CONSTRAINT[PK_STUDENT_RECORD] PRIMARY KEY CLUSTERED
(
      [STU_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

GO

Stored Procedure Script:
CREATE PROCEDURE [dbo].[Insert_Record]
      (
      @STU_NAME varchar(100),
      @CLASS varchar(50),
      @EMAIL varchar(50),
      @Name varchar(50),
      @ContentType varchar(50),
      @Image_File varbinary(max)
      )
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNTON;

    -- Insert statements for procedure here
      Insert intoSTUDENT_RECORD (STU_NAME,CLASS,EMAIL,Name,ContentType,Image_File) values(@STU_NAME,@CLASS,@EMAIL,@Name,@ContentType,@Image_File)
END

Step 2: In the Front End Add a New Web Form and add the following code:

Design View Code:

  <form id="form1" runat="server">
    <div>
   <table border="0" cellpadding="0"width="100%"cellspacing="0">
   <tr><td width="10%"></td><td width="10%"></td><td></td></tr>
      <tr><td width="10%"></td><td width="10%">Enter Name</td><td>
          <asp:TextBox ID="txtName"runat="server"></asp:TextBox></td></tr>
         <tr><td width="10%"></td><td width="10%">Enter Class</td><td><asp:TextBox ID="txtClass"runat="server"></asp:TextBox></td></tr>
            <tr><td width="10%"></td><td >Email Id</td><td>
                <asp:TextBox ID="txtEmail"
                    runat="server"></asp:TextBox></td></tr>
               <tr><td width="10%"></td><td >Upload Image</td><td>
                   <asp:FileUpload ID="FileUpload1"runat="server"/></td></tr>
                    <tr><td width="10%"></td><td >
                        &nbsp;</td><td>
                        <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />
                            <asp:HiddenField ID="hdid" runat="server"Value="0"/>
               </td></tr>
                  <tr><td>&nbsp;</td></tr>
                  <tr><td colspan="3">
                      <asp:GridView ID="GridView1" runat="server" Width="70%"
        AutoGenerateColumns="False"DataKeyNames="STU_ID"
        AllowPaging="True"PageSize="4"
        style="margin-top: 0px" onrowdeleting="GridView1_RowDeleting"
                          onpageindexchanging="GridView1_PageIndexChanging">
        <Columns>
      
            <asp:BoundField DataField="STU_NAME"HeaderText="STUDENT NAME"
                SortExpression="STU_NAME"/>
            <asp:BoundField DataField="CLASS"HeaderText="CLASS"SortExpression="CLASS"/>
                <asp:BoundField DataField="EMAIL"HeaderText="Email Id" SortExpression="CLASS" />
            <asp:ImageField DataImageUrlField = "STU_ID"
        DataImageUrlFormatString ="ImageCSharp.aspx?ImageID={0}"
     ControlStyle-Width = "100" ControlStyle-Height= "100"
     HeaderText = "Preview Image" ItemStyle-HorizontalAlign="Center"/>
          
        </Columns>
    </asp:GridView></td></tr>


   </table>
   
    </div>

    </form>

Add the Following Code in the Code Behind File:

 Code in C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Script.Serialization;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.IO;

public partial class Test_Grid : System.Web.UI.Page
{
    SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());
 static   int image_height;
  static  int image_width;
  static  int max_height;
  static  int max_width;
  static byte[] data;
  static stringfilename;
  static stringcontenttype;
    protected voidPage_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindGrid();
        }

    }
    //Bind Data to Grid View
    private voidBindGrid()
    {
        try
        {

            con.Open();
            string qry = "Select *from STUDENT_RECORD";
            SqlDataAdapter adp = newSqlDataAdapter(qry, con);
            DataTable dt = newDataTable();
            adp.Fill(dt);

            GridView1.DataSource = dt;
            GridView1.DataBind();

            con.Close();


        }
        catch (Exceptionex)
        {
            var message = new JavaScriptSerializer().Serialize(ex.Message);
            var script = string.Format("alert({0});", message);
            ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script, true);


        }
        finally
        {
            con.Close();
        }

    }


    //On Submit button save data to database
    protected voidbtnSubmit_Click(object sender, EventArgs e)
    {
        InsertData(txtName.Text.Trim(), txtClass.Text.Trim(),txtEmail.Text.Trim());
        ClearControl();
    }

    //Function to insert record in database
    private voidInsertData(string studentname, string stuclass, stringessay)
    {
        con.Open();
        try
        {

            if (FileUpload1.HasFile)
            {
                // Read the file and convert it to Byte Array
                string filePath = FileUpload1.PostedFile.FileName;
                filename = Path.GetFileName(filePath);
                string ext = Path.GetExtension(filename);
                contenttype = String.Empty;

                //Set the contenttype based on File Extension
                switch (ext)
                {
                    case ".doc":
                        contenttype = "application/vnd.ms-word";
                        break;
                    case ".docx":
                        contenttype = "application/vnd.ms-word";
                        break;
                    case ".xls":
                        contenttype = "application/vnd.ms-excel";
                        break;
                    case ".xlsx":
                        contenttype = "application/vnd.ms-excel";
                        break;
                    case ".jpg":
                        contenttype = "image/jpg";
                        break;
                    case ".png":
                        contenttype = "image/png";
                        break;
                    case ".gif":
                        contenttype = "image/gif";
                        break;
                    case ".pdf":
                        contenttype = "application/pdf";
                        break;
                }
                System.Drawing.Image image_file = System.Drawing.Image.FromStream(FileUpload1.PostedFile.InputStream);

                if (contenttype != string.Empty)
                {

                    image_height = image_file.Height;
                    image_width = image_file.Width;
                    max_height = 120;
                    max_width = 160;


                    image_height = (image_height * max_width) / image_width;
                    image_width = max_width;

                    if (image_height > max_height)
                    {
                        image_width = (image_width * max_height) / image_height;
                        image_height = max_height;
                    }
                    else
                    {
                    }
                    Bitmap bitmap_file = new Bitmap(image_file, image_width, image_height);
                    System.IO.MemoryStream stream = new System.IO.MemoryStream();
                    bitmap_file.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
                    stream.Position = 0;

                    data = new byte[stream.Length + 1];
                    stream.Read(data, 0, data.Length);

                }
            }
            else
            {
                var message = newJavaScriptSerializer().Serialize("Please Upload an image");
                var script = string.Format("alert({0});", message);
                ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script, true);
                return;

            }
            //Stream fs = FileUpload2.PostedFile.InputStream;
            //BinaryReader br = new BinaryReader(fs);
            //Byte[] bytes = br.ReadBytes((Int32)fs.Length);
            SqlCommand cmd = newSqlCommand("Insert_Record", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@STU_NAME", txtName.Text.Trim());
            cmd.Parameters.AddWithValue("@CLASS", txtClass.Text.Trim());
            cmd.Parameters.AddWithValue("@EMAIL ", txtEmail.Text.Trim());
            cmd.Parameters.AddWithValue("@Name", filename);
            cmd.Parameters.AddWithValue("@ContentType", contenttype);
            cmd.Parameters.AddWithValue("@image_file", data);
            cmd.ExecuteNonQuery();
            con.Close();
            BindGrid();

        }

        catch (Exceptionex)
        {

        }


    }
}


Step 3: Add a New Web Form With the name ImageCSharp.aspx and add the given code in the code behind file:

protected void Page_Load(object sender, EventArgse)
    {
        if (Request.QueryString["ImageID"] != null)
        {
            string strQuery = "select Name, ContentType, image_file from" +
                " STUDENT_RECORD where STU_ID=@STU_ID";
            String strConnString = System.Configuration.ConfigurationManager
                .ConnectionStrings["con"].ConnectionString;
            SqlCommand cmd = newSqlCommand(strQuery);
            cmd.Parameters.Add("@STU_ID", SqlDbType.Int).Value
                = Convert.ToInt32(Request.QueryString["ImageID"]);
            SqlConnection con = newSqlConnection(strConnString);
            SqlDataAdapter sda = newSqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            DataTable dt = newDataTable();
            try
            {
                con.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
            catch
            {
                dt = null;
            }
            finally
            {
                con.Close();
                sda.Dispose();
                con.Dispose();
            }
            if (dt != null)
            {
                Byte[] bytes = (Byte[])dt.Rows[0]["image_file"];
                Response.Buffer = true;
                Response.Charset = "";
                Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.ContentType = dt.Rows[0]["ContentType"].ToString();
                Response.AddHeader("content-disposition", "attachment;filename="
                    + dt.Rows[0]["Name"].ToString());
                Response.BinaryWrite(bytes);
                Response.Flush();
                Response.End();
            }
        }
    }

Step 4: Run the application and then insert some record in the database and see the final result.

If you get any problem while saving  the image into database as binary then mention your problem in the comments.

0 comments:

Post a Comment