VB.NET DataSet Examples

You want to use the DataSet type in your VB.NET program to store many DataTables. A DataSet is conceptually a set of DataTables and other information about those tables.

DataSet: This is a container for multiple DataTables. You can use it to create XML. It is a useful abstraction for simplifying programs.

DataSet usage

This example shows how you can add multiple DataTables to a DataSet collection. You should have some DataTables instantiated. Then, create a new DataSet with the constructor. Next, add tables with the Tables.Add subroutine invocation. We further demonstrate that the GetXml() function will print formatted XML that represents the data.

Program that uses DataSet [VB.NET]

Module Module1
    Sub Main()
	' Two DataTables.
	Dim table1 As DataTable = New DataTable("patients")
	table1.Columns.Add("name")
	table1.Columns.Add("id")
	table1.Rows.Add("sam", 1)
	table1.Rows.Add("mark", 2)

	Dim table2 As DataTable = New DataTable("medications")
	table2.Columns.Add("id")
	table2.Columns.Add("medication")
	table2.Rows.Add(1, "atenolol")
	table2.Rows.Add(2, "amoxicillin")

	' Create a DataSet. Put both tables in it.
	Dim set1 As DataSet = New DataSet("office")
	set1.Tables.Add(table1)
	set1.Tables.Add(table2)

	' Visualize DataSet.
	Console.WriteLine(set1.GetXml())
    End Sub
End Module

Output

<office>
  <patients>
    <name>sam</name>
    <id>1</id>
  </patients>
  <patients>
    <name>mark</name>
    <id>2</id>
  </patients>
  <medications>
    <id>1</id>
    <medication>atenolol</medication>
  </medications>
  <medications>
    <id>2</id>
    <medication>amoxicillin</medication>
  </medications>
</office>

Using

The Using resource acquisition statement can be used with the DataSet. This sometimes alleviates memory usage problems in programs. The following example demonstrates the correct syntax for the Using statement and a DataSet instance.

Program that shows Using statement with DataSet [VB.NET]

Module Module1
    Sub Main()
	Using set1 As DataSet = New DataSet("office")
	    ' Use set1 here.
	End Using
    End Sub
End Module

Namespace and Prefix

One of the uses of DataSet is that it allows you to get XML data for its contents. This can generate complete XML files for you. With the Namespace and Prefix properties, you can form correct XML for your specific application's requirements. Look at how the Namespace y and the Prefix x appear in the output.

Program that uses Namespace and Prefix [VB.NET]

Module Module1
    Sub Main()
	' Create DataTable.
	Dim table1 As DataTable = New DataTable("patients")
	table1.Columns.Add("name")
	table1.Columns.Add("id")
	table1.Rows.Add("sam", 1)

	' Create DataSet.
	Dim set1 As DataSet = New DataSet("office")
	set1.Tables.Add(table1)
	set1.Namespace = "y"
	set1.Prefix = "x"

	' Visualize it.
	Console.WriteLine(set1.GetXml())
    End Sub
End Module

Output

<x:office xmlns:x="y">
  <patients xmlns="y">
    <name>sam</name>
    <id>1</id>
  </patients>
</x:office>

DataSetName property

It is possible to change the name of your DataSet. Notice that when you call the DataSet constructor with a String argument, that sets the initial name. You can additionally modify or read the name by using the DataSetName String property.

Program that uses DataSetName [VB.NET]

Module Module1
    Sub Main()
	' Initialize DataSet.
	Dim set1 As DataSet = New DataSet("office")

	' Display, set, then display DataSetName.
	Console.WriteLine(set1.DataSetName)
	set1.DataSetName = "unknown"
	Console.WriteLine(set1.DataSetName)
    End Sub
End Module

Output

office
unknown

Copy and Clear

The DataSet provides ways for you to copy the entire contents of it into another object. You can use the Copy function for this purpose. Additionally, we show the Clear subroutine here, which scrubs the contents of the enclosed DataTables. When we call Clear, the copied DataSet is not changed.

Program that uses Copy and Clear [VB.NET]

Module Module1
    Sub Main()
	Dim table1 = New DataTable("patients")
	table1.Columns.Add("name")
	table1.Columns.Add("id")
	table1.Rows.Add("sam", 1)

	Dim table2 = New DataTable("medications")
	table2.Columns.Add("id")
	table2.Columns.Add("medication")
	table2.Rows.Add(1, "atenolol")

	' Create a DataSet instance.
	Dim set1 As DataSet = New DataSet("office")
	set1.Tables.Add(table1)
	set1.Tables.Add(table2)

	' Copy.
	Dim copy As DataSet = set1.Copy()

	' Clear original DataSet instance.
	set1.Clear()

	Console.WriteLine("set: {0}", set1.GetXml())
	Console.WriteLine("copy: {0}", copy.GetXml())
    End Sub
End Module

Output

set: <office />
copy: <office>
  <patients>
    <name>sam</name>
    <id>1</id>
  </patients>
  <medications>
    <id>1</id>
    <medication>atenolol</medication>
  </medications>
</office>

Loop through DataTables

Sometimes it is useful to loop through the DataTable instances stored in the enclosing DataSet. You can do this with a For loop. Simply loop from 0 to the Count of the Tables collection minus one; then get each collection from the index value.

Program that loops through tables [VB.NET]

Module Module1
    Sub Main()
	Dim table1 As DataTable = New DataTable("patients")
	table1.Columns.Add("name")
	table1.Columns.Add("id")
	table1.Rows.Add("sam", 1)

	Dim table2 As DataTable = New DataTable("medications")
	table2.Columns.Add("id")
	table2.Columns.Add("medication")
	table2.Rows.Add(1, "atenolol")
	table2.Rows.Add(6, "trifluoperazine")

	' Create the DataSet.
	Dim set1 As DataSet = New DataSet("office")
	set1.Tables.Add(table1)
	set1.Tables.Add(table2)

	' Loop over tables in the DataSet.
	Dim collection As DataTableCollection = set1.Tables
	For i As Integer = 0 To collection.Count - 1
	    ' Get table.
	    Dim table As DataTable = collection(i)
	    Console.WriteLine("{0}: {1}", i, table.TableName)
	Next

	' First table.
	Console.WriteLine("x: {0}", set1.Tables(0).TableName)

	' Row count of medications table.
	Console.WriteLine("y: {0}", set1.Tables("medications").Rows.Count)
    End Sub
End Module

Output

0: patients
1: medications
x: patients
y: 2

GetXml

The GetXml function, and its sibling GetXmlSchema, provide an easy way to visualize data in your programs. Some programs even persist their data as XML files, which makes these methods even more useful. GetXml returns the data in the DataSet as XML; GetXmlSchema returns a special document that reveals the layout of the data in the DataSet.

Summary

The DataSet is a powerful abstract data type that can store many instances of DataTables. It can be seen as a standard way of grouping together many DataTables. Those DataTables in turn store many DataColumn and DataRow object instances. DataSet assumes its place at the root of the hierarchy of data objects.