How to get datarow with LINQ in vb.net

Hakan
Hakan used Ask the Experts™
on
Hi,

I'm using below code to get user informations when user loads application and define it's user role.

I'm looking for is it possible to get Datarows with using LINQ in vb.net?

Thank you.

        Public Shared Sub DefineUserType()

            Dim dbDir As String = Path.Combine(Environment.CurrentDirectory, My.Settings.str_autombs__data_dir, My.Settings.str_autombs__data_db)
            Dim dtUsers As DataTable
            Dim dtRoles As DataTable

            dtUsers = AccessMethods.ReadTable(dbDir, "Select * from Users")
            Dim drs As DataRow() = dtUsers.Select("[User Name]='" + Globals.UserName + "'")

            If drs.Length > 0 Then
                dtRoles = AccessMethods.ReadTable(dbDir, "Select * from UserRoles")
                Dim drRoles As DataRow() = dtRoles.Select($"Role='{drs(0)("Role")}'")
End If

Open in new window


For access connection i'm using below code.

Namespace Utility
    Public Class AccessMethods
        Public Shared Sub ExecuteQuery(ByVal pathFile As String, ByVal Sql As String)
            Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathFile
            Using myconnection As New OleDbConnection(constring)
                myconnection.Open()
                Using cmd As New OleDbCommand(Sql, myconnection)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Shared Function ReadTable(ByVal pathFile As String, ByVal Sql As String) As DataTable
            Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathFile
            Dim dtb As New DataTable
            Using cnn As New OleDbConnection(constring)
                cnn.Open()
                Using dad As New OleDbDataAdapter(Sql, cnn)
                    dad.Fill(dtb)
                End Using
                cnn.Close()
            End Using
            Return dtb
        End Function
    End Class
End Namespace

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
the trick is to use .AsEnumerable() on your datatable object. have a look at https://www.aspsnippets.com/Articles/Using-LINQ-select-query-on-DataTable-in-C-and-VBNet.aspx

Author

Commented:
Hi Mr. Moreau

I'm a newbie at Linq and couldn't apply that my function could you please help me a

            dtUsers = AccessMethods.ReadTable(dbDir, "Select * from Users")
            Dim drs As DataRow() = dtUsers.Select("[User Name]='" + Globals.UserName + "'")

             dtRoles = AccessMethods.ReadTable(dbDir, "Select * from UserRoles")
             Dim drRoles As DataRow() = dtRoles.Select($"Role='{drs(0)("Role")}'")

Open in new window


Also in Linq if it tries to select and didn't found, is it returns boolean ?
Shahan AyyubSenior Software Engineer
Commented:
From the link given by Eric, lets convert relevant part to VB.Net first:

Dim customers = From customer In dt.AsEnumerable() _
                               Where customer.Field(Of String)("Country") = country _
                               Select New With { _
                                                              Key  .CustomerId = customer.Field(Of Integer)("CustomerId"), _
                                                              Key  .Name = customer.Field(Of String)("Name"), _
                                                              Key .Country = customer.Field(Of String)("Country") _
                                                             }

Open in new window


So in your case it will be something like:

Dim user = From user In dtUsers.AsEnumerable() _
                    Where user.Field(Of String)("User Name") = Globals.UserName _
                    Select New With { _
                                                             ' TODO: read keys using above procedure 
                                                  }

Open in new window


Here 'Select New' should create a dynamically created object with properties you will define in TODO.

Regarding your question:
> Also in Linq if it tries to select and didn't found, is it returns boolean ?

You can check: https://stackoverflow.com/questions/1191919/what-does-linq-return-when-the-results-are-empty
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
@Shahan Ayyub

For 'Select New' if it creates object with defining properties.

What's reading properties already defined in datatable ?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
If you want all the fields from the database, you don't need a "select new". Just do a "select customer" or "select user" (as per the 2 examples here above).
Commented:
Assuming that dtUsers and dtRoles are DataTables, as Eric stated, you simply need to use AsEnumerable on each in order to select the relevant row(s) based on your criteria; e.g. -
dtUsers = AccessMethods.ReadTable(dbDir, "Select * from Users")
Dim drs As IEnumerable(Of DataRow) = dtUsers.AsEnumerable().Where(Function(row) row("User Name") = Globals.UserName)

dtRoles = AccessMethods.ReadTable(dbDir, "Select * from UserRoles")
Dim drRoles As IEnumerable(Of DataRow) = dtRoles.AsEnumerable().Where(Function(row) row("Role") = drs(0)("Role").ToString())

Open in new window

Proof of concept -
Imports System.ComponentModel
Imports System.Runtime.CompilerServices

Module Module1
    Sub Main()
        Dim dtUsers As DataTable = Enumerable.Range(0, 10).Select(Function(i) New With {.ID = i, .UserName = $"UserName{i}"}).ConvertToDataTable()
        Dim dtRoles As DataTable = Enumerable.Range(0, 10).Select(Function(i) New With {.ID = i, .UserRole = $"UserRole{i}"}).ConvertToDataTable()

        Dim users As IEnumerable(Of DataRow) = dtUsers.AsEnumerable().Where(Function(row) row("UserName") = "UserName6")
        Dim roles As IEnumerable(Of DataRow) = dtRoles.AsEnumerable().Where(Function(row) row("UserRole") = "UserRole3")

        Console.WriteLine("Users: ")
        For Each user In users
            Console.WriteLine($"ID = {user("ID")}; User = {user("UserName")}")
        Next

        Console.WriteLine("Roles: ")
        For Each role In roles
            Console.WriteLine($"ID = {role("ID")}; Role = {role("UserRole")}")
        Next

        Console.ReadLine()
    End Sub
End Module

Module Extensions
    <Extension>
    Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As DataTable = New DataTable()

        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
                table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
            Else
                table.Columns.Add([property].Name, [property].PropertyType)
            End If
        Next

        Dim values(properties.Count - 1) As Object
        For Each item As T In source
            For i As Integer = 0 To properties.Count - 1
                values(i) = properties(i).GetValue(item)
            Next
            table.Rows.Add(values)
        Next

        Return table
    End Function
End Module

Open in new window

Produces the following output -Capture.PNG-saige-

Author

Commented:
Thanks for the information i'm testing but in one section i have difficiulties.

In my old approach i'm checking the "drs" length and if it's greater than 0 everything ok and it's start to look for a user role.

But now how can i define that instead of length in Linq?

dtUsers = AccessMethods.ReadTable(dbDir, "Select * from Users")
 Dim drs As IEnumerable(Of DataRow) = dtUsers.AsEnumerable().Where(Function(row) row("User Name").ToString = Globals.UserName)

If drs.Length > 0 Then
	dtRoles = AccessMethods.ReadTable(dbDir, "Select * from UserRoles")
	Dim drRoles As DataRow() = dtRoles.Select($"Role='{drs(0)("Role")}'")
	dtRoles = AccessMethods.ReadTable(dbDir, "Select * from UserRoles")
	Dim drRoles As IEnumerable(Of DataRow) = dtRoles.AsEnumerable().Where(Function(row) row("Role").ToString = drs(0)("Role").ToString())
 	GUI.pnlAppMain.Visible = True
Else
	Closing()
End If

Open in new window

Senior .Net Consultant
Top Expert 2016
Commented:
maybe "if drs.any then ..."

Commented:
Just as Eric states.

-saige-

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial