CODEWITHSUNDEEP
vb.netlinqdatatable
adaam
adaam

Reputation: 3706

Converting DataTable to Dictionary using LINQ in VB.NET

I have a DataTable which pulls out results from a SQL table using this SQL:

SELECT firstName,lastName,companyName,address1,countryCode FROM dbo.users

I would like to convert this DataTable to a Dictionary-esque structure with the key for each field above being the column name and the value being the value for each DataRow for that column. I have a vague idea of how to do it in C# but the VB.NET LINQ syntax is completely different so I'm struggling to figure out how to do this..

Structure (visualized in JSON) would be something like this:

[
    {
        "firstName": "Adam",
        "lastName": "Smith",
        "address1": "123 Old St",
        "companyName": "Fake Company",
        "countryCode": "us"
    },
    {
        "firstName": "Paul",
        "lastName": "Jones",
        "address1": "474 Old St",
        "companyName": "Fake Company",
        "countryCode": "gb"
    }
]

Upvotes: 3

Views: 10074

Answers (1)

Mark
Mark

Reputation: 8160

A Dictionary(Of String, String) would be about as Dictionary-esque as you could get:

Dim result As IEnumerable(Of Dictionary(Of String, String)) =
    From r As DataRow In myDataTable.AsEnumerable()
    Select New Dictionary(Of String, String) From {
        { "firstName", r.Field(Of String)("firstName") },
        { "lastName", r.Field(Of String)("lastName") },
        { "address1", r.Field(Of String)("address1") },
        { "companyName", r.Field(Of String)("companyName") },
        { "countryCode", r.Field(Of String)("countryCode") }
    }

You could also use an anonymous type:

Dim result2 =
    From r In myDataTable.AsEnumerable()
    Select New With {
        .firstName = r.Field(Of String)("firstName"),
        .lastName = r.Field(Of String)("lastName"),
        .address1 = r.Field(Of String)("address1"),
        .companyName = r.Field(Of String)("companyName"),
        .countryCode = r.Field(Of String)("countryCode")
    }

Or, as suggested in the comments, you could create a class and return that:

Public Class User
    Public Property firstName As String
    Public Property lastName As String
    Public Property address1 As String
    Public Property companyName As String
    Public Property countryCode As String
End Class

Dim result3 =
    From r In myDataTable.AsEnumerable()
    Select New User With {
        .firstName = r.Field(Of String)("firstName"),
        .lastName = r.Field(Of String)("lastName"),
        .address1 = r.Field(Of String)("address1"),
        .companyName = r.Field(Of String)("companyName"),
        .countryCode = r.Field(Of String)("countryCode")
    }

Upvotes: 6

Related Questions