VB.NET - DataSet Examples

DataSet. This type stores many DataTables in VB.NET programs. A DataSet is conceptually a set of DataTables and other information about those tables.

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

DataTable

DataRow

An example. To begin, we show how to 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.

Finally We demonstrate that the GetXml() function will print formatted XML that represents the data.

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

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

Detail The code demonstrates the correct syntax for the Using statement and a DataSet instance.

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

Namespace, Prefix. DataSet allows you to get XML data for its contents. This can generate complete XML files. With the Namespace and Prefix properties, you can form correct XML.

Here Look at how the Namespace "y" and the Prefix "x" appear in the output of this program.

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

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

DataSetName. It is possible to change the name of your DataSet. When you call the DataSet constructor with a String argument, that sets the initial name.

Also You can modify or read the name by using the DataSetName String property.

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

office unknown

Copy, 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.

Also We show the Clear subroutine here, which scrubs the contents of the enclosed DataTables.

Tip When we call Clear, the copied DataSet is not changed. The two objects are in separate memory.

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

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

Loop through DataTables. With a For-loop, we loop from 0 to the Count of the Tables collection minus one. Then we get each collection from the index value.

Tip Sometimes it is useful to loop through the DataTable instances stored in the enclosing DataSet.

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

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 persist their data as XML files.

Summary. DataSet can be seen as a standard way of grouping together many DataTables. Those DataTables in turn store many DataColumn and DataRow object instances.