An Introduction to DataSet in VB.NET

Just What You Need to Know About DataSet

Much of Microsoft's data technology, ADO.NET, is provided by the DataSet object. This object reads the database and creates an in-memory copy of that part of the database that your program needs. A DataSet object usually corresponds to a real database table or view, but DataSet is a disconnected view of the database. After ADO.NET creates a DataSet, there is no need for an active connection to the database, which helps in scalability because the program only has to connect with a database server for microseconds when reading or writing.

In addition to being reliable and easy to use, DataSet supports both a hierarchical view of the data as XML and a relational view that you can manage after your program disconnects.

You can create your own unique views of a database using DataSet. Relate DataTable objects to each other with DataRelation objects. You can even enforce data integrity using the UniqueConstraint and ForeignKeyConstraint objects. The simple example below uses only one table, but you can use multiple tables from different sources if you need them.

Coding a VB.NET DataSet

This code creates a DataSet with one table, one column and two rows:

Dim ds As New DataSet
Dim dt As DataTable
Dim dr As DataRow
Dim cl As DataColumn
Dim i As Integer

dt = New DataTable()
cl = New DataColumn(
    "theColumn",
    Type.GetType("System.Int32"))
dt.Columns.Add(cl)

dr = dt.NewRow()
dr("theColumn") = 1
dt.Rows.Add(dr)

dr = dt.NewRow()
dr("theColumn") = 2
dt.Rows.Add(dr)
ds.Tables.Add(dt)

For i = 0 To ds.Tables(0).Rows.Count - 1
    Console.WriteLine(
        ds.Tables(0).Rows(i).Item(0).ToString)
Next i

The most common way to create a DataSet is to use the Fill method of the DataAdapter object. Here's a tested program example:

Dim connectionString As String =
    "Data Source=MUKUNTUWEAP;" &
    "Initial Catalog=Booze;" &
    "Integrated Security=True"
Dim cn As New SqlConnection(connectionString)
Dim commandWrapper As SqlCommand =
    New SqlCommand("SELECT * FROM RECIPES", cn)
Dim dataAdapter As SqlDataAdapter =
    New SqlDataAdapter
Dim myDataSet As DataSet = New DataSet
dataAdapter.SelectCommand =
    commandWrapper
dataAdapter.Fill(myDataSet, "Recipes")

The DataSet can then be treated like a database in your program code. The syntax doesn't require it, but you will normally provide the name of the DataTable to load the data into. Here's an example showing how to display a field.

Dim r As DataRow
For Each r In myDataSet.Tables("Recipes").Rows
    Console.WriteLine(r("RecipeName").ToString())
Next

Although the DataSet is easy to use, if raw performance is the goal, you might be better off writing more code and using the DataReader instead.

If you need to update the database after changing the DataSet, you can use the Update method of the DataAdapter object, but you have to make sure that the DataAdapter properties are set correctly with SqlCommand objects. SqlCommandBuilder is usually used to do this.

Dim objCommandBuilder As New SqlCommandBuilder(dataAdapter)
dataAdapter.Update(myDataSet, "Recipes")

DataAdapter figures out what has changed and then executes an INSERT, UPDATE, or DELETE command, but as with all database operations, updates to the database can run into problems when the database is being updated by other users, so you often need to include code to anticipate and solve problems when changing the database.

Sometimes, only a DataSet does what you need.

If you need a collection and you're serializing the data, a DataSet is the tool to use. You can quickly serialize a DataSet to XML by calling the WriteXML method.

DataSet is the most likely object you will use for programs that reference a database. It's the core object used by ADO.NET, and it is designed to be used in a disconnected mode.