Wednesday, February 23, 2005

What two Libras are thinking this week...

Julie and I must be on the same wavelength this week because I was thinking about making an MP3 player purchase soon too. I need one with a great car adapter because I listen to most of my music on the road.

Tuesday, February 22, 2005

I am free!

I just installed a new hard drive in my laptop and WOW.. I feel free again! You'd think I installed a new processor and another GIG of RAM! Nope. Just a new (faster, bigger) hard drive. My old drive scared me by coughing up the blue screen a couple times last week so I ordered a new drive from Dell and started the rebuild on Saturday. I think I have this baby back to a workable computer.... just found the sound drivers today so my speakers are working again. So the old drive is still booting up in safe mode so if I forgot anything I can always go back and get it if I really have to. Much better buying a new drive for $90 than to format your old one only to realize you forgot to copy over all your great grandmother's 120th birthday pictures which you have no backups for. My registry is clean and under a GIG so I'm cooking with gas now. VS even opens in less than 5 seconds... sweeeeeet.

Tuesday, February 08, 2005

ADO.NET Calculated DateTime Expression

In our business we store user entered time values as strings separate from the date. If you're doing the same thing, here's an example of how you can create a calculated column in a DataTable that merges the date and time so you can use it in your business functions a lot easier. This will work the same regardless of whether you're storing the time value as military time or AM/PM and handles null values.
Module Module1
 Sub Main()
   
   Dim dt As New DataTable
   
   dt.Columns.Add(New DataColumn("MyDate", GetType(Date)))
   dt.Columns.Add(New DataColumn("MyTime", GetType(String)))

   Dim expr As String = _
   "SUBSTRING(CONVERT(MyDate, 'System.String'),1,LEN(CONVERT(MyDate, 'System.String'))-11) + ' ' + ISNULL(MyTime,'')"

   dt.Columns.Add(New DataColumn("MyDateTime", GetType(Date), expr))

   For i As Integer = 1 To 10
      Dim dr As DataRow = dt.NewRow
      
      If i < 5 Then
         dr(0) = DateAdd(DateInterval.Day, i, Now)
         dr(1) = i.ToString + ":00 PM"
      Else
         dr(0) = DateAdd(DateInterval.Day, 0, Now)
         dr(1) = i.ToString + ":00 AM"
      End If
      
      dt.Rows.Add(dr)
   Next

   Dim dv As New DataView(dt)
   dv.Sort = "MyDateTime"
   
   Console.WriteLine(ViewToString(dv))
   Console.ReadLine()

 End Sub

 Public Function ViewToString(ByVal view As DataView) As String
   Dim sb As New Text.StringBuilder

   For i As Integer = 0 To view.Count - 1
      Dim row As DataRow = view(i).Row
      sb.Append("Row ")
      sb.Append(i.ToString)
      sb.Append(": ")
      sb.Append(RowToString(row))
   Next
   Return sb.ToString
 End Function

 Public Function RowToString(ByVal row As DataRow) As String
   Dim sb As New Text.StringBuilder
   sb.Append(vbCrLf)

   For Each dc As DataColumn In row.Table.Columns
      If Not row.RowState = DataRowState.Deleted Then
         sb.Append(StrDup(10, " "))
         sb.Append(LSet(dc.ColumnName, 20))
         sb.Append(" : ")
         If row(dc) Is System.DBNull.Value Then
            sb.Append("<null>")
         Else
            sb.Append(row(dc).ToString)
         End If
         sb.Append(vbCrLf)
      End If
   Next
   Return sb.ToString
 End Function
End Module