How to Display Image in Gridview from Database

Display image in gridview 

In this article we will see how to display image in a gridview  from database (SQL Server).
In this case we need a Template column in a gridview and add html image (img) tag in it. you can also take asp.net image control but it will generate view state so we have taken html img tag.
if you are using asp.net image control than i would suggest that make the EnableViewState property of image control to false.
Lets see the scenario

Display Picture in a GridView from Database

let's 1st create a table and call it as Image_Table to create sample table below is the code. Here I am using Image data type of SQL server to save image into it. As we all know that SQL Server store Image as binary data in a column of datatype IMAGE.

CREATE TABLE [dbo].[Image_Table](

[id] [int] IDENTITY(1,1) NOT NULL,

[SomeText] [varchar](50) NULL,

[ImagePath] [varchar](200) NULL,

[Image_Blob] [image] NULL,

CONSTRAINT [PK_Image_Table] PRIMARY KEY CLUSTERED

(

[id] 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]

Here is the table schema in figure

 Now Create one stored procedure to fetch data from database and bind that to GridView

CREATE PROCEDURE GetImageInfoFromDB

AS

BEGIN

SELECT  id,SomeText,ImagePath,Image_Blob FROM dbo.Image_Table

END

In this example we are using HTTPHandler to fetch Images from Database. For that I have created another stored procedure which will accept one input parameter id

CREATE PROCEDURE GetImageFromDB(@id int)

AS

BEGIN

SELECT  id,SomeText,ImagePath,Image_Blob FROM dbo.Image_Table WHERE id = @id

END

I assume that some data is available into the table

  To add HTTPHandler right click on the project explorer --> AddNewItem --> Generic Handler from the templete eg given pic below and Name it as ImageHanlder.ashx

 

Now in you HTTPHandler you can add this lines of code

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

using System;

using System.Web;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

public class ImageHandler : IHttpHandler {

public void ProcessRequest(HttpContext context)

{

string strId = context.Request.QueryString["Id"];

if (!string.IsNullOrEmpty(strId))

{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

SqlDataReader sqlDr = null;

try

{

conn.Open(); //open database connection

SqlCommand sqlCmd = new SqlCommand("GetImageFromDB", conn);

sqlCmd.CommandType = CommandType.StoredProcedure;

//Add the parameter to SQLCommand object

sqlCmd.Parameters.AddWithValue("@id", strId);

sqlDr = sqlCmd.ExecuteReader();

sqlDr.Read();

context.Response.BinaryWrite((Byte[])sqlDr[3]);

context.Response.End();

}

catch (Exception ex)

{

//Handle error If occured

}

finally

{

if (sqlDr != null && !sqlDr.IsClosed)

{

sqlDr.Close(); //close SqlDataReader

}

conn.Close();//close database connection

}

}

}

public bool IsReusable {

get {

return false;

}

}

}

Now in you page in which gridView is there your code should look like this after adding templete column

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="False">

<columns>

<asp:boundfield datafield="SomeText" headertext="Header Text">

<asp:templatefield headertext="Image Form DataBase">

<itemtemplate>

<img alt="Image" src="%3C%#%20%22ImageHandler.ashx?Id=%22+%20Eval%28%22id%22%29%20%%3E" height="70px" width="70px">

</itemtemplate>

</asp:templatefield>

</asp:boundfield></columns>

</asp:gridview>

Now to bind data in a gridview you can add below lines of code in any event in which you want like Page Load event, Button Click event etc.
In C# example

private void LoadData()

{

string returnValue = string.Empty;

//Get the databse connection string from web.config file

string conString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

SqlConnection sqlConn = new SqlConnection(conString);

SqlDataReader sqlDr = null;

try

{

//Call the stored procedure using SQLDataAdapter

SqlDataAdapter salDa = new SqlDataAdapter("GetImageInfoFromDB", sqlConn);

DataSet ds = new DataSet();

salDa.Fill(ds);   // Fill the dataset

//Bind the data which in form of dataset to ggridview

GridView1.DataSource = ds;

GridView1.DataBind();

}

catch (Exception ex)

{

//Handle Error if any occured

}

finally

{

if (sqlDr != null && !sqlDr.IsClosed)

{

sqlDr.Close(); //close sqldatareader

}

//Close SQL connection

sqlConn.Close();

}

}

 If you run the application you can see in image in gridview like this

Chart Controls in Asp.net

open Visual Studio 2008. In the Data tab on the Toolbox, observe the new ‘Chart’ control.
 

To get started, let us create a sample application using the Chart control:

Step 1: Drag and drop the Chart control from the Toolbox to the Design surface of Default.aspx. You will see a ‘Column chart’ which looks similar to the following:

 

 

If you click on the ‘Source’ view of the page, you find the following mark up added to the page:

<%@ Register Assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

 

        <asp:Chart ID="Chart1" runat="server">

            <Series>

                <asp:Series Name="Series1">

                </asp:Series>

            </Series>

            <ChartAreas>

                <asp:ChartArea Name="ChartArea1">

                </asp:ChartArea>

            </ChartAreas>

        </asp:Chart>

By default the ChartType is ‘Column’. In order to change the Chart Type to either Line, Bar, Pie or any of these 25 chart types; with the Chart control selected, go to the Properties window > go to ‘Series’ > Click on the ellipse button(…) > in the ‘Series Collection Editor’, click on the ‘ChartType’ to view and select a different chart type as shown below:

 

For this demonstration, we will keep the ChartType to the default ‘Column’.

We will be binding the chart to some data. For this purpose, we will be using the SqlDataSource.

Step 2: Click on the smart tag or right click Chart > Show Smart Tag > Choose a DataSource > New Data Source… > Select Database. We will keep the default ID for SqlDataSource, SqlDataSource1 and click OK.

Step 3: On clicking OK, you will be presented with a ‘Configure Data Source’ wizard. Click on ‘New Connection’ to open the ‘Add Connection’. Type your ‘Server Name’ and ‘Select a database Name’ to connect to. Over here, I have typed (local) as the ServerName and the database I am connecting to is ‘Northwind’. Click on ‘Test Connection’ to make sure that there are no errors connecting to the server. Click Ok.

Step 4: In your ‘Configure Data Source’, click Next. An option will be displayed for saving the connection string to the configuration file. Select the checkbox ‘Yes, save this connection as:’ , type a name for the connectionstring ‘NorthwindConnectionString’ and click Next.

Step 5: In the ‘Configure Select Statement’ > select ‘Specify a Custom SQL Statement or stored procedure’ > click Next

 

Type the SQL query to ‘list the count of products in a category’ as shown below:

SELECT CategoryName, COUNT(*) AS ProductCount FROM Products JOIN Categories ON Products.CategoryID = Categories.CategoryID GROUP BY CategoryName

 

Click Next > Finish.

Step 6: Select the Chart control > Go to the Properties window > ChartAreas property and click the ellipse (…) button to open the ChartArea collection editor. Go to the ‘Axes’ property in the ChartArea Collection Editor and click the ellipse (…) button to open the Axis collection editor as shown below:

 

Set the ‘Title’ of the X axis to ‘Categories’ and the ‘Title’ of the Y axis to ‘Product Count’. Click OK to close the Axis Collection Editor and an OK again to close the ChartArea Collection Editor.

With the Chart control selected, go to the Series property in the Properties window and click the ellipse (…) button to open the Series collection editor. Go to the Data Source section and add the XValueMember as ‘CategoryName’ and YValueMembers to ‘ProductCount’. Also set the ‘IsValueShownAsLabel’ to True to display the value of ProductCount on the columns. Click OK

 

 

Step 7: With the properties set, run the application. You will see a chart as displayed below: