The DataSet in Connected Environments

In Chapter 3, you saw how to create and use DataSet objects in disconnected environments. You saw how to use a DataSet's properties and how to fill, delete, and update data using DataTable objects. In the following sections, you'll see how to work with DataSets in connected environments. Instead of using a DataTable, you'll use a DataAdapter to fill and save data from a DataSet to a data source.

As mentioned, the Fill method of a DataAdapter fills data from a data source to a DataSet. Once the DataSet is filled with data, you can do any operation on this data. This data is disconnected data, and there's no connection between a DataSet and a data source until you finally call the Update method of a DataAdapter. The Update method of a DataAdapter saves the DataSet changes to the data source. You saw these Fill and Update methods in "The DataAdapter: Adapting to Your Environment."

Filling a DataSet from Multiple Tables

You saw how to fill a DataSet from a table in many of the previous samples. Now, there may be times when you'll want to view data from multiple tables in data-bound controls. If you remember ADO, you used SQL JOIN statements to fill a recordset from multiple tables.

In ADO.NET, there are different ways you can fill data from multiple tables depending on your requirements. If you want to view data from multiple tables in multiple data-bound controls, the simplest way is create a DataAdapter for each table and fill a DataSet from a DataAdapter and use the DataSet to bind data to the controls. Listing 4-36 simply creates two SqlDataAdapter objects using two different SqlCommand objects and then fills two different DataSet objects (which you later bind to two separate DataGrid controls).

Listing 4-36: Filling Data from Multiple Tables

 Dim ConnectionString As String = "Integrated Security=SSPI;" & _      "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) conn.Open() Dim adapter1 As SqlDataAdapter = New SqlDataAdapter() Dim adapter2 As SqlDataAdapter = New SqlDataAdapter() adapter1.SelectCommand = New SqlCommand("SELECT * FROM Customers") adapter2.SelectCommand = New SqlCommand("SELECT * FROM Orders") adapter1.SelectCommand.Connection = conn adapter2.SelectCommand.Connection = conn Dim ds1 As DataSet = New DataSet() Dim ds2 As DataSet = New DataSet() adapter1.Fill(ds1) adapter2.Fill(ds2) DataGrid1.DataSource = ds1.DefaultViewManager DataGrid2.DataSource = ds2.DefaultViewManager ' Dispose conn.Close() conn.Dispose() 

Now what if you want to fill a DataSet with data from multiple tables? Actually, that's pretty easy, too. If you remember the Merge method of the DataSet from Chapter 3, you'll learned how to merge two DataSet objects. You can use the same method to fill data from two different tables in two different DataSet objects and later call the Merge method, which merges one DataSet into another. Listing 4-37 fills two DataSet objects from two different tables—Customers and Orders—and later one DataSet merges into another. After that you just bind the final DataSet to a DataGrid control, which displays data from both tables.

Listing 4-37: Merging Two DataSet Objects

 Dim adapter As SqlDataAdapter = New SqlDataAdapter()     adapter.SelectCommand = New SqlCommand("SELECT * FROM Customers")     adapter.SelectCommand.Connection = conn     Dim ds1 As DataSet = New DataSet()     adapter.Fill(ds1)     adapter.SelectCommand = New SqlCommand("SELECT * FROM Orders")     adapter.SelectCommand.Connection = conn     Dim ds2 As DataSet = New DataSet()     adapter.Fill(ds2)     ds1.Merge(ds2, False, MissingSchemaAction.Add) DataGrid1.DataSource = ds1.DefaultViewManager 

Note

The Merge method has seven overloaded forms. Using this method, you can even merge DataRow and DataTable objects with a DataSet.

Using a SQL Statement to Read Data from Two or More Tables

Using a SQL statement is the simplest way to put data from multiple tables into a DataSet. The following code selects data from the Customers and Orders tables and fills it to a single DataSet:

 Dim conn As SqlConnection = New SqlConnection(ConnectionString) SQL = "SELECT * FROM Customers; SELECT * FROM Orders" conn.Open() Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn) Dim ds As DataSet = New DataSet("CustOrdersDataSet") adapter.Fill(ds) 

Adding, Updating, and Deleting Data through the DataSet

You add, edit, and delete data in a DataSet through DataSet objects. As you know, a DataSet is a collection of DataSet objects. The DataSet's Rows and Columns properties represent the collection of a DataSet's rows and columns, which are represented by the DataRowCollection and DataColumnCollection objects. The DataRowCollection and DataColumnCollection classes provide methods to add and delete rows and columns, respectively (see Chapter 3 for more details). You can add a new row to a DataTable using its NewRow method, which takes a DataRow as an argument. To update a DataRow's data (through DataTable), you first get a DataRow based on the index of the row and edit its values. The Delete method of a DataRow deletes a row. Listing 4-38 adds, updates, and deletes data of a DataSet through a DataTable and a DataRow.

Listing 4-38: Adding, Editing, and Deleting a DataSet Rows

 Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn)     ' Create a command builder object     Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)     ' Create a dataset object     Dim ds As DataSet = New DataSet()     adapter.Fill(ds, "Employees")     ' Create a data table object and add a new row     Dim EmployeeTable As DataTable = ds.Tables("Employees")     Dim row As DataRow = EmployeeTable.NewRow()     row("FirstName") = "New Name"     row("LastName") = "Mr. Last"     row("Title") = "New Employee"     EmployeeTable.AcceptChanges()     EmployeeTable.Rows.Add(row)     ' Get the last row     Dim editedRow As DataRow = EmployeeTable.Rows _     (EmployeeTable.Rows.Count - 1)     editedRow("FirstName") = "Edited Name"     editedRow("LastName") = "Mr. Edited"     editedRow("Title") = "Edited Employee"     EmployeeTable.AcceptChanges()     ' Delete a row. Last row - 1     Dim deletedRow As DataRow = EmployeeTable.Rows _     (EmployeeTable.Rows.Count - 2)     deletedRow.Delete()     EmployeeTable.AcceptChanges()     ' Save changes to the database     adapter.Update(ds, "Employees") 

Accepting and Rejecting Changes through the DataSet

The DataSet class provides methods that allow you to make sure if you want to save changes or reject changes made to a DataSet since it was loaded or the last time AcceptChanges was called. The AcceptChanges method saves the changes, and the RejectChanges method rejects (rolls back) all the changes made to the DataSet. Listing 4-39 shows you how to use these methods to accept and reject changes.

Listing 4-39: Using the AcceptChanges and RejectChanges Methods of a DataSet

 Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn)     ' Create a command builder object     Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)     ' Create a dataset object     Dim ds As DataSet = New DataSet()     adapter.Fill(ds, "Employees")     ' Create a data table object and add a new row     Dim EmployeeTable As DataTable = ds.Tables("Employees")     Dim row As DataRow = EmployeeTable.NewRow()     row("FirstName") = "New Name"     row("LastName") = "Mr. Last"     row("Title") = "New Employee"     EmployeeTable.Rows.Add(row)     ' Update data adapter       If MessageBox.Show("Do you want to save change?", _           "DataSet AcceptReject Methods", _            MessageBoxButtons.YesNo, MessageBoxIcon.Question) _          = DialogResult.Yes Then       ds.AcceptChanges()     Else       ds.RejectChanges()     End If     adapter.Update(ds, "Employees") 

Saving Changed Data Only

As you know, you fill a DataSet using the Fill method of a DataAdapter, modify its contents, and call the Update method to save changes made to a DataSet. This method sends the entire DataSet data back to the database. This scenario may affect the performance and increase network congestion when working with large data—especially over the Web.

Wouldn't it be nice if you could send only the changed data of a DataSet back to the database instead of sending the entire contents of a DataSet? The DataSet provides methods that allow you to find out the changed rows and retrieve changed rows only. The HasChanges method of the DataSet returns True if it finds any changes based on the DataRow's state, represented by the DataRowState enumeration. The GetChanges method returns the changed rows since a DataSet was loaded or AcceptChanges was last called and returns the changed data in a DataSet. Now you just need to call the DataAdapter's Update method with the DataSet object returned by the GetChanges method, which will send this DataSet's contents back to the database.

Listing 4-40 shows you how to call the GetChanges method and save only the modified rows back to the database.

Listing 4-40: Saving Only Modified Rows of a DataSet

 ' See if DataSet has changes or not     If Not ds.HasChanges(DataRowState.Modified) Then Exit Sub     Dim tmpDtSet As DataSet     ' GetChanges for modified rows only.     tmpDtSet = ds.GetChanges(DataRowState.Modified)     If tmpDtSet.HasErrors Then       MessageBox.Show("DataSet has errors")       Exit Sub     End If     adapter.Update(tmpDtSet) 

Using a DataSet vs. Using a DataReader

When should you use a DataSet and when should you use a DataReader? This question comes up on almost every ADO.NET-related discussion forum or news-group. There are several differences between the two:

• Accessing performance: A DataReader provides a read-only, forward-only view of data and is faster than a DataSet because it's only reading data. In other words, you can only read data using a DataReader. A DataSet provides both read and write access to data.

• Data-bound connectivity: A DataReader doesn't provide any direct data-bound connectivity, which makes it less flexible than a DataSet. A DataSet is a versatile and flexible control, which allows both data read and write options and provides way to add, edit, delete, and view data in data-bound controls with little effort.

• Connected state: A DataReader is always in a connected state. This means if you're using a DataReader, there's always a connection established between the reader and the database, until you close the DataReader. A DataSet stores data in a disconnected state in Extensible Markup Language (XML) format. Once you fill data from a data source to a DataSet, there's no connection between a DataSet and the database. A connection is established when you read or updated data again.

You should now have an idea of when to use a DataReader and when to use a DataSet.

Fetching Data Based on Wildcard Characters

The SELECT...LIKE SQL statement is useful when you want to select rows based on a wildcard string. For example, say you want to get all rows of the Employees table where an employee's first name starts with C and ends with a. You build a SQL statement like so:

 Dim sql as string = SELECT * FROM Employees WHERE FirstName LIKE 'C%a' 

Another useful example of LIKE is when you want to select all the employees whose first name contains the string gar. You can build a SELECT statement as follows:

 Dim sql as string = SELECT * FROM Employees WHERE FirstName LIKE '%gar%' 

Now you can use this SELECT statement in a DataReader or DataAdapter to retrieve the data from a database.