Populating a DataSet from a Data Source

Populating a `DataSet` from a Data Source

The minimum that you need in order to make use of the DataAdapter is a connection and a Select command. Although independent Connection and Command objects can be created, configured, and assigned to the DataAdapter's Connection and SelectCommand properties, using the form of the DataAdapter's constructor that accepts two string parameters one for the Select statement and one for the connection string is often more convenient. The code for this task is

 Dim da As SqlDataAdapter = New SqlDataAdapter( _       "select * from tblDepartment", _      "server=localhost;uid=sa; database=novelty")

Note

Don't forget, the SQL statement that you specify for the SelectCommand can contain parameters. If necessary, refer back to Chapter 4 to refresh your memory about how to define parameters for the different .NET Data Providers.

Let's now call the Fill method to retrieve data from the Novelty database and load them into the DataSet. First, we add the code to the frmDataSets form in the DataSetCode project begun in Chapter 5 by doing the following.

Right-click on the DataSetCode project in the Solution Explorer and select Properties from the pop-up menu displayed.
Select the General item in the Common Properties folder and then set the Startup object property to frmDataSets.
Display frmDataSets in the Form Designer.
Add a button below the btnConstraints button from the Windows Forms tab of the Toolbox.
In the Properties window, set the Name property of the button to btnDataAdapterFill and set the Text property to DataAdapter Fill.
As we'll be using the SqlClient data provider, we need to add after the existing import statements at the top an imports statement for this namespace (shown in boldface type):
```
 Imports System  Imports System.Data Imports System.Data.SqlClient 
```
Add the code shown in Listing 6.1 to frmDataSets.

Listing 6.1 Using a SqlDataAdapter to fill the dsEmployeeInfo `DataSet`

 Private Sub btnDataAdapterFill_Click(ByVal sender As _         System.Object, ByVal e As System.EventArgs) _         Handles btnDataAdapterFill.Click      ReadData() End Sub Private Sub ReadData()      Dim rows as Integer      Dim daDepartments As SqlDataAdapter = New _      SqlDataAdapter("select * from tblDepartment", _        "server=localhost;uid=sa;database=novelty")      dsEmployeeInfo = New DataSet()      rows = daDepartments.Fill(dsEmployeeInfo, "Departments")      DisplayDataSet(dsEmployeeInfo) End Sub

After creating the daDepartments DataAdapter with the Select statement and the connection string, we can call the Fill method to fill a table in the dsEmployeeInfo DataSet named Departments. The Fill method also returns the number of rows added to (or refreshed in) the DataSet. The following steps are implicitly performed by the DataAdapter in order to execute the Fill method:

Opens the SelectCommand's connection, if it is not already open.
Executes the command specified by the SelectCommand's CommandText property (and parameters, if any).
Creates a DataReader to return the column names and types used to create a new Data-Table in the specified DataSet, if it doesn't already exist.
Uses the DataReader to retrieve the data and populate the table.
Closes the DataReader.
Closes the connection, if it was opened by the DataAdapter. If it was originally found open, the DataAdapter will leave it open.

Note

When executing a single command against a data source, you will usually find it simpler and more efficient to let the DataAdapter internally create and manage the Command and Connection objects by supplying the Select and connections strings when creating the DataAdapter. However, if you're going to execute several commands against the same database, it is more efficient to create and open a Connection object and then assign it to the DataAdapter. That keeps the connection open rather than its repeatedly being opened and closed, which is a significant performance hit. The equivalent code would then be

 Private Sub ReadData()     Dim rows as Integer    Dim daDepartments As New SqlDataAdapter()    Dim conn As New SqlConnection( _      "server=localhost;uid=sa;database=novelty")    Dim cmdSelect As New SqlCommand( _        "select * from tblDepartment")    dsEmployeeInfo = New DataSet()    cmdSelect.Connection = conn    daDepartments.SelectCommand = cmdSelect    ' Open the connection before starting operations    conn.Open()    rows = daDepartments.Fill(dsEmployeeInfo, "Departments")    ' Do other database operations here    ' . . .    DisplayDataSet(dsEmployeeInfo)    ' When we are all done, close the connection    conn.Close() End Sub

Of course, to make it really worthwhile, you would need additional database operations using the same connection string.

We passed the Fill method a reference to a DataSet and the name of the DataTable to fill. We could also have passed a reference to a DataTable instead of the name. Another option is to specify only the DataSet and then Fill defaults to loading the data into a DataTable named Table.

Note

Although you would normally use the DataAdapter to fill a DataTable contained in a DataSet, there is an overloaded version of the Fill method that loads data into a stand-alone DataTable object.

If we want to load a second table, we can add a second DataAdapter with a different Select statement. To load both the Department and Employees tables from their corresponding database tables, we replace the routine ReadData in Listing 6.1 with:

 Private Sub ReadData()    Dim rows as Integer   Dim daDepartments As SqlDataAdapter = New _   SqlDataAdapter("select * from tblDepartment", _     "server=localhost;uid=sa;database=novelty")   Dim daEmployees As SqlDataAdapter = New _       SqlDataAdapter("select * from tblEmployee", _     "server=localhost;uid=sa;database=novelty")     dsEmployeeInfo = New DataSet()     rows = daDepartments.Fill(dsEmployeeInfo, "Departments")     rows = daEmployees.Fill(dsEmployeeInfo, "Employees")     DisplayDataSet(dsEmployeeInfo)   End Sub

Running the DataSetCode project and clicking on the DataAdapterFill button would fill the listbox with results similar to those obtained before. Only now, the data loaded into the DataSet and displayed in the listbox comes from the SQL Server database, rather than being generated locally in code.

We could of course also create a DataRelation between the two tables, as we did earlier in the previous chapter, to establish a parent-child relationship between the rows in the two tables.

Using a different DataAdapter for each table in the DataSet isn't always necessary. We can reuse the same DataAdapter by modifying the commands that it uses. This approach is useful mainly for multiple fills programmatically creating and modifying all the commands (Insert, Update, Delete) for updating each of the DataTables to the data source is more involved.

Note

It is also possible, and sometimes preferable, to fill a DataSet table with the result of using a SQL Join to link two tables. Then there would be a single DataSet table and no need to create a relation between them. However, using independent tables linked by a DataRelation is usually more flexible. That's particularly true when it comes to updating the data source, as there often are limitations on updating joined table. But, if we're actually updating them independently, these limitations don't exist.

Thus we can rewrite the preceding code, using a single DataAdapter, as follows:

 Private Sub ReadData()    Dim rows as Integer   Dim da As SqlDataAdapter = New _     SqlDataAdapter("select * from tblEmployee", _       "server=localhost;uid=sa;database=novelty")   dsEmployeeInfo = New DataSet()   rows = da.Fill(dsEmployeeInfo, "Employees")   ' Change Select statement to different table   da.SelectCommand.CommandText = _     "select * from tblDepartment"   rows = da.Fill(dsEmployeeInfo, "Departments")   DisplayDataSet(dsEmployeeInfo) End Sub

Note

A more efficient way of loading two tables in the DataSet would be to supply a SelectCommand that either calls a stored procedure that returns multiple result sets or that executes a batch of SQL commands. Doing so requires only a single round-trip to the server to retrieve all of the data, as opposed to the multiple trips required by the code shown. However, although the retrieving of multiple tables in this manner is straightforward, updating the data source with changes made to the DataSet tables would be somewhat complicated, if there are relations between the tables. We look at how to update such tables in Business Case 6.1 later in this chapter.

Listing 6.2 demonstrates how to reuse a single DataAdapter for multiple operations and how to merge multiple Fills into a single DataTable.

Listing 6.2 Using a single SqlDataAdapter to perform multiple `Fill` operations to a single table

 Private Sub ReadData()   Dim daEmployees As SqlDataAdapter = New SqlDataAdapter( _     "select * from tblEmployee where DepartmentID = 1", _     "server=localhost;uid=sa;database=novelty")   dsEmployeeInfo = New DataSet()   daEmployees.Fill(dsEmployeeInfo, "Employees")   'Change WHERE clause from DepartmentID = 1 to   ' DepartmentID = 3   daEmployees.SelectCommand.CommandText = _   "select * from tblEmployee where DepartmentID = 3"   daEmployees.Fill(dsEmployeeInfo, "Employees")   DisplayDataSet(dsEmployeeInfo) End Sub

Note that in Listing 6.2 the value returned by the Fill method in the local variable rows is no longer captured. It isn't necessary to capture this returned value unless we intend to use or test it. We could take the approach of Listing 6.2 one step farther and execute the same Select statement multiple times to refresh the same DataTable with the most recent data (possibly modified by a different user) as it currently exists at the data source.

Note

Existing values in a DataTable are updated only on a subsequent Fill operation if a primary key is defined for the DataTable. The default operation of the Fill method is to fill the DataTable with column schema information and rows of data, without setting any constraints that might be configured at the data source. To set the PrimaryKey property correctly so that such refreshes (as well as the Find method) can be executed one of the following must be done before the Fill method is called.

Call the FillSchema method of the DataAdapter.
Set the DataAdapter's MissingSchema property to AddWithKey.
Explicitly set the PrimaryKey property to the appropriate column(s) if they are known at design time.