How to store images in sql server 2005 database?

Question

Hi, Good morning, Please tell to me,How to store images from front end (upload control) to back end database in .net sql server 2005.

Neha Sharma · Answer

hi, http://www.c-sharpcorner.com/Forums/Thread/190743/store-image-files-in-sql-server.aspx Please go through with this link. if it helps you mark it as answer.

Satyapriya Nayak · Answer

Method -1 Default.aspx Untitled Page 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 Untitled Page Details of Employee Id : Name : 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 Untitled Page 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 Untitled Page 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=' '> ' Visible="False"> '> ' Visible="False"> '> '> ' height="50px" width="50px"/> 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(); } } }

Neha Sharma · Answer

Go through with the attachment. If it helps you,mark it as answer. Looking forward to hear from you soon. Thanks

How to store images in sql server 2005 database?

Answers (3)