Bonski's Box

Image in DetailsView part 2

Introduction

In my previous post, we were able to make our DetailsView display our images coming from the database. We were able to do so with the help of an auxilliary web form to display the image, sort of like an image handler. While there's nothing wrong with that technique it requires an extra page to be created just so we can display the image. We can do away with the image handler and let the page draw the image itself.

Post it to me pls

We are going to change the image url /uri of ImageField row from our default page(default.aspx):

DataImageUrlFormatString="~\DisplayPhoto.aspx?emp_no={0}">

to this:

DataImageUrlFormatString="~\Default.aspx?emp_no={0}">

The source will be the page itself; upon loading of the control(DetailsView) it will call itself, this timewith  the paramater(emp_no).

Then we're going to move the streaming of image or the drawing of the image to the codebehind of the page itself:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim empNo As String = Request.QueryString("emp_no")

        If Not empNo Is Nothing Then

            If empNo.Trim <> "" Then

                ShowImage(empNo)

            End If

        End If

    End Sub

    Private Sub ShowImage(ByVal empNo As String)

        Dim commandString As String = "SELECT Photo FROM HR_EmpPhoto WHERE Emp_No=" + empNo.ToString()

        Using myConn As New SqlConnection(ConfigurationManager.ConnectionStrings("HissDBConnectionString").ToString())

            Dim myCommand As New SqlCommand(commandString, myConn)

            myConn.Open()

            Dim myReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            If myReader.Read() Then

                Dim imgBuffer As Byte() = CType(myReader.Item("Photo"), Byte())

                Response.ContentType = "image/jpeg"

                Response.BinaryWrite(imgBuffer)

            End If

            myConn.Close()

        End Using

    End Sub

That's all there is to it! You now have an DetailsView which displays images from a database. Pretty easy isn't it?

Notes

Improvements on the technique is to cache the images being displayed so that the application won't need to query for the image . Another thing to note should you use the method ExecuteScalar, it will give you the entire content of the data. While there's nothing wrong with that, getting a huge row size(uncommon for bitmaps) at one time will leave your user unsatisfied with the performance. What we can do is use ExecuteReader and pass the enum parameter SequentialAccess so that it will be given to us as a stream of bytes and use the GetBytes method to access the data into the buffer array.

With the said technique and a combination of GDI+ graphics we can integrated graphical reports to our ASP.NET application. Not just static images but dynamic one, like the ones in Cold Fusion were the report can be graphical and is even animated.

We'll continue with our endeavor into graphics using ASP.NET when we'll create our own CAPTCHA(TM) control using the same technique.

Default Values

The Basics

In VB I just love optional parameters, it's a shame that C# doesn't have that feature(instead of overloading methods of different parameters.) In T-Sql we can simulate this optional parameters in our stored procedures by using default values:
PROC dbo.pr_GetInvoiceList (@PeriodMonth INT = 1, @PeriodYear INT = 2005)
AS
SELECT *
FROM InvoiceTable
WHERE PeriodMonth = @PeriodMonth AND PeriodYear =@PeriodYear

Then you can just execute your stored procedure sans the parameters:
EXEC pr_GetInvoiceList
The purpose of the default parameter values is to make sure our stored procedure will run even when there is no parameter assigned. Having said that however, the only time I find it is useful, is during testing.

An interesting application

I have used stored procs extensibly in my application, but I was kind of frustrated when a situation arises that not all parameters will be used for either an INSERT or UPDATE procedure. I use only a single stored proc to update for example any modification to our MedicalReports table. An update might contain just updating the last modified column or update both last modified column and approvedby column. I have to resort to client side updates to handle both scenario and find it cumbersome to maintain. Then I was toying the idea of default values, but instead of having actual values, I would instead default all the values to NULL.
PROC dbo.pr_UpdateMedRep(@RepId INT, @LastModBy VARCHAR(10)=NULL, @ApprovedBy VARCHAR(10)=NULL)

The body of our stored proc will be like this:

UPDATE MedicalReportsTable SET
LastModBy = ISNULL(@LastModBy, LastModBy),
ApprovedBy = ISNULL(@ApprovedBy, ApprovedBy)
WHERE MedicalReportsTable.RepId = @RepId


Now if I want to update only the LastModify column:
pr_UpDateMedRep 1, 'User1'

Updating only ApprovedBy column:
pr_UpDateMedRep 1, NULL, 'User2'

Without worrying that either column will be updated when it shouldn't be and I'm still using stored procedure to do so.

Image in DetailsView

One of our forum members in MS Phil  has recently inquired if it's possible to display image from the database using a DetailsView control. Out of the box, it can't since the Imagefield rowfield only accepts a url for the image, but with the help of an auxilliary page we can.

Let's have our SqlDataSource setup first:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HissDBConnectionString %>"

SelectCommand="SELECT [Emp_No], [First_Name], [Last_Name] FROM [HR_EmployeeMaster] WHERE Company=@Company">

<SelectParameters>

<asp:ControlParameter Name="Company" ControlID="DropDownList1" PropertyName="SelectedValue" />

</SelectParameters>

</asp:SqlDataSource>

I have a select statement which will query all employees depending on their company which will be selected on my Dropdownlist control.

Then our DetailsView control:

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Emp_No"

DataSourceID="SqlDataSource1" Height="50px" Width="125px" AllowPaging="True">

<Fields>

 <asp:BoundField DataField="Emp_No" HeaderText="Emp_No" ReadOnly="True" SortExpression="Emp_No" />

<asp:BoundField DataField="Last_Name" HeaderText="Last_Name" SortExpression="Last_Name" />

</Fields>

</asp:DetailsView>