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
