Retrieve images from SQL Server database and bind to Gridview

In the previous blog post I have explained how to upload images to SQL Server Database. In this post I am going to show you how you can retrieve images stored in SQL Server Database and bind it to gridview. I am going to handler to bind image in the gridview. Start by adding a handler at the root directory of your project and call it as ShowPhoto.ashx. We are going to use the same table that we used to store images in the database in the previous post. Additionally, a couple of stored procedures are needed to bind images to gridview. Refer previous post for tables

Stored procedure to get picture by Id.

CREATE PROCEDURE [dbo].[sprocGetPictureById]
@ImageId int
AS
BEGIN
SELECT [Picture]
  FROM [Pictures] WHERE ImageId = @ImageId
END

Stored procedure to get all pictures.

CREATE PROCEDURE [dbo].[sprocGetPictures]

AS
BEGIN
SELECT [ImageId]
      ,[ImageName]
      ,[ImageSize]
  FROM [Pictures]
END

ShowPhoto.ashx

<%@ WebHandler Language="C#" Class="ShowPhoto" %>

using System;
using System.Web;
using System.IO;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class ShowPhoto : IHttpHandler
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    object img;
    public void ProcessRequest(HttpContext context)
    {
        int imageId, newWidth = 100, newHeight = 100;
        double scale = 0;
        if (context.Request.QueryString["id"] != null)
            imageId = Convert.ToInt32(context.Request.QueryString["id"]);
        else
            throw new ArgumentException("No parameter specified");

        //Code to Read Thumbnail Image from the database
        System.Drawing.Image image = System.Drawing.Image.FromStream(ShowPicture(imageId));
        if (image.Height < image.Width)
        {
            scale = ((double)newHeight) / image.Width;
        }
        else
        {
            scale = ((double)newWidth) / image.Height;
        }

        // Set new width and height.
        newWidth = (int)(scale * image.Width);
        newHeight = (int)(scale * image.Height);

        // create a new image from original
        System.Drawing.Image thumbnail = new Bitmap(image, newWidth, newHeight);
        System.Drawing.Graphics graphic = System.Drawing.Graphics.FromImage(thumbnail);

        graphic.InterpolationMode = InterpolationMode.HighQualityBicubic;
        graphic.SmoothingMode = SmoothingMode.HighQuality;
        graphic.PixelOffsetMode = PixelOffsetMode.HighQuality;
        graphic.CompositingQuality = CompositingQuality.HighQuality;

        graphic.DrawImage(image, 0, 0, newWidth, newHeight);

        ImageCodecInfo[] info;
        info = ImageCodecInfo.GetImageEncoders();
        EncoderParameters encoderParameters;
        encoderParameters = new EncoderParameters(1);
        encoderParameters.Param[0] = new EncoderParameter(Encoder.Quality, 100L);
        context.Response.ContentType = "image/jpeg";
        thumbnail.Save(context.Response.OutputStream, info[1], encoderParameters);
    }

    public Stream ShowPicture(int imageId)
    {
        SqlCommand cmd = new SqlCommand("sprocGetPictureById", sqlcon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ImageId", imageId);
        sqlcon.Open();
        try
        {
            img = cmd.ExecuteScalar();
            return new MemoryStream((byte[])img);
        }
        catch
        {
            return null;
        }
        finally
        {
            sqlcon.Close();
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}

Next, right-click the root of your project and click Add new item -> Web Form and give it a name (in our case RetrieveImage.aspx)

RetrieveImage.aspx

<div>
    <asp:GridView ID="gvPictures" runat="server" AutoGenerateColumns="False"
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="ImageId" HeaderText="ID" />
            <asp:BoundField DataField="ImageName" HeaderText="Image Name" />
            <asp:BoundField DataField="ImageSize" HeaderText="Image Size(Kb)" />
            <asp:TemplateField HeaderText="Picture">
                <ItemTemplate>
                    <img id="Img1" alt='<%#Eval("ImageName") %>' runat="server" src='<%# "~/ShowPhoto.ashx?id=" + Eval("ImageId") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</div>

RetrieveImage.aspx.cs

SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            gvPictures.DataSource = BindPictures();
            gvPictures.DataBind();
        }
    }


private DataTable BindPictures()
    {
        DataTable dtPictures = new DataTable();
        SqlDataAdapter adpPictures = new SqlDataAdapter("sprocGetPictures", sqlcon);
        adpPictures.SelectCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            adpPictures.Fill(dtPictures);
            return dtPictures;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }

Upload images to SQL Server Database using asp.net

There are several ways to upload images from an asp.net application. Uploading images or pictures to the directory is quite simple and easy in asp.net but another approach is to use SQL server database. To save an image file in the database, first create a table with columns to hold the ImageName, ImageSize, Picture. The datatype for the Picture column should be image and for other columns  use nvarchar. The image is saved in the database in binary format. In order to save an image file in the database it should be first converted to byte.

Create a Pictures table in the database Album.

CREATE TABLE [dbo].[Pictures](
[ImageId] [int] IDENTITY(1,1) NOT NULL,
[ImageName] [nvarchar](100) NOT NULL,
[ImageSize] [int] NULL,
[Picture] [image] NULL,
 CONSTRAINT [PK_Pictures] PRIMARY KEY CLUSTERED
(
[ImageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Stored Procedure to save image to the database table Pictures.

CREATE PROCEDURE sprocSaveImage
@ImageName nvarchar(100),
@ImageSize int,
@Picture image
AS
BEGIN
INSERT INTO [Album].[dbo].[Pictures]
           (ImageName
           ,ImageSize
           ,Picture)
     VALUES
           (@ImageName
           ,@ImageSize
           ,@Picture)
END
GO

Default.aspx

<div>
    Image:&nbsp;
    <asp:FileUpload ID="fileImage" runat="server" />
    &nbsp;
    <asp:Button ID="btnUpload" runat="server" Text="Upload"
        onclick="btnUpload_Click" />
</div>

Default.aspx.cs

using System.Data;
using System.Data.SqlClient;

SqlConnection sqlcon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Album.MDF;Integrated Security=True;User Instance=True");
    protected void Page_Load(object sender, EventArgs e)
    {

    }
protected void btnUpload_Click(object sender, EventArgs e)
    {
        HttpPostedFile _pfImage = fileImage.PostedFile;
        string imgName = _pfImage.FileName;
        int imgSize = _pfImage.ContentLength;
        byte[] imageOriginal = new byte[imgSize];
        _pfImage.InputStream.Read(imageOriginal, 0, imgSize);

        SaveImageToDatabase(imgName, imgSize, imageOriginal);
    }
    private void SaveImageToDatabase(string imgName, int imgSize, byte[] imageOriginal)
    {
        SqlCommand sqlcmd = new SqlCommand("sprocSaveImage", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.Parameters.AddWithValue("ImageName", imgName);
        sqlcmd.Parameters.AddWithValue("ImageSize", imgSize);
        sqlcmd.Parameters.AddWithValue("Picture", imageOriginal);
        if (sqlcon.State == ConnectionState.Closed)
        {
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
        }
        if (sqlcon.State == ConnectionState.Open)
        {
            sqlcon.Close();
        }
    }

In the next post I will explain how to retrieve images from SQL server database and bind to gridview.

Add/Remove Gridview rows dynamically in asp.net

In this article i am going to explain how to add or remove rows from Gridview dynamically. First, a default row in the gridview is added with controls bound to it when the page loads. Then, the user can add or remove any number of rows to the gridview. Every time a new row is added the gridview is loaded from the ViewState. It is important to preserve the state of the DropDownList while adding another row therefore the selected value of the DropDownList should be bound with the DataField.

Default.aspx

<div>
 <asp:GridView ID="gvBooks" runat="server" ShowFooter="True"
                                AutoGenerateColumns="False" HeaderStyle-CssClass="header-column" onrowcommand="gvBooks_RowCommand"
                                onrowdatabound="gvBooks_RowDataBound" BorderStyle="None" GridLines="None">
                                <Columns>
                                    <asp:TemplateField HeaderText="Book Name">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtBookName" runat="server">
                                            </asp:TextBox>
                                        </ItemTemplate>
                                        <FooterTemplate>
                                            <asp:LinkButton ID="lbNewBook" runat="server" CausesValidation="false" CommandName="NewBook">New</asp:LinkButton>
                                        </FooterTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Author">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtAuthor" runat="server" Text='<%#Bind("Author") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Description">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtDescription" runat="server" Text='<%#Bind("Description") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Genre">
                                        <ItemTemplate>
                                            <asp:DropDownList ID="ddlGenre" runat="server" AppendDataBoundItems="true">
                                            <asp:ListItem>Choose...</asp:ListItem>
                                            <asp:ListItem>Computer</asp:ListItem>
                                            <asp:ListItem>Fantasy</asp:ListItem>
                                           <asp:ListItem>Horror</asp:ListItem>
                                           <asp:ListItem>Comedy</asp:ListItem>
                                            </asp:DropDownList>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Price">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtPercentage" runat="server" Width="100px" Text='<%#Bind("Price") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Publish Date">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtPublishDate" runat="server" Width="100px" Text='<%#Bind("PublishDate") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField>
                                        <ItemTemplate>
                                            <asp:ImageButton ID="imgbtnDeleteBook" runat="server" CausesValidation="false" ImageUrl="~/App_Themes/Red/Images/cross.png" CommandName="DeleteBook" />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                </Columns>
                            </asp:GridView>
                        </div>

Default.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
                 AddBookDefault();
        }
}
private void AddBookDefault()
    {
        DataTable dt = new DataTable();
        DataRow dr = null;
        dt.Columns.Add(new DataColumn("BookName", typeof(string)));
        dt.Columns.Add(new DataColumn("Author", typeof(string)));
        dt.Columns.Add(new DataColumn("Description", typeof(string)));
        dt.Columns.Add(new DataColumn("Genre", typeof(string)));
        dt.Columns.Add(new DataColumn("Price", typeof(string)));
        dt.Columns.Add(new DataColumn("PublishDate", typeof(string)));

        dr = dt.NewRow();

        dr["BookName"] = string.Empty;
        dr["Author"] = string.Empty;
        dr["Description"] = string.Empty;
        dr["Genre"] = string.Empty;
        dr["Price"] = string.Empty;
        dr["PublishDate"] = string.Empty;

        dt.Rows.Add(dr);
        //dr = dt.NewRow();
        //Store the DataTable in ViewState

        ViewState["CurrentTable"] = dt;

        gvBooks.DataSource = dt;
        gvBooks.DataBind();
    }
protected void gvBooks_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        int rowIndex = 0;
        DataTable dt = (DataTable)ViewState["CurrentTable"];

        if (e.CommandName == "NewBook")
        {
            DataRow rw = dt.NewRow();
            dt.Rows.Add(rw);

            //Set Previous Data
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count - 1; i++)
                {
                    TextBox txtBookName = (TextBox)gvBooks.Rows[rowIndex].Cells[0].FindControl("txtBookName");
                    TextBox txtAuthor = (TextBox)gvBooks.Rows[rowIndex].Cells[1].FindControl("txtAuthor");
                    TextBox txtDescription = (TextBox)gvBooks.Rows[rowIndex].Cells[2].FindControl("txtDescription");
                    DropDownList ddlGenre = (DropDownList)gvBooks.Rows[rowIndex].Cells[3].FindControl("ddlGenre");
                    TextBox txtPrice = (TextBox)gvBooks.Rows[rowIndex].Cells[4].FindControl("txtPrice");
                    TextBox txtPublishDate = (TextBox)gvBooks.Rows[rowIndex].Cells[5].FindControl("txtPublishDate");

                    dt.Rows[i]["BookName"] = txtBookName.Text;
                    dt.Rows[i]["Author"] = txtAuthor.Text;
                    dt.Rows[i]["Description"] = txtDescription.Text;
                    dt.Rows[i]["Genre"] = ddlGenre.SelectedItem.Text;
                    dt.Rows[i]["Price"] = txtPrice.Text;
                    dt.Rows[i]["PublishDate"] = txtPublishDate.Text;

                    rowIndex++;
                }
            }
        }
        else if (e.CommandName == "DeleteBook")
        {
            GridViewRow gvr = (GridViewRow)((ImageButton)e.CommandSource).NamingContainer;
            dt.Rows.RemoveAt(gvr.RowIndex);
        }

        gvBooks.DataSource = dt;
        gvBooks.DataBind();
    }

protected void gvBooks_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DropDownList ddlGenre = (DropDownList)e.Row.Cells[3].FindControl("ddlGenre");
         
            ddlGenre.SelectedValue = DataBinder.Eval(e.Row.DataItem, "Genre").ToString();
        }
    }

Dynamic Gridview with TemplateColumn

There are situations when you want to create the columns of the Gridview dynamically because the number of columns and rows are unknown until runtime. Thankfully, ASP.Net has a nice feature to overcome this challenge. Using a seperate class which inherits the ITemplate interface lets you create the controls and the data to be bound to the Gridview at runtime.

Create a class called GridviewTemplate which also inherits the ITemplate interface, in the App_Code folder of your project.

GridviewTemplate.cs

public class GridViewTemplate : ITemplate
{
    ListItemType _templateType;
    string _columnName;

    //Constructor where we define the template type and column name.
    public GridViewTemplate(ListItemType type, string colname)
    {
        _templateType = type;
        _columnName = colname;
    }

    void ITemplate.InstantiateIn(System.Web.UI.Control container)
    {
        switch (_templateType)
        {
            case ListItemType.Header:
                Label lbl = new Label();
                lbl.Text = _columnName;
                container.Controls.Add(lbl);
                break;

            case ListItemType.Item:
                TextBox tb1 = new TextBox();
                tb1.DataBinding += new EventHandler(tb1_DataBinding);
                tb1.Columns = 8;
                container.Controls.Add(tb1);
                break;

            //EditItem is optional
            case ListItemType.EditItem:
                DropDownList ddl = new DropDownList();
                ddl.ID = "ddl" + _columnName;
                container.Controls.Add(ddl);
                break;

            case ListItemType.Footer:
                CheckBox chkColumn = new CheckBox();
                chkColumn.ID = "Chk" + _columnName;
                container.Controls.Add(chkColumn);
                break;
        }
    }

    /// <summary>
    /// This is the event, which will be raised when the binding happens.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void tb1_DataBinding(object sender, EventArgs e)
    {
        TextBox txtdata = (TextBox)sender;
        GridViewRow container = (GridViewRow)txtdata.NamingContainer;
        object dataValue = DataBinder.Eval(container.DataItem, _columnName);
        if (dataValue != DBNull.Value)
        {
            txtdata.Text = dataValue.ToString();
        }
    }
}

Default.aspx

<div>
<asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False">
</asp:GridView>
</div>

Default.aspx.cs

//Global declaration of variables on the page

const string PRODUCTNAME = "PRODUCT NAME";
const string ID = "ID";
const string PRICE = "PRICE";

protected void Page_Load(object sender, EventArgs e)
    {
        BindProducts();
    }

    private void BindProducts()
    {
        DataTable dt = new DataTable();
     
        DataColumn dc = new DataColumn(ID, typeof(System.Int32));
        dc.AutoIncrement = true;
        dt.Columns.Add(dc);

        dc = new DataColumn(PRODUCTNAME, typeof(System.String));
        dt.Columns.Add(dc);

        dc = new DataColumn(PRICE, typeof(System.Double));
        dt.Columns.Add(dc);

        Random r = new Random();

        for (int idx = 0; idx < 10; idx++)
        {
            DataRow dr = dt.NewRow();

            dr[PRODUCTNAME] = "Product " + Convert.ToString((idx + 1));
            dr[PRICE] = Convert.ToDouble(Math.Round(r.NextDouble() * 100 + 25, 2));

            dt.Rows.Add(dr);
        }

        foreach (DataColumn col in dt.Columns)
        {
            TemplateField tf= new TemplateField();
            tf.HeaderTemplate = new GridViewTemplate(ListItemType.Header, col.ColumnName);
            tf.ItemTemplate = new GridViewTemplate(ListItemType.Item, col.ColumnName);
            gvProducts.Columns.Add(tf);
        }

        gvProducts.DataSource = dt;
        gvProducts.DataBind();
    }

Grouping Gridview cells in ASP.NET

Grouping cells in a Gridview is important to merge related items. But, merging related items has not been easy from the code-behind. Thankfully, using Jquery merging of gridview cells can be done effortlessly.


Add reference to the jquery library from the Microsoft Content Delivery Network or Google Libraries API,

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
(or alternatively)
<script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.4.min.js"></script>


And in the body place two Gridviews, one for listing the products with no grouping and the other for listing the products with grouping.

Default.aspx

<script type="text/javascript">

function GroupGridViewCells(gridviewId) {

            var rowNum = $(gridviewId + " tr").length;

            var colNum = $(gridviewId + " tr:eq(0)>th").length;

            var cell = null;

            var previouscell = null;

            // Begin to loop from the second row to the end
            for (var col = 0; col < colNum; ++col) {

                for (var row = 1; row < rowNum; ++row) {

                    cell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")").first();

                    if (row == 1) {
                        previouscell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")").first();
                        previouscell.attr("rowspan", "1");
                    }
                    else {

                        if (cell.html() == previouscell.html()) {

                            previouscell.attr("rowspan", parseInt(previouscell.attr("rowspan") + 1));
                            cell.css("display", "none");
                        }
                        else {

                            previouscell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")");
                            previouscell.attr("rowspan", "1");
                            previouscontent = previouscell.html();
                        }
                    }
                }
            }
        }

$(document).ready(function () {
        GroupGridViewCells("#groupedGridView");
}
</script>

<div>
        <table>
            <tr>
                <td style="padding: 20px">
                    <h3>
                        GridView(without grouping)</h3>
                    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
                        OnPageIndexChanged="GridView1_PageIndexChanged"
                        OnPageIndexChanging="GridView1_PageIndexChanging">
                        <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                        <RowStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                    </asp:GridView>
                </td>
                <td style="padding: 20px">
                    <h3>
                        GridView(with grouping)</h3>
                    <asp:GridView ID="groupedGridView" runat="server" AllowPaging="True"
                        OnPageIndexChanged="groupedGridView_PageIndexChanged"
                        OnPageIndexChanging="groupedGridView_PageIndexChanging">
                        <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                        <RowStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                    </asp:GridView>
                </td>
            </tr>
        </table>
    </div>

Default.aspx.cs

using System.Data;

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindSortedProducts(GridView1);
                BindSortedProducts(groupedGridView);
            }
        }

private void BindSortedProducts(GridView gridView)
        {
            const string ProductsDataViewStateId = "ProductsData";
            DataTable dt = ViewState[ProductsDataViewStateId] as DataTable;

            if (dt == null)
            {
                dt = new DataTable();
                dt.Columns.Add("Product Name", typeof(string));
                dt.Columns.Add("Category", typeof(int));
                dt.Columns.Add("Price", typeof(double));
                Random r = new Random();

                for (int i = 1; i <= 100; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["Product Name"] = "Product" + r.Next(1, 5);
                    dr["Category"] = Convert.ToInt32(r.Next(1, 5));
                    dr["Price"] = Convert.ToDouble(Math.Round(r.NextDouble() * 100 + 25, 2));
                    dt.Rows.Add(dr);
                }

                ViewState[ProductsDataViewStateId] = dt;
            }

            // Sort by ProductName and Category
            dt.DefaultView.Sort = "Product Name,Category";
         
            gridView.DataSource = dt;
            gridView.DataBind();
        }


        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            generalGridView.PageIndex = e.NewPageIndex;
        }

        protected void GridView1_PageIndexChanged(object sender, EventArgs e)
        {
            BindSortedProducts(GridView1);
        }

        protected void groupedGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            groupedGridView.PageIndex = e.NewPageIndex;
        }

        protected void groupedGridView_PageIndexChanged(object sender, EventArgs e)
        {
            BindSortedProducts(groupedGridView);
        }