Insert , Edit, Update, Delete, Get back Image from DataBase...?

In this artical i'm trying to explain how to insert,edit,update,delete and getback images from Database. Here i clearly explain how to insert image to DataBase and how to get back and display that into frontEnd Application..

Insert & Get back Image file from DataBase:

First off all we need to prepare tables for this. After successfully created tables need to create stored procedures for inserting and Getting results from DataBase. Now, we Design page like below.

Source Code:



<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ImageFromDB.aspx.cs" Inherits="ImageFromDB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

< html xmlns="http://www.w3.org/1999/xhtml">
< head runat="server">
    < title> < /title>
< /head>
< body>
    < form id="form1" runat="server">
    < div align="center" style="color: #000066">
        < br />
        < br />
    
    < table>
        < tr>
            < td>
                Emplpoyee Name :
            < /td>
            < td>
                < asp:TextBox ID="txtname" runat="server"> < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Job :
            < /td>
            < td>
                < asp:TextBox ID="txtjob" runat="server"> < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Manager :
            < /td>
            < td>
                < asp:TextBox ID="txtmgr" runat="server"> < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Salary :
            < /td>
            < td>
                < asp:TextBox ID="txtsal" runat="server">
                    < /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Commission :
            < /td>
            < td>
                < asp:TextBox ID="txtcomm" runat="server">< /asp:TextBox>
            < /td>
        < /tr>
        < tr>
            < td>
                Upload Image :
            < /td>
            < td>
                < asp:FileUpload ID="fileuploadimage" runat="server" />
            < /td>
        < /tr>
        < tr>
            < td>
            < /td>
            < td>
                < asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" 
                    Text="Insert" />
            < /td>
        < /tr>
    < /table>
    < table>
        < asp:GridView ID="GV" runat="server" AutoGenerateColumns="false" 
            HeaderStyle-BackColor="#7779AF" HeaderStyle-ForeColor="white" 
            OnRowCancelingEdit="GV_RowCancelingEdit" OnRowDeleting="GV_RowDeleting" 
            OnRowEditing="GV_RowEditing" OnRowUpdating="GV_RowUpdating">
            < AlternatingRowStyle BackColor="White" />
            < EditRowStyle BackColor="#7C6F57" />
            < FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            < HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            < PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            < RowStyle BackColor="#E3EAEB" />
            < SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            < SortedAscendingCellStyle BackColor="#F8FAFA" />
            < SortedAscendingHeaderStyle BackColor="#246B61" />
            < SortedDescendingCellStyle BackColor="#D4DFE1" />
            < SortedDescendingHeaderStyle BackColor="#15524A" />
            < Columns>
                < asp:BoundField DataField="empno" HeaderText="Employee Number" 
                    ReadOnly="true" />
                < asp:BoundField DataField="ename" HeaderText="Employee Name" />
                < asp:BoundField DataField="job" HeaderText="Job" />
                < asp:BoundField DataField="mgr" HeaderText="Manager" />
                < asp:BoundField DataField="hiredate" HeaderText="HireDate" ReadOnly="true" />
                < asp:BoundField DataField="sal" HeaderText="Salary" />
                < asp:BoundField DataField="comm" HeaderText="Commission" />

                < asp:TemplateField HeaderText="Picture">
                        < ItemTemplate>
                            < asp:Image ID="img" runat="server" Height="50px" ImageAlign="Top" 
                                ImageUrl='< %#DataBinder.Eval(Container.DataItem,"picture") %> ' Width="50px" />
                        < /ItemTemplate>
                        < EditItemTemplate>
                            < asp:FileUpload ID="fileuploadimage" runat="server" />
                        < /EditItemTemplate>
                < /asp:TemplateField>

                < asp:CommandField HeaderText="Edit" ShowEditButton="true" />
                < asp:CommandField HeaderText="Delete" ShowDeleteButton="true" />
            < /Columns>
        < /asp:GridView>
    < /table>
    < /div>
    < div>
    
    < /div>
    < /form >
< /body >
< /html >

Code Behind:


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;

public partial class ImageFromDB: System.Web.UI.Page
{

    SqlConnection con = new SqlConnection("DataBase=ENGSOFT;User id=sa;Password=P@ssword9");
    SqlDataAdapter da;
    DataSet ds;
    DataTable dt;
    SqlCommand cmd;
    int DeptNo=1;
    static int num = 0;


    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind_GV();
        }
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        if (fileuploadimage.HasFile)
        {
            string str = Server.MapPath(@"Image/");
            fileuploadimage.SaveAs(str + fileuploadimage.FileName);

//getting image name and store image name into Database.
            string img = fileuploadimage.FileName;
            try
            {
                con.Open();

                cmd = new SqlCommand("Insert_Employee_Details", con);
                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Parameters.AddWithValue("@Ename", txtname.Text);
                cmd.Parameters.AddWithValue("@Job", txtjob.Text);
                cmd.Parameters.AddWithValue("@mgr", txtmgr.Text);
                cmd.Parameters.AddWithValue("@Sal", txtsal.Text);
                cmd.Parameters.AddWithValue("@Comm", txtcomm.Text);
                cmd.Parameters.AddWithValue("@Pic", img);
                cmd.Parameters.AddWithValue("@Dept_No", DeptNo);

                int n = cmd.ExecuteNonQuery();
                
                if (n == 1)
                {
                    Response.Write("Record inserted sucessfully");
                    txtname.Text = "";
                    txtjob.Text = "";
                    txtmgr.Text = "";
                    txtsal.Text = "";
                    txtcomm.Text = "";
                }
                num = 0;
                Bind_GV();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
    }


        protected void Bind_GV()
    {        
        cmd = new SqlCommand("Get_Employees", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@DeptNo", DeptNo);
        dt = new DataTable();

        try
        {
            con.Open();
            da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                GV.DataSource = dt;
                GV.DataBind();
            }
            else
            {
                DataRow dr = dt.NewRow();

                dt.Rows.Add(dr);
                dt.AcceptChanges();
                GV.DataSource = dt;
                GV.DataBind();
                GV.Rows[0].Visible = false;
            }
            if (num == 0)
            {
                int n = dt.Rows.Count;
                for (int i = 0; i < n; i++)
                {
                    //fetch image from DataBase 
                    string file = dt.Rows[i][7].ToString();
                    //give specific path of the image
                    string path = "Image/" + file;
                    //find image control from Gridview
                    Image img = (Image)GV.Rows[i].FindControl("img");
                    // assign path to image
                    img.ImageUrl = path;
                }
                num = 1;
            }
        }
        catch (Exception ex)
        {

        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }


    protected void GV_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GV.EditIndex = e.NewEditIndex;
        Bind_GV();
    }
    protected void GV_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GV.EditIndex = -1;
        num = 0;
        Bind_GV();
    }
    protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int EmpNo = Convert.ToInt32(GV.Rows[e.RowIndex].Cells[0].Text);
        FileUpload fu = (FileUpload)GV.Rows[e.RowIndex].FindControl("fileuploadimage");
        TextBox EName = (TextBox)GV.Rows[e.RowIndex].Cells[1].Controls[0];
        TextBox Job = (TextBox)GV.Rows[e.RowIndex].Cells[2].Controls[0];
        TextBox Mgr = (TextBox)GV.Rows[e.RowIndex].Cells[3].Controls[0];
        TextBox sal = (TextBox)GV.Rows[e.RowIndex].Cells[5].Controls[0];
        TextBox comm = (TextBox)GV.Rows[e.RowIndex].Cells[6].Controls[0];
        //In edit mode also we give fileupload to upload new image and save that while update the row infomration.
        string imgPath = fu.FileName;
        string img = "Image/" + fu.FileName;
        if (fu.FileName == "")
        {
            img = imgPath;
        }
        else
        {
            fu.SaveAs(Server.MapPath("Image/" + fu.FileName));
        }
        try
        {
            con.Open();
            cmd = new SqlCommand("Update_Employee_Details", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EMPNO", EmpNo);
            cmd.Parameters.AddWithValue("@Ename", EName.Text);
            cmd.Parameters.AddWithValue("@Job", Job.Text);
            cmd.Parameters.AddWithValue("@mgr", Mgr.Text);
            cmd.Parameters.AddWithValue("@Sal", sal.Text);
            cmd.Parameters.AddWithValue("@Comm", comm.Text);
            cmd.Parameters.AddWithValue("@Pic", imgPath);
            cmd.ExecuteNonQuery();

            GV.EditIndex = -1;
            num = 0;
            Bind_GV();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }



    protected void GV_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        try
        {
            con.Open();
            int EmpNo = Convert.ToInt32(GV.Rows[e.RowIndex].Cells[0].Text);

            cmd = new SqlCommand("Delete_Employee_Details", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmpNo", EmpNo);
            cmd.ExecuteNonQuery();
            num = 0;  
            Bind_GV();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }
     

}

Conclusion:

Using the above code we can easily insert, update and getting the image from DataBase.

Hope this Artical will help those who are try to do the samething...

Insert , Edit, Update, Delete, Get back Image from DataBase...?

Insert & Get back Image file from DataBase:

Source Code:

Code Behind:

Conclusion:

Comments

About dotnetspider.com

Quick Links

General

Services