How to display Duration only Hours:Minutes:Second in Gridview Asp.Net by using LINQ to SQL?

Question

Hi again Guys, I want to display the duration only Hour, Minutes, and Second in data Gridview by Subtract TimeCheckOut from TimeCheckIn in ASP.NET using LINQ to SQL

Here is code behind:

Dim db = new MyDataContext
Dim user = from u in db.Employees select IDNumber = u.IDNumber, _
           FirstName = u.firstName, LastName = u.lastName, TimeCheckIn = u.timeCheckIn, _
           TimeCheckOut = u.timeCheckOut, Duration = u.timeCheckIn
Gridview1.DataSource = user  
Gridview1.DataBind()

Code on page:

  <asp:GridView ID="GridView1" runat="server" Width="100%" 
    AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="IDNumber" HeaderText="ID Number" ReadOnly="True" />
        <asp:BoundField DataField="FirstName" HeaderText="First Name" ReadOnly="True"/>
        <asp:BoundField DataField="LastName" HeaderText="Last Name" ReadOnly="True"/>
        <asp:BoundField DataField="TimeCheckIn" HeaderText="Time Check In" ReadOnly="True"/>
        <asp:BoundField DataField="TimeCheckOut" HeaderText="Time Check Out" ReadOnly="True" />
        <asp:TemplateField HeaderText="Duration">
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# FieldDisplayDuration(Eval("Duration")) %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Here is my helper for FieldDisplayDuration:

Protected Function FieldDisplayDuration(ByVal Duration As DateTime) As String
    Dim rtn As String = "DefaultValue"
    Dim dif As TimeSpan = DateTime.Now.Subtract(Duration)
        rtn = dif.Hours & " hours, " & dif.Minutes & " minutes, " & dif.Seconds & " seconds. "
    Return rtn
End Function

In Line 3 in Helper function, Dim dif as TimeSpan = DateTime.Now.Subtract(Duration) which give only the duration of Hour, Minute, and Second from TimeCheckIn until DateTime.Now. However, I want to have the duration from TimeCheckIn until TimeCheckOut only in Hour, Minute, and Second. I know that the FieldDisplayDuration function is totally wrong logic, but I just want you to get my point only, and also it could be the code sample for those who want to calculate the duration of the employee from the hire date. Finally, Let's get back to TimeSpan by Subtract TimeCheckOut from TimeCheckIn in gridview problem, How can I do that? Please give me some clue.. Thanks you so much...

Answer 1

It is possible to just subtract the date and time to get the Timespan so you should be able to use

 Text='<%# FieldDisplayDuration(Eval("TimeCheckIn"), Eval("TimeCheckOut")) %>'

and something like this function:

Protected Function FieldDisplayDuration(ByVal CheckIn As DateTime, ByVal CheckOut as DateTime) As String    
    Dim rtn As String = "DefaultValue"    
    Dim dif As TimeSpan = CheckOut - CheckIn        
    rtn = dif.Hours & " hours, " & dif.Minutes & " minutes, " & dif.Seconds & " seconds. "    
    Return rtnEnd 
Function

Though I would probably use a string format on the rturn line and do this...

rtn = string.Format("{0} hours, {1} minutes, {2} seconds.", dif.Hours, dif.Minutes, difSeconds)

As I think it is easier to read.

Answer 2

You can put the logic to compute the duration right in your Select clause, like so:

Dim user = from u in db.Employees select IDNumber = u.IDNumber, _
       FirstName = u.firstName, LastName = u.lastName, TimeCheckIn = u.timeCheckIn, _
       TimeCheckOut = u.timeCheckOut, Duration = u.timeCheckOut.Subtract(u.timeCheckIn)

Then, you only need to format the resulting Timespan.

Alternatively, you could do it all in the select, or even call a function, so have something like

Duration = FormatDuration(u.timeCheckOut.Subtract(u.timeCheckIn)

or

Duration = FormatDuration(u.timeCheckIn, u.timeCheckOut)\

If you did this, and assuming FormatDuration returned a string, you would be able to do away with the column template entirely