HLRosenberger
asked on
Datatable - split into multiple data tables.
I have a datatable, and say it contains data for a list of cars. There are multiple records per car. The VIN column identifies the data per car. It's ordered by VIN. Is there a way to easily split out the set of records per car into separate datatables? So, if I had 6 cars worth of data, I need 6 separate data tables created from the original datatable.
In other words:
Produces the following output -
-saige-
Imports System.Runtime.CompilerServices
Imports System.ComponentModel
Module Module1
Sub Main()
Dim data = (From i In Enumerable.Range(0, 20) Select New With {.ID = i, .Name = String.Format("Name{0}", i), .LastSaleDate = DateTime.Now.AddMonths(-(9 * i)), .VIN = If(i Mod 6 = 0, Guid.Parse("59c0743b-4cb7-466a-a069-f3be4c9ca434"), If(i Mod 5 = 0, Guid.Parse("e0f86c79-c008-480a-9cd1-7562084ebe56"), If(i Mod 4 = 0, Guid.Parse("cef55dd8-25a2-4949-92aa-3fbf1e50a192"), If(i Mod 3 = 0, Guid.Parse("a1b2f20f-6248-460e-a939-205334029266"), If(i Mod 2 = 0, Guid.Parse("f485afc0-e994-40ef-b8a1-83e2b8af330d"), Guid.Parse("6f43bb8b-29b9-4c83-89a0-6820bbab9815"))))))}).ConvertToDataTable()
Dim sets = data.Rows.Cast(Of DataRow).GroupBy(Function(r) r("VIN")).Select(Function(g) g.CopyToDataTable())
For Each [set] In sets
Console.WriteLine("Demographics for VIN: {0}", [set].Rows.Cast(Of DataRow).First()("VIN"))
Console.WriteLine("Sold {0} times before this year.", [set].Rows.Cast(Of DataRow).Where(Function(r) DateTime.Parse(r("LastSaleDate")) < DateTime.Now.AddYears(-1)).Count())
Console.WriteLine("Sold {0} times this year.", [set].Rows.Cast(Of DataRow).Where(Function(r) DateTime.Parse(r("LastSaleDate")).Year.Equals(DateTime.Now.Year)).Count())
Console.WriteLine()
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
Produces the following output -
-saige-
Here is some code that will give you what you are looking for.
Public Class Form1
Dim MyDataTable As New DataTable()
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'' Test Data
Dim dc = New DataColumn("VIN", GetType(String))
MyDataTable.Columns.Add(dc)
dc = New DataColumn("Manufacturer", GetType(String))
MyDataTable.Columns.Add(dc)
dc = New DataColumn("Owner", GetType(String))
MyDataTable.Columns.Add(dc)
Dim row = MyDataTable.NewRow()
row(0) = "A123"
row(1) = "Ford"
row(2) = "John Smith"
MyDataTable.Rows.Add(row)
row = MyDataTable.NewRow()
row(0) = "B123"
row(1) = "Ford"
row(2) = "John Smith"
MyDataTable.Rows.Add(row)
row = MyDataTable.NewRow()
row(0) = "A123"
row(1) = "Ford"
row(2) = "Tom Smith"
MyDataTable.Rows.Add(row)
row = MyDataTable.NewRow()
row(0) = "B123"
row(1) = "Ford"
row(2) = "John Smith"
MyDataTable.Rows.Add(row)
row = MyDataTable.NewRow()
row(0) = "B123"
row(1) = "Ford"
row(2) = "Henry Smith"
MyDataTable.Rows.Add(row)
row = MyDataTable.NewRow()
row(0) = "Z123"
row(1) = "Ford"
row(2) = "Alice Jones"
MyDataTable.Rows.Add(row)
DataGridView1.DataSource = MyDataTable
End Sub
Dim dTables As New List(Of DataTable)
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'' Linq query to split the original DataTable by VIN number
dTables = (From dt In MyDataTable.AsEnumerable()
Group dt By vbInformation = dt.Field(Of String)("VIN") Into carGroup = Group
Select carGroup.CopyToDataTable()).ToList()
'' Show the results in there own DataGridView
DataGridView2.DataSource = dTables(0)
DataGridView3.DataSource = dTables(1)
DataGridView4.DataSource = dTables(2)
End Sub
End Class
The results of the code is
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This work and is easy to implement.
C#