Click here to Skip to main content
Click here to Skip to main content

Display/Store and Retrieve Image Data from Database to Gridview, and also on Mouse Over

, 15 Oct 2011
Rate this:
Please Sign up or sign in to vote.
How to Display/Store and Retrieve Image Data from Database to Gridview, and also on mouse over
image002_small.jpg

image003_small.jpg

image004_small.jpg

Introduction

A while ago, I had a task to develop a User’s Maintenance Module which should have a Datagrid view to display the information about Users with the corresponding image/picture and having corresponding command image link option to Edit/Delete and display in full size image on detail page with corresponding inputted information. Also, at the bottom center of the grid, there should be an option to add a new user. When the Add button is clicked, there should be a popup custom control page to input an entry and store some information with images within a database as part of project requirements. This uploaded image file should be resized as a thumbnail and fixed full size, then should be displayed into image box as thumb custom control add page. After input of all the earlier mentioned things, there should be a button option to save or cancel Add option entry, then automatically back to grid, then refresh.

This article will demonstrate how to display/store and retrieve image data from an SQL database to gridview, and shall also demonstrate how to create thumbnail/full size images from resizing from uploaded file, then save this into SQL database. Also I added a magnifying glass like zoom on mouse over on image.

The Database

Why should we use a database and not just have the images within a virtual folder under the main ASP.NET folder? Well there are several reasons why it might be good to store images in a database, such as:

  • If the images binary data is stored in a database table, we have all the data required to make the image any size we want, and it will always look like the original image, also we have an option to resize the image and save it with a specified Height/width dimension.
  • If the images binary data is stored in a database table, when we back the database up, we have also backed up all the images.
  • Performance on retrieving images to display from web is faster.

I am not saying this is the only way to go. I supposed it’s simply one way. Therefore I thought I should share what I found with you.

So what does the database look like?

It simply contains one table called tbl_image which can be setup using the following script (contained within the object creation script at the top of this article).

USE [UserCatalog]
GO

/****** Object:  Table [dbo].[User]    Script Date: 10/12/2011 13:58:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[User](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](255) NOT NULL,
	[Password] [varchar](max) NOT NULL,
	[LastName] [varchar](255) NULL,
	[FirstName] [varchar](255) NULL,
	[MiddleName] [varchar](255) NULL,
	[WorksiteCode] [varchar](50) NOT NULL,
	[AccessLevel] [int] NOT NULL,
	[Active] [varchar](5) NOT NULL,
	[DateCreated] [datetime] NULL,
	[DateUpdated] [datetime] NULL,
	[Worksitedesc] [varchar](50) NULL,
	[Picture] [varbinary](max) NULL,
	[ImageFull] [varbinary](max) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
	[UserID] 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

So how do we get data out from this table?

Let's have a look at one of this generic handler with full out text information/image from table to data gridview, they are quite easy, in fact I think the comments code in the below code pretty much explain them in enough detail. So I won't bore you with any more explanatory words, as it's clear.

So here it is…

//*======================================================================
<%@ WebHandler Language="C#" Class="ShowImage" %>
using System;
using System.Configuration;
using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;

public class ShowImage : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        Int32 idNo;

        if (context.Request.QueryString["id"] != null)
            idNo = Convert.ToInt32(context.Request.QueryString["id"]);

        else
            throw new ArgumentException("No parameter specified");

        context.Response.ContentType = "image/jpeg";
        Stream strm = ShowEmpImage(idNo);
        byte[] buffer = new byte[4096];
        int byteSeq = 0;
        try
        {
            byteSeq = strm.Read(buffer, 0, 4096);

        }
        catch (Exception)
        {
            return;
        }

        while (byteSeq >

So how do we get data in into this table?

Let's have a look at custom control client code and at its code behind that I’ve made, they are quite easy, in fact I think the comments code in the below code pretty much explains them in enough detail. So I won't bore you with any more explanatory words, as it's clear.

So here is the Custom Control … (Client Code):

//*======================================================================
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="UserAccountUC.ascx.cs"
    Inherits="UsersControl_UserAccountUC" %>
<%@ Register Assembly="System.Web.Extensions, Version=3.5.0.0, 
	Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI" TagPrefix="asp" %>

<script type="text/javascript" language="javascript">
   <!--
    function RetrievePicture(imgCtrl, picid) {
        imgCtrl.onload = null;
        imgCtrl.src = 'ShowImage.ashx?id=' + picid;
    }
    -->
</script>

<table width="100%">
    <table frame="box" width="100%">
        <tr>
            <td width="2%">
            </td>
            <td>
                <h1>
                    <asp:Label ID="lblAccountPanel" runat="server" 

			Font-Size="Medium" ForeColor="Black"
                        	Text="User Settings"></asp:Label>
                </h1>
            </td>
            <td align="right">
                <asp:ImageButton ID="btnClose" runat="server" 
			CausesValidation="false" ImageUrl="~/Images/close.gif"
                    OnClick="btnClose_Click" />
            </td>
            <td width="2%">
            </td>
        </tr>
    </table>
    <table frame="box" width="100%">
        <tr>
            <td width="2%">
            </td>
            <td class="style1">
                <asp:FileUpload ID="FileUpload1" runat="server" />
            </td>
            <td width="2%">
            </td>
        </tr>
    </table>
    <table frame="box" width="100%">
        <tr>
            <td width="2%">
            </td>
            <td>
                <div class="pictureWrap" style="float: left; width: 100px; 
				height: 100px; border: 1px solid #ccc;">
                    <asp:UpdatePanel ID="upImg" runat="server" UpdateMode="Conditional">
                        <ContentTemplate>
                            <asp:Image ID="Image1" runat="server" 
				Height="100px" ImageAlign="Middle" Visible="true"
                                Width="100px" OnError="src='images/spicture.jpg'" />
                        </ContentTemplate>
                    </asp:UpdatePanel>
                    <asp:Button ID="btnUpLoad" runat="server" 
				Text="Upload Img." OnClick="btnUpload_Click" />
                </div>
                <br />
                <table>
                    <tr>
                        <td align="right">
                            <label>
                                User Name:
                            </label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtUserName" runat="server" 
				Title="Enter Username" />
                        </td>
                    </tr>
                    <tr>
                        <td align="right">
                            <label>
                                Password:
                            </label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtPassword" runat="server" 
			    MaxLength="20" TextMode="Password" Title="p@ssw0rd"  />
                        </td>
                    </tr>
                    <tr>
                        <td align="right">
                            <label>
                                Confirm Password:
                            </label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtPasswordConf" runat="server" 
			   MaxLength="20" TextMode="Password" Title="p@ssw0rd" />
                        </td>
                    </tr>
                    <tr>
                        <td align="right">
                            <label>
                                First Name:
                            </label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtFirstName" runat="server" />
                        </td>
                    </tr>
                    <tr>
                        <td align="right">
                            <label>
                                Middle Name:
                            </label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtMiddleName" runat="server" />
                        </td>
                    </tr>
                    <tr>
                        <td align="right">
                            <label>
                                Last Name:
                            </label>
                        </td>
                        <td>
                            <asp:TextBox ID="txtLastName" runat="server" />
                        </td>
                    </tr>
                    <tr>
                        <td align="right">
                            <label>
                                Access Level:
                            </label>
                        </td>
                        <td>
                            <asp:DropDownList ID="ddlAccessLevel" 
				runat="server" Width="155">
                                <asp:ListItem Text="Processor" Value="1"> 
				</asp:ListItem>
                                <asp:ListItem Text="Approver" Value="2"> 
				</asp:ListItem>
                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td align="left">
                             
                        </td>
                        <td>
                            <asp:CheckBox ID="chkActive" runat="server"
                            Text="Is Active" TextAlign="Left" Checked="true" />
                        </td>
                    </tr>
                </table>
            </td>
            <td width="2%">
            </td>
        </tr>
    </table>
    <table frame="box" width="100%">
        <tr>
            <td width="2%">
            </td>
            <td>
                <asp:Label ID="Label1" runat="server"
                ForeColor="Red" Text=""></asp:Label>
            </td>
            <td width="2%">
            </td>
        </tr>
    </table>
    <table frame="box" width="100%">
        <tr align="center">
            <td width="2%">
            </td>
            <td>
                <asp:Button ID="btnSave" runat="server"
                CssClass="button-primary" OnClick="btnSave_Click"
                    TabIndex="100" Text="Save" />
                <asp:Button ID="btnClear" runat="server"
                CssClass="button-primary" OnClick="btnClear_Click"
                    TabIndex="100" Text="Clear" />
            </td>
            <td width="2%">
            </td>
        </tr>
    </table>
</table>
<br />
<asp:HiddenField ID="hidUserID" runat="server" />
<asp:HiddenField ID="hfFileToUpload" runat="server" />
<asp:HiddenField ID="hfUrl" runat="server" />

And here is the Custom Control… (the 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.IO;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;
using System.Net;

[Serializable]
internal class Manage
{
    private const string FILEUPLOAD = "FileUpload";
    public static string SessionFileZise
    {
        get
        {
            if (HttpContext.Current.Session[FILEUPLOAD] == null)
            {
                return string.Empty;
            }
            else
            {
                return HttpContext.Current.Session[FILEUPLOAD].ToString();
            }
        }
        set
        {
            HttpContext.Current.Session[FILEUPLOAD] = value;
        }
    }
}

public partial class UsersControl_UserAccountUC : System.Web.UI.UserControl
{
    public void DisplayInfo(object sender, EventArgs e)
    {
        this.Image1 = null;
        Page_Load(sender, e);
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.Image1 == null)
        {

        }
    }

    protected void ShowMessage(string message, int fileUploadPos)
    {
        if (fileUploadPos == 0)
        {
            Label1.Text = message;
        }
    }

    protected void btnClose_Click(object sender, ImageClickEventArgs e)
    {

        Page.GetType().InvokeMember("CloseModalUserAccountUC",
        System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
        this.Dispose();
    }
    protected void btnClear_Click(object sender, EventArgs e)
    {
        this.Image1 = null;
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (this.txtPassword.Text != this.txtPasswordConf.Text)
        {
            this.Label1.Text = "Password confirmation not equal!";
            Page.GetType().InvokeMember("IvokeAdd",
            System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
            return;
        }
        if (this.txtFirstName.Text == string.Empty)
        {
            this.Label1.Text = "First Name text box should not empty!";
            Page.GetType().InvokeMember("IvokeAdd",
            System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
            return;
        }
        if (this.txtMiddleName.Text == string.Empty)
        {
            this.Label1.Text = "Middle Name text box should not empty!";
            Page.GetType().InvokeMember("IvokeAdd",
            System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
            return;
        }
        if (this.txtLastName.Text == string.Empty)
        {
            this.Label1.Text = "Last Name text box should not empty!";
            Page.GetType().InvokeMember("IvokeAdd",
            System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
            return;
        }
        if (this.txtPassword.Text.Length < 8)
        {
            this.Label1.Text = "Password length should not lesser that 8!";
            Page.GetType().InvokeMember("IvokeAdd",
            System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
            return;
        }
        bool success = false;
        HttpFileCollection uploadFilCol = null;
        FileUpload img = null;
        FileUpload imgUpload = null;
        var originalSize = Tools.Tools.IifInt(Manage.SessionFileZise);
        try
        {
            HttpFileCollection uploadFil = Request.Files;
            uploadFilCol = uploadFil;
            imgUpload = (FileUpload)Session["UploadFile2"];
            img = (FileUpload)imgUpload;
            originalSize = Tools.Tools.IifInt(Manage.SessionFileZise);
        }
        catch (Exception)
        {
            return;
        }
        var targetDir = Server.MapPath("./upload/");
        var sourceDirFile = Server.MapPath("./images/");
        string[] files = Directory.GetFiles(targetDir);
        if (this.Image1.ImageUrl == string.Empty)
        {
            // Save record with default image NoImage...
            int id = SaveRecord(false, imgUpload, img, originalSize,
                sourceDirFile, files);
        }
        else
        {
            int id = SaveRecord(true, imgUpload, img, originalSize,
               sourceDirFile, files);
        }

        try
        {
            foreach (string file in files)
            {
                File.SetAttributes(file, FileAttributes.Normal);
                File.Delete(file);
            }
        }
        catch (FileNotFoundException)
        {

        }
        this.FileUpload1.Dispose();
        success = true;
        this.Label1.Text = "Record was successfully save.";

        if (success)
        {
            Page.GetType().InvokeMember("CloseModalUserAccountUC", 
		System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
        }
    }

    private int SaveRecord(bool withImage, FileUpload imgUpload, FileUpload img,
        int originalSize, string sourceDirFile, string[] files)
    {
        SqlConnection connection = null;
        User user = new User();
        Byte[] imgByte = null;
        try
        {
            if (img.HasFile && img.PostedFile != null)
            {
                //To create a PostedFile
                HttpPostedFile File = imgUpload.PostedFile;
                //Create byte Array with file len
                imgByte = new Byte[File.ContentLength];
                //force the control to load data in array
                File.InputStream.Read(imgByte, 0, File.ContentLength);
                originalSize = File.ContentLength;
            }
        }
        catch
        {

        }
        try
        {
            user.Username = this.txtUserName.Text;
            user.Password = this.txtPassword.Text;
            user.LastName = this.txtLastName.Text.Substring(0, 1).ToUpper() +
                            this.txtLastName.Text.Substring(1).ToLower();
            user.FirstName = this.txtFirstName.Text.Substring(0, 1).ToUpper() +
                             this.txtFirstName.Text.Substring(1).ToLower();
            user.MiddleName = this.txtMiddleName.Text.ToUpper().Substring(0, 1) +
                              this.txtMiddleName.Text.Substring(1).ToLower();
            user.WorksiteCode = "1";
            user.AccessLevel = Tools.Tools.IifInt
				(this.ddlAccessLevel.SelectedValue.ToString());
            if (this.chkActive.Checked)
            {
                user.Active = "Y";
            }
            else
            {
                user.Active = "N";
            }
            user.DateCreated = DateTime.Now;
            //user.DateUpdated = DateTime.Now;
            user.Worksitedesc = "1";
            //===============================
            string userName = CleanText(user.Username);
            string password = CleanText(user.Password);
            string lastName = CleanText(user.LastName);
            string firstName = CleanText(user.FirstName);
            string middleName = CleanText(user.MiddleName);
            string workSiteCode = CleanText(user.WorksiteCode);
            int accessLevel = user.AccessLevel;
            string active = CleanText(user.Active);
            DateTime dateCreated = user.DateCreated;
            string workSiteDesc = CleanText(user.Worksitedesc);
            // ==========
            Size eimgFullSize = new Size();
            eimgFullSize.Height = 400;
            eimgFullSize.Width = 400;
            //---
            Size eimgThumbSize = new Size();
            eimgThumbSize.Height = 100;
            eimgThumbSize.Width = 100;
            //--
            Size eimgPosterSize = new Size();
            eimgPosterSize.Height = 250;
            eimgPosterSize.Width = 250;
            // Here we have three dimension size of image to produce...
            Byte[] eimgFull = null;
            Byte[] eimgThumb = null;
            Byte[] eimgPoster = null;
            //========
            if (withImage)
            {
                eimgFull = ResizeImageFile(imgByte, eimgFullSize);
                eimgThumb = (Byte[])ResizeImageFile(imgByte, eimgThumbSize);
                eimgPoster = (Byte[])ResizeImageFile(imgByte, eimgPosterSize);
            }
            else
            {
                // Read the default NoImage file
                System.Drawing.Image image1 = System.Drawing.Image.FromFile
					(sourceDirFile + "\\spicture.jpg");
                imgByte = ImageToByte2(image1);
                eimgFull = ResizeImageFile(imgByte, eimgFullSize);
                eimgThumb = (Byte[])ResizeImageFile(imgByte, eimgThumbSize);
                eimgPoster = (Byte[])ResizeImageFile(imgByte, eimgPosterSize);

            }
            // Insert the employee name and image into db
            string conn = ConfigurationManager.ConnectionStrings
				["ConnectionString"].ConnectionString;
            connection = new SqlConnection(conn);
            connection.Open();
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO dbo.[User](");
            sb.Append("Username,Password,LastName,FirstName,MiddleName,WorksiteCode,");
            sb.Append("AccessLevel,Active,DateCreated,Worksitedesc,Picture,ImageFull ");
            sb.Append(") VALUES (");
            sb.Append("@userName ,@password,@lastName,@firstName,");
            sb.Append("@middleName,@worksiteCode,@accessLevel,@active,");
            sb.Append("@dateCreated,@worksitedesc,");
            sb.Append("@eimgThumb,@eimgFull) ");  // For Picture image
            sb.Append(" SELECT @@IDENTITY");
            string sql = sb.ToString();
            //----------
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.Parameters.AddWithValue("@userName", user.Username);
            cmd.Parameters.AddWithValue("@password", user.Password);
            cmd.Parameters.AddWithValue("@lastName", user.LastName);
            cmd.Parameters.AddWithValue("@firstName", user.FirstName);
            cmd.Parameters.AddWithValue("@middleName", user.MiddleName);
            cmd.Parameters.AddWithValue("@worksiteCode", user.WorksiteCode);
            cmd.Parameters.AddWithValue("@accessLevel", user.AccessLevel);
            cmd.Parameters.AddWithValue("@active", user.Active);
            cmd.Parameters.AddWithValue("@dateCreated", user.DateCreated);
            cmd.Parameters.AddWithValue("@worksitedesc", user.Worksitedesc);
            cmd.Parameters.AddWithValue("@eimgThumb", eimgThumb);
            cmd.Parameters.AddWithValue("@eimgFull", eimgFull);
            int id = Convert.ToInt32(cmd.ExecuteScalar());
            connection.Close();

            Size targetSize = new Size();
            targetSize.Width = 100;
            targetSize.Height = 100;

            //=========
            // Save File to Show sub directory
            // Must be in fix length
            string targetPath = Server.MapPath("./Show/");
            string fleName = (firstName.Trim() + "_" + 
		middleName.Substring(0, 1) + "_" + lastName).Trim() + ".jpg";
            File.WriteAllBytes(@targetPath + @fleName, eimgPoster);
            return id;
        }
        catch
        {
            return 0;
        }
    }
    private string CleanText(string p)
    {
        string str = string.Empty;
        if (p != string.Empty)
        {
            str = p.Replace(" ", "");
        }
        return str;
    }

    public static byte[] ImageToByte2(System.Drawing.Image img)
    {
        byte[] byteArray = new byte[0];
        using (MemoryStream stream = new MemoryStream())
        {
            img.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
            stream.Close();
            byteArray = stream.ToArray();
        }
        return byteArray;
    }

    private byte[] ResizeImageFile(byte[] imageFile, Size targetSize)
    {
        using (System.Drawing.Image oldImage = 
		System.Drawing.Image.FromStream(new MemoryStream(imageFile)))
        {
            Size newSize = CalculateDimensions
		(oldImage.Size, targetSize.Height, targetSize.Width);
            using (Bitmap newImage = new Bitmap
		(newSize.Width, newSize.Height, PixelFormat.Format24bppRgb))
            {
                using (Graphics canvas = Graphics.FromImage(newImage))
                {
                    canvas.SmoothingMode = SmoothingMode.AntiAlias;
                    canvas.InterpolationMode = InterpolationMode.HighQualityBicubic;
                    canvas.PixelOffsetMode = PixelOffsetMode.HighQuality;
                    canvas.DrawImage(oldImage, new Rectangle(new Point(0, 0), newSize));
                    MemoryStream m = new MemoryStream();
                    newImage.Save(m, ImageFormat.Jpeg);
                    return m.GetBuffer();
                }
            }
        }
    }

    private Size CalculateDimensions(Size oldSize, int targetH, int targetW)
    {
        Size newSize = new Size();
        if (oldSize.Height > oldSize.Width)
        {
            newSize.Width = targetW;
            newSize.Height = targetH;
        }
        else
        {
            //Make the image as uniform with fix size.
            newSize.Width = targetW;
            newSize.Height = targetH;
        }
        return newSize;
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        this.btnSave.Enabled = false;
        bool hasFile = false;
        string fileName = string.Empty;
        HttpPostedFile file = null;
        HttpFileCollection uploadFilCol = Request.Files;

        hasFile = true;
        HttpPostedFile file2 = uploadFilCol[0];
        if (file2.ContentLength == 0)
        {
            return;
        }
        file = uploadFilCol[0];
        Session["UploadFile2"] = FileUpload1;
        Manage.SessionFileZise = file.ContentLength.ToString();
        string fileExt = Path.GetExtension(file.FileName).ToLower();
        fileName = Path.GetFileName(file.FileName);
        if (fileName != string.Empty)
        {
            try
            {
                if (fileExt == ".jpg" || fileExt == ".gif")
                {
                    file.SaveAs(Server.MapPath("./upload/") + fileName);
                    this.ShowMessage(" " + fileName + " Successfully Uploaded", 0);

                }
                else
                {
                    // We do allow image file only
                    this.Label1.Text = "Valid files to upload is .jpg and .gif only!";
                    Page.GetType().InvokeMember("IvokeAdd", 
			System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
                    return;
                }
                this.hfUrl.Value = fileName;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        string fileImgName = "~/upload/" + fileName;
        this.Image1.ImageUrl = fileImgName;
        upImg.Update();

        if (hasFile)
        {
            this.btnSave.Enabled = true;
            Page.GetType().InvokeMember("IvokeAdd", 
		System.Reflection.BindingFlags.InvokeMethod, null, Page, null);
        }
        else
        {
            this.btnSave.Enabled = false;
        }
    }
}

So how do we make the DataGridView on each event argument Add/Detail View?

Firstly, we look at its client code:

Here it is…

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

<%@ Register Assembly="AjaxControlToolkit" 
	Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<%@ Register Src="~/UsersControl/Loader.ascx" TagName="Loader" TagPrefix="uc2" %>
<%@ Register Src="~/UsersControl/UserAccountUC.ascx" 
	TagName="UserAccountUC" TagPrefix="uc3" %>
<!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>
    <link rel="Stylesheet" type="text/css" href="css/XPStyle.css" />
    <style type="text/css">
        .AlgemBackground
        {
            background-color: #000000;
            opacity: 0.75;
            filter: alpha(opacity=70);
        }
    </style>
</head>

<script type="text/javascript">

    function ShowModal() {
        var modalPopupBehavior = $find('loader');

        modalPopupBehavior.updated();
        return true;
    }

</script>

<body class="bg">
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <div>
        <cc1:ModalPopupExtender runat="server" ID="mpeLoader" 
		BehaviorID="loader" TargetControlID="hfloader"
            PopupControlID="panelLoading" BackgroundCssClass="modalBackground" 
			DropShadow="False">
        </cc1:ModalPopupExtender>
        <asp:Panel runat="server" ID="panelLoading">
            <uc2:Loader ID="ucLoader" runat="server" />
        </asp:Panel>
        <asp:Button runat="server" ID="hfloader" Style="display: none" />
        <table width="100%">
            <tr align="center">
                <td>
                    <h2>
                        User's Maintenance
                    </h2>
                </td>
            </tr>
            <tr align="center">
                <td>
                    <asp:UpdatePanel ID="upUserGrid" runat="server" 
			UpdateMode="Conditional">
                        <ContentTemplate>
                            <asp:GridView ID="GridView1" runat="server"
                            AutoGenerateColumns="False" AllowPaging="True"
                                OnPageIndexChanging="GridView1_PageIndexChanging"
                                PageSize="3" OnRowCommand="GridView1_RowCommand"
                                EmptyDataText="*** No Record ***">
                                <EmptyDataRowStyle ForeColor="#CC3300" />
                                <Columns>
                                    <asp:TemplateField ItemStyle-HorizontalAlign="Center" 
				HeaderText="Delete">
                                        <ItemTemplate>
                                            <asp:ImageButton ID="gImgBtnDelete"
                                            runat="server" ToolTip="Delete" 
					OnClick="gImgBtnDelete_Click"
                                                OnClientClick="return confirm
                                                ('Are you sure you want to delete 
						this records?');"
                                                ImageUrl="~/Images/btn_delete.gif" />
                                        </ItemTemplate>
                                        <ItemStyle HorizontalAlign="Center" />
                                    </asp:TemplateField>
                                    <asp:BoundField DataField="UserID"
                                    HeaderText="User ID" InsertVisible="False" 
					ReadOnly="True" />
                                    <asp:BoundField DataField="Username" 
					HeaderText="User Name" />
                                    <asp:BoundField DataField="Password"
                                    HeaderText="Password" ItemStyle-ForeColor="Blue">
                                        <ItemStyle ForeColor="Blue" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="LastName" 
					HeaderText="LastName" />
                                    <asp:BoundField DataField="FirstName" 
					HeaderText="FirstName" />
                                    <asp:BoundField DataField="MiddleName" 
					HeaderText="MiddleName" />
                                    <asp:BoundField DataField="WorksiteCode"
                                    HeaderText="WorksiteCode" Visible="false" />
                                    <asp:BoundField DataField="AccessLevel" 
					HeaderText="AccessLevel" />
                                    <asp:BoundField DataField="Active" 
					HeaderText="Active" />
                                    <asp:BoundField DataField="DateCreated" 
					HeaderText="DateCreated" />
                                    <asp:BoundField DataField="DateUpdated" 
					HeaderText="DateUpdated" />
                                    <asp:BoundField DataField="Worksitedesc" 
					HeaderText="Worksitedesc" Visible="false" />
                                    <asp:BoundField DataField="Username" 
					HeaderText="User Name" />
                                    <asp:TemplateField HeaderText="Image"
                                    ItemStyle-HorizontalAlign="Justify" 
					ItemStyle-VerticalAlign="Middle">
                                        <ItemTemplate>
                                            <a href='PhotoDetail.aspx?id=
					<%# Eval("UserID") %>'>
                                                <img src='<%# "ShowImage.ashx?Id=" +
                                                Eval("UserID") %>' style="border: 
							4px solid white"
                                                    class="" alt='Deleted 
						Photo Album Number
                                                    <%# Eval("Picture") %>' /></a>
                                        </ItemTemplate>
                                        <ItemStyle HorizontalAlign="Justify" 
					VerticalAlign="Middle"></ItemStyle>
                                    </asp:TemplateField>
                                </Columns>
                            </asp:GridView>
                        </ContentTemplate>
                    </asp:UpdatePanel>
                </td>
            </tr>
        </table>
        <table width="100%">
            <tr align="center">
                <td>
                    <asp:Button ID="btnAddNew" runat="server" Text="Add New" 
			OnClick="btnAddNew_Click"
                        OnClientClick="return ShowModal();" />
                    <asp:Button ID="btnBack" runat="server" Text="Back" 
			OnClick="btnBack_Click" />
                </td>
            </tr>
        </table>
        <cc1:ModalPopupExtender runat="server" ID="mpeUserAccountUC" 
			TargetControlID="hfPopAddNew"
            PopupControlID="pnlUserAccountUC" BackgroundCssClass="AlgemBackground" 
			BehaviorID="mpeBehavior3"
            DropShadow="false" PopupDragHandleControlID="pnlUserAccountUC">
        </cc1:ModalPopupExtender>
        <asp:Panel runat="server" ID="pnlUserAccountUC" Style="display: none; 
			background-color: White;"
            Width="70%">
            <uc3:UserAccountUC runat="server" ID="ucUserAccountUC" />
        </asp:Panel>
        <asp:Button runat="server" ID="hfPopAddNew" Style="display: none" />
    </div>
    <table>
        <tr>
            <td>
                <asp:HiddenField ID="hfKeyId" runat="server" />
                <asp:HiddenField ID="hfPageIndex" runat="server" />
                <asp:HiddenField ID="hfAddNew" runat="server" />
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

And here is the Code Behind of DataGridView, they are quite easy, in fact I think the comments code on the below code pretty much explains them in enough detail. So I won't bore you with any more explanatory words, as it's clear.

So here it is…

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using Utils;

public partial class DisplayRecord : System.Web.UI.Page
{
    string sqlConnection = ConfigurationManager.ConnectionStrings
				["ConnectionString"].ConnectionString;
    SqlConnection conn;
    SqlCommand cmd;
    List<User> lstUser = new List<User>();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (SessionManager.PageIndex != string.Empty)
            {
                this.hfKeyId.Value = SessionManager.KeyPrimary;
                this.GridView1.PageIndex = Convert.ToInt32(SessionManager.PageIndex);
            }
            BindGrid();
        }
    }
    protected void btnBack_Click(object sender, EventArgs e)
    {
        Response.Redirect("DisplayRecord.aspx");
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        BindGrid();
    }

    protected void BindGrid()
    {

        List<User> lst = new List<User>();
        try
        {
            using (conn = new SqlConnection(sqlConnection))
            {
                conn.Open();

                StringBuilder sbQry = new StringBuilder();
                sbQry.Append("select ");
                sbQry.Append("UserID,Username,Password,LastName,FirstName,MiddleName,");
                sbQry.Append("WorksiteCode,AccessLevel,Active,
				DateCreated,DateUpdated,Worksitedesc, ");
                sbQry.Append("Picture,ImageFull FROM dbo.[User]");
                using (cmd = new SqlCommand(sbQry.ToString(), conn))
                {
                    cmd.CommandType = CommandType.Text;

                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            User temp = new User();
                            temp.UserID = Tools.Tools.IifInt(dr["UserID"]);
                            temp.Username = Tools.Tools.IifStr(dr["Username"]);
                            temp.Password = Tools.Tools.IifStr(dr["Password"]);
                            temp.LastName = Tools.Tools.IifStr(dr["LastName"]);
                            temp.FirstName = Tools.Tools.IifStr(dr["FirstName"]);
                            temp.MiddleName = Tools.Tools.IifStr(dr["MiddleName"]);
                            temp.WorksiteCode = Tools.Tools.IifStr(dr["WorksiteCode"]);
                            temp.AccessLevel = Tools.Tools.IifInt(dr["AccessLevel"]);
                            temp.Active = Tools.Tools.IifStr(dr["Active"]);
                            temp.DateCreated = Tools.Tools.IifDT(dr["DateCreated"]);
                            temp.DateUpdated = Tools.Tools.IifDT(dr["DateUpdated"]);
                            temp.WorksiteCode = Tools.Tools.IifStr(dr["Worksitedesc"]);
                            temp.Picture = dr["Picture"];
                            temp.ImageFull = dr["ImageFull"];
                            lst.Add(temp);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        Session["ListUser"] = lst;
        this.GridView1.DataSource = lst;
        this.GridView1.DataBind();
    }

    protected void gImgBtnDelete_Click(object sender, EventArgs e)
    {
        ImageButton iBtnDelete = sender as ImageButton;
        GridViewRow row = (GridViewRow)iBtnDelete.NamingContainer;
        int idNo = Convert.ToInt32(row.Cells[1].Text);
        bool success = DeleteEntry(idNo);
        if (success)
        {
            SessionManager.KeyPrimary = this.hfKeyId.Value;
            SessionManager.PageIndex = this.hfPageIndex.Value;
            Response.Redirect("DisplayRecord.aspx");
        }
    }

    private bool DeleteEntry(int id)
    {
        bool result = false;

        conn = new SqlConnection(sqlConnection);
        conn.Open();
        SqlTransaction trans = conn.BeginTransaction();
        using (conn)
        {
            try
            {

                string script = string.Empty;

                script = "DELETE FROM dbo.[User] WHERE UserId = " + id.ToString();
                using (cmd = new SqlCommand(script, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = trans;
                    cmd.ExecuteNonQuery();
                }

                trans.Commit();
                result = true;
            }
            catch (Exception)
            {
                trans.Rollback();
                result = false;
            }
        }
        this.upUserGrid.Update();
        return result;
    }

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {

    }
    protected void btnAddNew_Click(object sender, EventArgs e)
    {
        ucUserAccountUC.DisplayInfo(sender, e);
        mpeUserAccountUC.Show();
    }

    public void CloseModalUserAccountUC()
    {
        mpeUserAccountUC.Hide();
        mpeUserAccountUC.Dispose();
        Response.Redirect("DisplayRecord.aspx");
    }

    public void IvokeAdd()
    {
        mpeUserAccountUC.Show();
        this.upUserGrid.Update();
    }
    public void CloseModalEditUserAcct()
    {
        mpeUserAccountUC.Hide();
        mpeUserAccountUC.Dispose();
        Response.Redirect("DisplayRecord.aspx");
    }
}

Points of Interest

The author in this article describes the technical usage on how to display/store and retrieve Image data unto SQL database to gridview, and also demonstrates how images from uploaded file can be resized and saved into a table.

Featured Image Zoomer @ http://www.dynamicdrive.com/dynamicindex4/.

This script lets you view a magnified portion of any image upon moving your mouse over it. A magnifying glass appears alongside the image displaying the magnified area on demand. The user can toggle the zoom level by using the mousewheel. It's great to use on product images, photos, or other images with lots of details.

History

  • 12th October, 2011: Initial version 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Al Moje
Software Developer (Senior) ***
Philippines Philippines
MCTS - Microsoft Certified Technology Specialist.
An Accountant.
Had been developed Payroll Accounting System Application
Live in: Quezon City, Metro Manila Philippines
Could reached at email address: ag_mojedo@live.com

Comments and Discussions

 
GeneralMy vote of 4 Pinmemberkiran dangar21-Oct-11 2:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 15 Oct 2011
Article Copyright 2011 by Al Moje
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid