Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query datatable in LINQ

Posted on 2016-09-21
6
Medium Priority
?
82 Views
Last Modified: 2016-10-16
How can query datatable using linq and set the return value in a new datatable?

Dim dt As DataTable = From o In datableorder.Select 'Where u.Item("OrderHeader_Id") = Wherever value I want to pass to

datableorder

OrderHeader_Id      username itemno      zip
1            john       a1111      11111
1            john       b1111  22222
2            john       c1111      33333
2            john       c1111      11111
3 etc
0
Comment
Question by:jagr12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 41809489
Let me clarify, you want to update a value in existing datatable?
0
 

Author Comment

by:jagr12
ID: 41809491
No, If I pass OrderHeader_Id 1 it should return only

1            john       a1111      11111
1            john       b1111  22222
0
 

Author Comment

by:jagr12
ID: 41809492
like Dim dt As DataTable = From o In datableorder.Select 'Where u.Item("OrderHeader_Id") = 1
this new dt datable should return only
1            john       a1111      11111
1            john       b1111  22222
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:jagr12
ID: 41809496
sorry, with header
like Dim dt As DataTable = From o In datableorder.Select 'Where u.Item("OrderHeader_Id") = 1
this new dt datable should return only

OrderHeader_Id      username itemno      zip
1            john       a1111      11111
1            john       b1111  22222
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 1000 total points
ID: 41809514
try

var query= from ord in datatableorder whereord.OrderHeader_Id.Equals(val) select ord;

DataTable dt = query.CopyToDataTable<DataRow>();

Open in new window


or VB:

dim query

query= from ord in datatableorder whereord.OrderHeader_Id.Equals(val) select ord

Dim dt as DataTable = query.ToDataTable()

Open in new window

0
 
LVL 34

Accepted Solution

by:
it_saige earned 1000 total points
ID: 41809567
If you don't want to move the rows from the original table then you could just use the CopyToDataTable method:
Imports System.Runtime.CompilerServices
Imports System.ComponentModel

Module Module1
	Sub Main()
		Dim table1 = (From i In Enumerable.Range(1, 20) Select New With {.OrderHeaderID = If(i And 1, i, i - 1), .UserName = "John", .ItemNo = String.Format("{0}1111", If(i And 1, Chr(65 + i), Chr(65 + (i - 1)))), .ZipCode = If(i And 1, 11111, 22222)}).ConvertToDataTable()
		Dim table2 = (From row In table1 Where row("OrderHeaderID").Equals(1) Select row).CopyToDataTable()
		Console.WriteLine("Rows in table2")
		For Each row In table2.Rows.Cast(Of DataRow)()
			Console.WriteLine("OrderHeaderID: {0}; UserName: {1}; ItemNo: {2}; ZipCode: {3}", row("OrderHeaderID"), row("UserName"), row("ItemNo"), row("ZipCode"))
		Next
		Console.WriteLine()
		Console.WriteLine("Rows in table1")
		For Each row In table1.Rows.Cast(Of DataRow)()
			Console.WriteLine("OrderHeaderID: {0}; UserName: {1}; ItemNo: {2}; ZipCode: {3}", row("OrderHeaderID"), row("UserName"), row("ItemNo"), row("ZipCode"))
		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

Which produces the following output -Capture.JPG
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question