Introduction to DataSets with Visual Basic .NET
- Building a DataSet with Code
- Loading a DataSet from a Database
- Saving Changes
- Saving Changes Redux
- Constraints
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
If you've been building database programs for a while with Visual Basic, you are probably familiar with the Recordset object. It provides a flat table-like view of the data you have selected. Depending on how it is created, the Recordset may provide methods that let you view, modify, add, and delete records as you move forward and backward through the data. Figure 1 shows a schematic representation of a Recordset.
)
Figure 1 A Recordset represents table-like data.
Despite the similarity of name, a DataSet bears little resemblance to a Recordset. While a Recordset represents a table-like collection of data, a DataSet represents an in-memory database. The DataSet object's Tables collection may contain any number of DataTable objects. Those objects hold table-like data similar to the data managed by a Recordset. Figure 2 shows a schematic representation of a DataSet.
)
Figure 2 A DataSet represents an in-memory database.
DataTable objects provide methods for examining, modifying, and deleting the data. They also provide methods for restricting a DataTable's data values and for relating one DataTable object to another. For example, you can indicate that one of the DataTable's columns is the table's primary key, disallows duplicates, has a foreign key relationship with a field in another DataTable, or defines a parent-child relationship between two DataTables.
Before you get into all those gory details, however, you need to know how to populate a DataSet. There are a couple of ways to do this. The following section shows how to build a DataSet at runtime using only program code. The section after that shows how you can load data from a database into a DataSet. The rest of this article explains some other objects related to DataSets, and shows how to use them.
Building a DataSet with Code
Your program can create database objects just like it can create any other object. There is nothing magical about the DataSet, DataTable, and other classes. Your program can declare variables to hold those objects, use the New keyword to instantiate the objects, and then use the objects' methods to fill them with data.
Program InlineDB uses the code shown in Listing 1 to build a database using inline data. Click here to download the InlineDB example program.
The program starts by creating a new DataSet object, giving it the name Student Scores. It creates a new DataTable object, names it Students, and adds it to the DataSet's Tables collection. It then uses the DataTable's Columns collection to add definitions for the table's columns. The first parameter to the collection's Add method gives the column's name, and the second gives its data type.
After it has defined the Students table's columns, the program repeats these steps to build the TestScores table.
Next, the program populates its tables. To fill the Students table, the program creates an array of objects, one for each field in the table. It sets the values for each field and then calls the Add method of the DataTable's Rows collection, passing it the array of values.
After it fills the Students table, the program demonstrates another method for filling a table. It calls the NewRow method of the TestScores DataTable to create a new DataRow object that contains the right fields for the TestScores table. It adds the DataRow to the DataTable's Rows collection and then sets the values for the row's fields. The program repeats these steps, creating a new DataRow, adding it to the DataTable's Rows collection, and setting the row's column values until it has finished populating the TestScores table.
The form's Load event handler finishes by binding the program's DataGrid control to the new DataSet object.
Figure 3 shows the InlineDB program in action. Initially, the DataGrid displays the two tables in the TreeView-like arrangement shown in Figure 3. Click on one of the table names to expand that table.
)
Figure 3 Program InlineDB displays inline data in a
DataGrid control.
Figure 4 shows the data in the TestScores table. Notice that the DataGrid displays the DataSet's name, Student Scores, in its caption. Click the left-pointing white arrow near the upper-right corner of the DataGrid to return to the table list shown in Figure 3.
)
Figure 4 Click on the TestScores link in Figure 3 to display the
TestScores DataTable.
Listing 1—Program InlineDB uses this code to create a DataSet from scratch
Private m_DataSet As DataSet
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
' Build the DataSet.
m_DataSet = New DataSet("Student Scores")
' Build the Students table.
Dim dt_students As New DataTable("Students")
m_DataSet.Tables.Add(dt_students)
dt_students.Columns.Add("StudentId", GetType(Integer))
dt_students.Columns.Add("FirstName", GetType(String))
dt_students.Columns.Add("LastName", GetType(String))
' Build the TestScores table.
Dim dt_testscores As New DataTable("TestScores")
m_DataSet.Tables.Add(dt_testscores)
dt_testscores.Columns.Add("StudentId", GetType(Integer))
dt_testscores.Columns.Add("TestNumber", GetType(Integer))
dt_testscores.Columns.Add("Score", GetType(Integer))
' Populate the Students table.
Dim student_data(2) As Object
student_data(0) = 1
student_data(1) = "Amy"
student_data(2) = "Anderson"
dt_students.Rows.Add(student_data)
student_data(0) = 2
student_data(1) = "Bob"
student_data(2) = "Baker"
dt_students.Rows.Add(student_data)
' Code for other insertions deleted...
' Populate the TestScores table.
Dim testscores_row As DataRow
' Student 1.
testscores_row = dt_testscores.NewRow()
dt_testscores.Rows.Add(testscores_row)
testscores_row.Item("StudentId") = 1
testscores_row.Item("TestNumber") = 1
testscores_row.Item("Score") = 98
testscores_row = dt_testscores.NewRow()
dt_testscores.Rows.Add(testscores_row)
testscores_row.Item("StudentId") = 1
testscores_row.Item("TestNumber") = 2
testscores_row.Item("Score") = 94
' Code for other insertions deleted...
' Bind the DataGrid to the DataSet.
DataGrid1.DataSource = m_DataSet
End Sub