Link to home
Start Free TrialLog in
Avatar of Skale
Skale

asked on

How to get datarow with LINQ in vb.net

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

Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

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
Avatar of Skale
Skale

ASKER

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 ?
SOLUTION
Avatar of Shahan Ayyub
Shahan Ayyub
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Skale

ASKER

@Shahan Ayyub

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

What's reading properties already defined in datatable ?
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).
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Skale

ASKER

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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just as Eric states.

-saige-