在VB.NET中使用或不使用LINQ格式化DataTable中的数据 [英] Format Data In DataTable With or Without LINQ in VB.NET

问题描述

我在尝试正确格式化某些数据时遇到了一些麻烦.

I'm having a little trouble trying to get some data to format properly.

我不确定LINQ在这种情况下是否有帮助，但以下是:

I'm not sure if LINQ is helpful in this case but below is:

预期输出

March 14
2:00 PM - 7:00 PM | Registration
5:30 PM - 7:00 PM | Meetup
7:30 PM - 9:00 PM | Dinner
March 15
2:00 PM - 7:00 PM | Registration
5:30 PM - 7:00 PM | Meetup

当前数据输出

March 14
2:00 PM - 7:00 PM | Registration
March 14
5:30 PM - 7:00 PM | Meetup
March 14
7:30 PM - 9:00 PM | Dinner
March 15
2:00 PM - 7:00 PM | Registration
March 15
6:00 PM - 7:00 PM | Meetup

代码结构

Imports System
Imports System.Globalization
Imports System.Data
Imports System.Data.DataSetExtensions

Public Module Module1

    Public Sub Main()
        Dim sessions = SampleTable()
        Dim ci = CultureInfo.CreateSpecificCulture("en-us")
        For Each session In sessions.AsEnumerable()

            Dim sessionID = session.Item("SessionID")
            Dim sessionDate = CDate(session.Item("SessionStartTime")).ToString("M", ci)
            Dim sessionStart = CDate(session.Item("SessionStartTime")).ToString("t", ci)
            Dim sessionEnd = CDate(session.Item("SessionEndTime")).ToString("t", ci)
            Dim sessionTitle = CStr(session.Item("SessionName"))

            Console.WriteLine("{0}", sessionDate)
            Console.WriteLine("{0} - {1} | {2}", sessionStart, sessionEnd, sessionTitle)
        Next
    End Sub

    Public Function SampleTable() As DataTable
        Dim table As New DataTable
        With table
            'Add Named Columns
            .Columns.Add("SessionID", GetType(Integer))
            .Columns.Add("SessionStartTime", GetType(Date))
            .Columns.Add("SessionEndTime", GetType(Date))
            .Columns.Add("SessionName", GetType(String))

            'Add 5 Rows of Data to test
            .Rows.Add(1, #03/14/2019 14:00:00#, #03/14/2019 19:00:00#, "Registration")
            .Rows.Add(1, #03/14/2019 17:30:00#, #03/14/2019 19:00:00#, "Meetup")
            .Rows.Add(1, #03/14/2019 19:30:00#, #03/14/2019 21:00:00#, "Dinner")
            .Rows.Add(2, #03/15/2019 14:00:00#, #03/15/2019 19:00:00#, "Registration")
            .Rows.Add(2, #03/15/2019 18:00:00#, #03/15/2019 19:00:00#, "Meetup")
        End With
        Return table
    End Function
End Module

我已经引用了其他SO问题，例如 THIS ，但我不确定是否LINQ将使预期的输出更容易获得.

I've referenced other SO questions such as THIS but I'm not sure if LINQ would make the expected output easier to obtain.

欢迎任何建议.我正在尝试为每个循环添加一个嵌套，然后如果sessionDate相同，则添加一个条件.

Any suggestions are welcomed. I'm trying to possibly add a nested for Each loop and then add a condition if the sessionDate is the same.

****** 更新 *******

****** UPDATE *******

我非常感谢您的反馈和有用的答案.我正在尝试对数据进行另一种格式更改.我必须向所有事件项添加特定的SVG图标. SVG图标特定于第一"，最后"，其余"事件.

I really appreciate the feedback and helpful answers. I'm trying to do one more format change to the data. I have to add a specific SVG Icon to all the events items. The SVG icons are specific to the First, Last, Rest of events.

我正在尝试使用提供的LINQ答案，但是我对使用它的了解并不深，但是它看起来非常易于阅读.出于本示例的目的，可以将String变量用作first/rest/last的占位符.

I'm trying to use the LINQ answer provided but I'm not well versed in using it yet but it does seem very easy to read. For the purposes of this example, A String variable can be used as place holders for the first / rest / last.

我还添加了尝试添加图标的尝试，但是我没有使用LINQ.

I've also added my attempt to try to add the icon but I was not using LINQ.

下面是更新的代码和预期的输出

预期输出

March 14
(Start SVG) 2:00 PM - 7:00 PM | Registration
(Body SVG)  5:30 PM - 7:00 PM | Meetup
(End SVG)   7:30 PM - 9:00 PM | Dinner
March 15
(Start SVG) 2:00 PM - 7:00 PM | Registration
(End SVG)   5:30 PM - 7:00 PM | Meetup

代码结构

Imports System
Imports System.Globalization
Imports System.Data
Imports System.Data.DataSetExtensions

Public Module Module1

  Public Sub Main()
    Dim ci = CultureInfo.CreateSpecificCulture("en-us")
    Dim sessionIcon = String.Empty
    'Dim Sessions = SampleTable().AsEnumerable()

    'For Each session In Sessions
    '  If Sessions.Rows.IndexOf(session) = 0 Then
    '    sessionIcon = "<svg>Start Icon</svg>"
    '  ElseIf Sessions.Rows.IndexOf(session) = (Sessions.Rows.Count - 1) Then
    '   sessionIcon = "<svg>End Icon</svg>"
    '  Else
    '    sessionIcon = "<svg>Rest Icon</svg>"
    '  End If
    'Next

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
        Key.SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
        Key.SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
        Key.SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
        Key.SessionName = x.Field(Of String)("SessionName")
    })

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
      Console.WriteLine("{0}", g.Key)
      For Each r In g
        Console.WriteLine("{0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName)
      Next
    Next
End Sub

Public Function SampleTable() As DataTable
  Dim table As New DataTable
    With table
      'Add Named Columns
      .Columns.Add("SessionID", GetType(Integer))
      .Columns.Add("SessionStartTime", GetType(Date))
      .Columns.Add("SessionEndTime", GetType(Date))
      .Columns.Add("SessionName", GetType(String))

    'Add 5 Rows of Data to test
    .Rows.Add(1, #03/14/2019 14:00:00#, #03/14/2019 19:00:00#, "Registration")
      .Rows.Add(1, #03/14/2019 17:30:00#, #03/14/2019 19:00:00#, "Meetup")
      .Rows.Add(1, #03/14/2019 19:30:00#, #03/14/2019 21:00:00#, "Dinner")
      .Rows.Add(2, #03/15/2019 14:00:00#, #03/15/2019 19:00:00#, "Registration")
      .Rows.Add(2, #03/15/2019 18:00:00#, #03/15/2019 19:00:00#, "Meetup")
    End With
  Return table
End Function
End Module

推荐答案

在使用LINQ时，就像这样

When using LINQ, like this

Public Sub Main()

    Dim ci = CultureInfo.CreateSpecificCulture("en-us")

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
        Key .SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
        Key .SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
        Key .SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
        Key .SessionName = x.Field(Of String)("SessionName")
    })

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
        Console.WriteLine("{0}", g.Key)
        For Each r In g
            Console.WriteLine("{0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName)
        Next
    Next

End Sub

添加

基本上，您的思维方式没有错. 添加该代码的位置略有不同. 因为您要处理按日期分组的每一行 必须在该循环中完成.

Basically it is not wrong with your way of thinking. The position to add that code is slightly different. Because you want to process each row grouped by date It must be done in that loop.

Public Sub Main()

    Dim ci = CultureInfo.CreateSpecificCulture("en-us")

    Dim sessions = SampleTable().AsEnumerable().[Select](Function(x) New With {
    Key .SessionDate = x.Field(Of Date)("SessionStartTime").ToString("M", ci),
    Key .SessionStartTime = x.Field(Of Date)("SessionStartTime").ToString("t", ci),
    Key .SessionEndTime = x.Field(Of Date)("SessionEndTime").ToString("t", ci),
    Key .SessionName = x.Field(Of String)("SessionName")
})

    For Each g In sessions.GroupBy(Function(s) s.SessionDate)
        Console.WriteLine("{0}", g.Key)
        For Each r In g
            Dim sessionIcon As String
            If r.Equals(g.First) Then
                sessionIcon = "<svg>Start Icon</svg>"
            ElseIf r.Equals(g.Last) Then
                sessionIcon = "<svg>End Icon</svg>"
            Else
                sessionIcon = "<svg>Body Icon</svg>"
            End If
            Console.WriteLine("{3} {0} - {1} | {2}", r.SessionStartTime, r.SessionEndTime, r.SessionName, sessionIcon)
        Next
    Next

End Sub

这篇关于在VB.NET中使用或不使用LINQ格式化DataTable中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！