Retrieving Images from SqlServer and displaying in a DataGrid

Print Add To Favorites Email To Friend Rate This Article
Retrieving Images from SqlServer and displaying in a DataGrid - ASP .NET

page 1 of 1

Published: 22 Oct 2003

Unedited - Community Contributed

Abstract
Before reading this article, I assume that the reader has read my two previous articles which talks about Inserting Images to Sql Server in ASP .NET and Retreiving Images from Sql Server in ASP .NET. In this article, we will discuss about populating a datagrid with images. Of course, the images will be coming from the database.

by Jesudas Chinnathampi (Das)
Feedback

Average Rating:

Views (Total / Last 10 Days): 59815/ 67

Retrieving Images from SqlServer and displaying in a DataGrid - ASP .NET

Written on: May, 15th 2002.

Introduction

Before reading this article, I assume that the reader has read my two previous articles which talks about Inserting Images to Sql Server in ASP .NET and Retreiving Images from Sql Server in ASP .NET.

In this article, we will discuss about populating a datagrid with images. Of course, the images will be coming from the database.

We will be learning the following aspects in this article.

Formatting or populating a URL dynamically
How to read images from a sql server
How to display the images in a DataGrid which is retrievied from a SqlServer

We will have two ASPX pages, one for displaying the datagrid and another one for pulling the images from sql server datbase. The one which displays the datagrid just contains the TemplateColumns, and ItemTemplates. The DataGrid will have many columns. We will just concentrate on the column which displays the image.

Code to show the image from sql server (DataGrid part).

    <asp:TemplateColumn HeaderText="Image">
        <ItemTemplate>
            <asp:Image
            Width="150" Height="125"
            ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "PersonID")) %>'
            Runat=server />
        </ItemTemplate>
    </asp:TemplateColumn>

And the method FormatURL (A Server side Function) is as follows.

    Function FormatURL(strArgument) as String
        Return ("readrealimage.aspx?id=" & strArgument)
    End Function

And the method FormatURL (A Server side Function) is as follows.

    Function FormatURL(strArgument) as String
        Return ("readrealimage.aspx?id=" & strArgument)
    End Function

How it works?

In the above code, we have an ItemTemplate column. And we have a <asp:Image web server control which is equivalent to the <img> tag. In the ImageURL property, we invoke a server side method, FormatURL, which populates the image source. In the FormatURL, you can see that, we invoke another page called readrealimages.aspx, which actually pulls the image from the sql server. The technique for retrieving the image is the same that was discussed in my other article, which deals with
Retreiving Images from Sql Server in ASP .NET. Let us take a took at the content of readimage.aspx.

Code to show the image from sql server (Talking with the Database part).

    Public Sub Page_Load(sender As Object, e As EventArgs)

        Dim strImageID as String = Request.QueryString("id")

        ' Create Instance of Connection and Command Object
        Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
        Dim myCommand As New SqlCommand("Select PersonImageType, PersonImage from das_person_real_images Where PersonID=" & strImageID, myConnection)

        ' I have used the select statement. But it would be much much better, if you
        'could write a small stored procedure which contains the above sql statement.
        ' Mark the Command as a SPROC (in case, if you wrote the stored procedure
        'myCommand.CommandType = CommandType.StoredProcedure

        Try
            myConnection.Open()
            Dim myDataReader as SqlDataReader
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
            Do While (myDataReader.Read())
                Response.ContentType = myDataReader.Item("PersonImageType")
                Response.BinaryWrite(myDataReader.Item("PersonImage"))
            Loop
            myConnection.Close()
        Catch SQLexc As SqlException

        End Try

    End Sub

How it works?

In the Page_Load event, we first retrieve the querystring (ID of the Person). Then we execute a Sql statement which returns the imagetype and the imagecontent from the database. Retrieving the image and writing to the browser is same as we discussed in Retreiving Images from Sql Server in ASP .NET.

Test this Script

Advantages and Disadvantages

The greatest advantage is that, since we store the images in database, more security is their. And the greatest disadvantage is that, we are making a database call for each image. So, if we have thousands of rows then, this process of retrieving image every time will result in low response time.

Download the code

Click here to download the datagridimages.aspx page
Click here to download the ReadRealImage.aspx page
Click here to download the Stored Procedures and table scripts

Conclusion

In the DataGrid, we do not have a hyperlink to click on the image. You can add another column which has a link that will be display the full image once it is clicked. You can work on this as an enhancement. If you have any trouble in getting this done, don't hesitate to email me.

Links

Inserting Image to SqlServer
Retrieving Images from SqlServer
How to Upload a File?
Textbox Web Server Control

Send your comments to das@aspalliance.com