VB.NET DataRow Tips

When using the DataTable type in the VB.NET language, you often need to access or manipulate individual rows of data. The DataRow type offers a way to do this. Narrowing the data abstraction to the level of the row, the DataRow provides ways to add, remove, or read cells from the enclosing data structure.

These VB examples show how to use the DataRow type. DataRow stores a row in a DataTable.

Example 1

As we begin, please notice that the examples that follow invoke a function called GetTable. This function returns a fully formed DataTable instance: it has four columns and five rows, for a total of 20 cells. You can paste this function into the following examples to achieve compilation.

Program that creates DataTable instance [VB.NET]

Module Module1

    Function GetTable() As DataTable
	' Generate a new DataTable.
	' ... Add columns.
	Dim table As DataTable = New DataTable
	table.Columns.Add("Weight", GetType(Integer))
	table.Columns.Add("Name", GetType(String))
	table.Columns.Add("Breed", GetType(String))
	table.Columns.Add("Date", GetType(DateTime))
	' ... Add rows.
	table.Rows.Add(57, "Koko", "Shar Pei", DateTime.Now())
	table.Rows.Add(130, "Fido", "Bullmastiff", DateTime.Now())
	table.Rows.Add(92, "Alex", "Anatolian Shepherd Dog", DateTime.Now())
	table.Rows.Add(25, "Charles", "Cavalier Kind Charles Spaniel", DateTime.Now())
	table.Rows.Add(7, "Candy", "Yorkshire Terrier", DateTime.Now())
	Return table
    End Function

    Sub Main()
	' Acquire the DataTable instance.
	Dim table As DataTable = GetTable()
    End Sub

End Module

Create DataRow

In this example, we see a simple way to construct a DataRow instance using an Object() array. When you create the Object() array, please set it up so that the elements are arranged to match the ordering of the DataRow template itself. Next, invoke the Add method upon the Rows collection to turn the Object() array into part of the enclosing DataTable structure.

Program that adds DataRow [VB.NET]

Module Module1

    Sub Main()
	Dim table As DataTable = GetTable()
	' Create an array of four objects and add it as a row.
	Dim v(3) As Object
	v(0) = 7
	v(1) = "Candy"
	v(2) = "Yorkshire Terrier"
	v(3) = DateTime.Now()
	table.Rows.Add(v)
    End Sub

End Module

Get DataRow

It is useful to acquire references to individual DataRow instances from the DataTable based on indexes. Once you acquire the DataRows, you can access cells from those rows using indexes or string indexers. In this example, we access the first and last row from the DataRow, and then look up a cell on those rows.

Program that accesses rows [VB.NET]

Module Module1

    Sub Main()
	Dim table As DataTable = GetTable()

	' First row.
	Dim row1 As DataRow = table.Rows(0)
	Console.WriteLine(row1("Breed"))

	' Last row.
	Dim row2 As DataRow = table.Rows(table.Rows.Count - 1)
	Console.WriteLine(row2("Breed"))
    End Sub

End Module

Output

Shar Pei
Yorkshire Terrier

Loop through DataRow

How can you loop through the cells in a DataRow instance? One way you can do this is through the ItemArray instance property upon the row instance. On the iteration variable in the For Each loop, please notice that you must test the type of the Object; after this, you could perform more specific operations based on that type.

Program that loops through row [VB.NET]

Module Module1

    Sub Main()
	Dim table As DataTable = GetTable()
	' Get first row.
	Dim row1 As DataRow = table.Rows(0)
	' Loop over ItemArray.
	For Each item As Object In row1.ItemArray
	    ' Test the type of each element.
	    If (TypeOf item Is Integer) Then
		Console.WriteLine("Integer")
	    ElseIf (TypeOf item Is String) Then
		Console.WriteLine("String")
	    ElseIf (TypeOf item Is DateTime) Then
		Console.WriteLine("DateTime")
	    End If
	Next
    End Sub

End Module

Output

Integer
String
String
DateTime

Remove DataRow

Sometimes you want to remove a row from an enclosing DataTable, but not actually erase that row's data from memory. You can therefore remove a row and then add it somewhere else. To do this, please invoke the Remove instance method upon the Rows collection. This example demonstrates that when you remove the first row, the DataTable changes so that the second row is in the first position.

Program that removes rows [VB.NET]

Module Module1

    Sub Main()
	' Get the DataTable.
	Dim table As DataTable = GetTable()

	' Get the first row.
	Dim row As DataRow = table.Rows(0)
	table.Rows.Remove(row)

	' Get the new first row.
	row = table.Rows(0)
	Console.WriteLine(row("Name"))
    End Sub

End Module

Output

Fido

Delete DataRow

As an alternative to the Remove subroutine, you can invoke the Delete subroutine. This actually clears the memory inside the DataRow instance you call it upon. In other words, this will yield an empty or null row, as well as removing it from the previously enclosing DataTable structure.

Program that deletes rows [VB.NET]

Module Module1

    Sub Main()
	' Get the DataTable.
	Dim table As DataTable = GetTable()

	' Get the first row and delete it.
	Dim row As DataRow = table.Rows(0)
	row.Delete()

	' Get the new first row.
	row = table.Rows(0)
	Console.WriteLine(row("Name"))
    End Sub

End Module

Output

Fido

Summary

The DataRow provides a necessary level of abstraction for manipulating the DataTable structure. As an essential type for developing data-driven VB.NET programs, the DataRow is fairly straightforward to use once the underlying concepts are understood. Please also check out information about the enclosing DataTable type in this language.