How to store images in sql server 2005 database?

Method -1

Default.aspx

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Details_with_photo._Default" %>

 



 





   





   

   

   

Employee Details

 

   

      

       

               

               

           

 

           

               

               

           

           

               

               

           

           

Name
Address
Image File

          

 

           
            Text="Insert" onclick="btn_insert_Click">

          

           

   

   

 
Default.aspx.cs

 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

namespace Details_with_photo

{

    public partial class _Default : System.Web.UI.Page

    {

        string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        string str;

        SqlCommand com;

      

        protected void btn_insert_Click(object sender, EventArgs e)

        {

            insertdata();

        }

        public void insertdata()

        {

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

            str = "select count(*)from employee where name='" + txtname.Text + "'";

            com = new SqlCommand(str, con);

            int count = Convert.ToInt32(com.ExecuteScalar());

            con.Close();

            if (count > 0)

            {

                lblMessage.Text = "Sorry! you can't take this username";

            }

            else

            {

                lblMessage.Text = "You can take this username";

                con.Open();

                int len = Upload.PostedFile.ContentLength;

                byte[] pic = new byte[len + 1];

                Upload.PostedFile.InputStream.Read(pic, 0, len);

                str = "insert into employee values (@name,@address,@photo)";

                com = new SqlCommand(str, con);

                com.Parameters.AddWithValue("@name", txtname.Text);

                com.Parameters.AddWithValue("@address", txtaddress.Text);

                com.Parameters.AddWithValue("@photo", pic);

                com.ExecuteNonQuery();

                con.Close();

                lblMessage.Text = "Employee Details Inserted Successfully!";

                Response.Redirect("employee.aspx");

                con.Close();

            }

       }

    }

}

 

 

employee.aspx

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="employee.aspx.cs" Inherits="Details_with_photo.employee" %>

 



 





   





   

   

   

      

         

Details of Employee

      

      

         Id : <%# Eval("id") %>

        


         Name   : <%# Eval("name")%>

        


         Address  : <%# Eval("address")%>

        


      

        

---

      

    

   

   

 

 

employee.aspx.cs

 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

namespace Details_with_photo

{

    public partial class employee : System.Web.UI.Page

    {

        string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        string str;

        SqlCommand com;

        protected void Page_Load(object sender, EventArgs e)

        {

         SqlConnection con = new SqlConnection(strConnString);

         con.Open();

         str = "select id,name,address from employee";

         com = new SqlCommand(str, con);

         SqlDataReader reader;

         reader = com.ExecuteReader();

         repeater1.DataSource = reader;

         repeater1.DataBind();

         con.Close();

        }

    }

}

 

 

image.aspx

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="image.aspx.cs" Inherits="Details_with_photo.image" %>

 



 





   





   

   

  

   

   

 

image.aspx.cs

 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Drawing;

using System.Drawing.Imaging;

using System.IO;

using System.Data.SqlClient;

namespace Details_with_photo

{

    public partial class image : System.Web.UI.Page

    {

        string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        string str;

        SqlCommand com;

        protected void Page_Load(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

            str = "select photo from employee where id = '" + Request.QueryString["id"] + "'";

            com=new SqlCommand(str, con);

            MemoryStream stream = new MemoryStream();

            byte[] image = (byte[])com.ExecuteScalar();

            stream.Write(image, 0, image.Length);

            Bitmap bitmap = new Bitmap(stream);

            Response.ContentType = "image/Jpeg";

            bitmap.Save(Response.OutputStream, ImageFormat.Jpeg);

            con.Close();

            stream.Close();

        }

    }

}
----------------------------------------------------


Method -2

Stored procedure

 

CREATE PROCEDURE insert_employee

(     

@name varchar(50),

@address varchar(50),

@image varchar(50)

)

AS

Insert into employee values (@name,@address,@image)

 

 

CREATE PROCEDURE update_employee

(

@id int,

@name varchar(50),

@address varchar(50)

)

AS

Update employee set name=@name, address=@address where id=@id

 

 

CREATE PROCEDURE delete_employee

(@id int)

AS

Delete from employee where id=@id

 

 
Default.aspx

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Add_update_delete_gridview._Default" %>

 



 





   





   

   

   

       

           

           

       

       

           

           

       

       

           

           

       

       

           

           

       

       

           

       

       

Name	ControlToValidate="txt_name" ErrorMessage="Please enter the name">
Address	ControlToValidate="txt_address" ErrorMessage="Please enter the address">
Image	ControlToValidate="FileUpload1" ErrorMessage="Please browse the image">
	Text="Insert" />
onrowcancelingedit="GridView1_RowCancelingEdit"                     onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"                     onrowupdating="GridView1_RowUpdating"                     onselectedindexchanging="GridView1_SelectedIndexChanging"                     BackColor="#CC3300" ForeColor="Black">                                                                                                                                                                                                             Text='<%# Eval("name") %>'>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          CommandName="Update">Update                                                                     CommandName="Cancel">Cancel                                                                                                                                                           CommandName="Edit">Edit                                                                                                                                                                                                                           CommandName="Delete"                                     onclientclick="return confirm('Are you sure you want to delete this record')">Delete

   

   

 

 

 
Default.aspx.cs

 

 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using System.IO;

namespace Add_update_delete_gridview

{

    public partial class _Default : System.Web.UI.Page

    {

 

    string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    string s1;

    string path;

    SqlConnection cnn = new SqlConnection();

    SqlCommand com = new SqlCommand();

    SqlDataAdapter sqlda;

    DataTable dt;

    int id;

    string name;

    string address;

    string image;

 

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                bindgrid();

            }

        }

 

        protected void btn_insert_Click(object sender, EventArgs e)

        {

          

            if (FileUpload1.PostedFile.ContentLength > 0)

            {

                s1 = Path.GetFileName(FileUpload1.FileName);

                path = Server.MapPath("images") + "/" + s1;

                FileUpload1.SaveAs(path);

            }

          

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

            SqlCommand com = new SqlCommand("insert_employee", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Connection = con;

            com.Parameters.AddWithValue("@name", txt_name.Text);

            com.Parameters.AddWithValue("@address", txt_address.Text);

            com.Parameters.AddWithValue("@image", s1);

            com.ExecuteNonQuery();

            com.Dispose();

            bindgrid();

            con.Close();

            clear();

        }

        private void clear()

        {

            txt_name.Text = "";

            txt_address.Text = "";

        }

        private void bindgrid()

        {

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

            sqlda = new SqlDataAdapter("SELECT * FROM employee ", con);

            dt = new DataTable();

            sqlda.Fill(dt);

            sqlda.Dispose();

            GridView1.DataSource = dt;

            GridView1.DataBind();

            con.Close();

        }

 

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

        {

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

            try

            {

             

                id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("label2"))).Text);

 

                SqlCommand com = new SqlCommand("delete_employee", con);

                com.CommandType = CommandType.StoredProcedure;

                com.Connection = con;

                com.Parameters.Add("@id", SqlDbType.Int).Value = id;

                SqlDataAdapter sqlda = new SqlDataAdapter("select * from employee where id=@id", con);

             

                sqlda.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;

                DataSet ds = new DataSet();

                sqlda.Fill(ds);

                try

                {

                    image = Convert.ToString(ds.Tables[0].Rows[0]["image"]);

                    File.Delete(Server.MapPath("images") + "\\" + image);

                }

                catch (Exception)

                {

                 

                }

                com.ExecuteNonQuery();

                com.Dispose();

                bindgrid();

            }

            catch(Exception)

            {

             

            }

        }

 

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

        {

            GridView1.EditIndex = -1;

            bindgrid();

        }

 

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

        {

            GridView1.EditIndex = e.NewEditIndex;

            bindgrid();

        }

 

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

        {

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

        

            id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("label4"))).Text);

            name = (((TextBox)(GridView1.Rows[e.RowIndex].FindControl("txt_name"))).Text);

            address = (((TextBox)(GridView1.Rows[e.RowIndex].FindControl("txt_address"))).Text);

            SqlCommand com = new SqlCommand("update_employee", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Connection = con;

            com.Parameters.Add("@id", SqlDbType.Int).Value = id;

            com.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = name;

            com.Parameters.Add("@address", SqlDbType.VarChar, 50).Value = address;

            com.ExecuteNonQuery();

            com.Dispose();

            con.Close();

            GridView1.EditIndex = -1;

            bindgrid();

        }

 

        protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)

        {

            GridView1.PageIndex = e.NewSelectedIndex;

            bindgrid();

        }

    }

}

Go through with the attachment.

If it helps you,mark it as answer.

Looking forward to hear from you soon.

Thanks