Home
VB.NET
DataTable Examples
This page was last reviewed on Dec 4, 2023.
Dot Net Perls
DataTable. In .NET and the VB.NET language, the DataTable type stores data in memory. It is often used alongside SQL databases.
Shows a datatable
Type details. DataTable has columns and rows properties, and we use these like other VB.NET collections. DataTable is an in-memory representation of structured data.
DataTable Select
First example. We define GetTable—this returns a new DataTable. When the GetTable function is invoked, it creates a new DataTable and adds 3 columns to it.
Info Columns are named with a string argument and a Type argument. They have different types.
Detail In a DataTable, each column allows a specific type of data. The GetTable method adds 5 rows to the DataTable.
Tip The arguments to the Rows.Add method are of the types specified in the columns.
Detail The PatientID column is of type String, and it stores String representations of the IDs (which are numeric in this program).
Important For .NET 5 in 2021, we must add the Imports System.Data directive at the top of the program.
Shows a datatable
Imports System.Data Module Module1 Sub Main() ' Get a DataTable instance from helper function. Dim table As DataTable = GetTable() Console.WriteLine("COLUMNS: {0}", table.Columns.Count) Console.WriteLine("ROWS: {0}", table.Rows.Count) End Sub ''' <summary> ''' Helper function that creates new DataTable. ''' </summary> Function GetTable() As DataTable ' Create new DataTable instance. Dim table As New DataTable ' Create 3 typed columns in the DataTable. table.Columns.Add("Dosage", GetType(Integer)) table.Columns.Add("Drug", GetType(String)) table.Columns.Add("PatientID", GetType(String)) ' Add five rows with those columns filled in the DataTable. table.Rows.Add(25, "Drug A", "10") table.Rows.Add(50, "Drug B", "50") table.Rows.Add(10, "Drug C", "51") table.Rows.Add(21, "Drug D", "52") table.Rows.Add(100, "Drug E", "11") Return table End Function End Module
COLUMNS: 3 ROWS: 5
Rows example. Here we reuse GetTable, and act on the DataTable in a more complex way. In the For-Each loop, we loop over each Row. With Field(), we print the first Integer cell in each row.
Detail Field, part of DataRow, is a generic method. So we must specify the Of Integer part to indicate its parametric type.
Imports System.Data Module Module1 Sub Main() Dim table As DataTable = GetTable() ' Access Rows property on DataTable. For Each row As DataRow In table.Rows ' Write value of first Integer. Console.WriteLine(row.Field(Of Integer)(0)) Next End Sub Function GetTable() As DataTable ' Create new DataTable instance. Dim table As New DataTable ' Create 3 typed columns in the DataTable. table.Columns.Add("Dosage", GetType(Integer)) table.Columns.Add("Drug", GetType(String)) table.Columns.Add("PatientID", GetType(String)) ' Add five rows with those columns filled in the DataTable. table.Rows.Add(25, "Drug A", "10") table.Rows.Add(50, "Drug B", "50") table.Rows.Add(10, "Drug C", "51") table.Rows.Add(21, "Drug D", "52") table.Rows.Add(100, "Drug E", "11") Return table End Function End Module
25 50 10 21 100
Compare rows. Suppose we have 2 DataTables and we wish to compare the rows in each. We can use a nested For-Each loop. And then we call SequenceEqual.
Info SequenceEqual is an extension method from LINQ. It tells us whether 2 arrays are the same.
Note We access ItemArray on each row, and then pass those arrays to SequenceEqual.
LINQ
Tip For more complex rows, we could add a method that converts the Object array into an array of just the important elements.
Module Module1 Function GetTable1() As DataTable Dim table As New DataTable table.Columns.Add("Size", GetType(Integer)) table.Columns.Add("Weight", GetType(Integer)) table.Rows.Add(25, 100) table.Rows.Add(100, 200) Return table End Function Function GetTable2() As DataTable Dim table As New DataTable table.Columns.Add("Size", GetType(Integer)) table.Columns.Add("Weight", GetType(Integer)) table.Rows.Add(300, 400) table.Rows.Add(25, 100) Return table End Function Sub Main() Dim table1 As DataTable = GetTable1() Dim table2 As DataTable = GetTable2() ' Loop over all rows. For Each row1 As DataRow In table1.Rows For Each row2 As DataRow In table2.Rows ' Get ItemArray for each row. Dim array1 As Object() = row1.ItemArray Dim array2 As Object() = row2.ItemArray ' Use SequenceEqual to compare arrays. If (array1.SequenceEqual(array2)) Then Console.WriteLine("FIRST ELEMENT OF EQUAL ROWS: " + array1(0).ToString()) End If Next Next End Sub End Module
FIRST ELEMENT OF EQUAL ROWS: 25
Merge, PrimaryKey. We can join 2 DataTables together with the Merge subroutine and the PrimaryKey property. First we create the tables, and set the PrimaryKey on each.
Important For this example to work, the PrimaryKey must be the same on each of the 2 tables. We join on the ID column.
Info PrimaryKey is an array of DataColumns. We can use an array initializer expression to create these arrays.
Finally When we call Merge, each PrimaryKey cell is considered. Each result row contains all the cells from rows with that ID.
Module Module1 Sub Main() ' A table containing IDs and Styles. Dim tableStyle As DataTable = New DataTable() Dim idColumn As DataColumn = New DataColumn("ID", GetType(Integer)) tableStyle.Columns.Add(idColumn) tableStyle.PrimaryKey = New DataColumn() {idColumn} tableStyle.Columns.Add(New DataColumn("Style", GetType(String))) tableStyle.Rows.Add(1, "opaque") tableStyle.Rows.Add(2, "translucent") tableStyle.Rows.Add(3, "none") Display(tableStyle) ' A table containing IDs and Costs. Dim tableCost As DataTable = New DataTable() Dim idColumnSize As DataColumn = New DataColumn("ID", GetType(Integer)) tableCost.Columns.Add(idColumnSize) tableCost.PrimaryKey = New DataColumn() {idColumnSize} tableCost.Columns.Add(New DataColumn("Cost", GetType(String))) tableCost.Rows.Add(1, 70) tableCost.Rows.Add(2, 120) tableCost.Rows.Add(3, 60) Display(tableCost) ' Merge on the ID column. tableStyle.Merge(tableCost) Display(tableStyle) End Sub Sub Display(ByRef table As DataTable) ' Display the entire DataTable. Console.WriteLine("::TABLE::") For Each row As DataRow In table.Rows Console.WriteLine("ROW: {0}", String.Join(",", row.ItemArray)) Next End Sub End Module
::TABLE:: ROW: 1,opaque ROW: 2,translucent ROW: 3,none ::TABLE:: ROW: 1,70 ROW: 2,120 ROW: 3,60 ::TABLE:: ROW: 1,opaque,70 ROW: 2,translucent,120 ROW: 3,none,60
Rows and columns. The Rows property returns DataRow instances. A DataRow must contain a cell for each DataColumn in the table. We can access fields with the Field extension.
DataRow
DataColumn
DataSet
A summary. DataTable supplies instructions for in-memory data representations—with it, we handle data. The class supports some modifications, like merging.
Dot Net Perls is a collection of tested code examples. Pages are continually updated to stay current, with code correctness a top priority.
Sam Allen is passionate about computer languages. In the past, his work has been recommended by Apple and Microsoft and he has studied computers at a selective university in the United States.
This page was last updated on Dec 4, 2023 (simplify).
Home
Changes
© 2007-2024 Sam Allen.