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.
For access connection i'm using below code.
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
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
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
ASKER
Hi Mr. Moreau
I'm a newbie at Linq and couldn't apply that my function could you please help me a
Also in Linq if it tries to select and didn't found, is it returns boolean ?
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")}'")
Also in Linq if it tries to select and didn't found, is it returns boolean ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Shahan Ayyub
For 'Select New' if it creates object with defining properties.
What's reading properties already defined in datatable ?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just as Eric states.
-saige-
-saige-