VB.NET DataTable Examples

Store data in memory from databases and other data sources with the DataTable type.

DataTable. Data is often stored in tables. And these tables often reside in databases. In the .NET Framework, the DataTable type stores data in memory.

Type details. Used often in VB.NET programs, DataTable has columns and rows properties. It is an in-memory representation of structured data (like data read from a database).

First example. We define GetTable—this returns a new DataTable. When the GetTable function is invoked, it creates a new DataTable and adds 4 columns to it.

Columns: These are named with a string argument and a Type argument. They have different types.

GetTable: 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.

VB.NET program that creates DataTable Module Module1 Sub Main() ' Get a DataTable instance from helper function. Dim table As DataTable = GetTable() End Sub ''' <summary> ''' Helper function that creates new DataTable. ''' </summary> Function GetTable() As DataTable ' Create new DataTable instance. Dim table As New DataTable ' Create four typed columns in the DataTable. table.Columns.Add("Dosage", GetType(Integer)) table.Columns.Add("Drug", GetType(String)) table.Columns.Add("Patient", GetType(String)) table.Columns.Add("Date", GetType(DateTime)) ' Add five rows with those columns filled in the DataTable. table.Rows.Add(25, "Indocin", "David", DateTime.Now) table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now) table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now) table.Rows.Add(21, "Combivent", "Janet", DateTime.Now) table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now) Return table End Function End Module

Rows example. Please paste in the GetTable method to run this program. In the For-Each loop, we loop over each Row. With Field(), we print the first Integer cell in each row.

Generic method: Field, part of DataRow, is a generic method. So we must specify the Of Integer part to indicate its parametric type.

VB.NET program that loops over rows Module Module1 Sub Main() ' This calls the GetTable method from above. 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 End Module Output 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.

SequenceEqual: This is an extension method from LINQ. It tells us whether 2 arrays are the same.

Array

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.

VB.NET program that compares rows 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 Output 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.

PrimaryKey: This is an array of DataColumns. We can use an array initializer expression to create these arrays.

Merge: When we call Merge, each PrimaryKey cell is considered. Each result row contains all the cells from rows with that ID.

VB.NET program that uses Merge, PrimaryKey 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 Output ::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

Select. One Function we can call is Select. This acts like a database query. We pass it is query string and the DataTable itself returns matching rows.DataTable Select

Rows. This 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 DataRow Field

Columns. In every table we must have columns. These are a template for the actual data, for the DataRows. Each field's type is specified with a DataColumn.DataColumn

DataSet. A program can use many DataTables. And with DataSet, we can combine those tables into a single Class—this makes them easier to handle.DataSet

Windows Forms. You can display DataTables on a DataGridView control in Windows Forms. We can use the DataSource property as well with the DataTable.DataGridView

A summary. DataTable supplies instructions for in-memory data representations. With it we handle data. The class supports some modifications, like merging.

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

VB.NET DataTable Examples

Related Links:

.Net

Java

Related Links