Database Access with Visual Basic .NET (3rd Edition)

DataViews

The DataView object allows us to create different views of the data stored in a DataTable, allowing for multiple simultaneous views of the same data. The DataView has properties that allow customization of the data exposed, based on:

• Sort order one or more columns, either ascending or descending

• Row filter expression expression specifying criteria for which rows are to be exposed, based on column values

• Row state filter expression specifying criteria for which rows are to be exposed, based on the state of the row (see the DataViewRowState enumeration shown in Table 5.3)

Although this method may seem similar to the DataTable's Select method, there is a significant difference. On the one hand, the DataView is a fully dynamic view of the data. In addition to column value changes, additions and deletions of rows in the underlying table are immediately reflected in the data exposed by the DataView. On the other hand, the Select method returns a fixed length array of row references, which reflects changes to column values in the underlying table but doesn't reflect any changes in membership (additions or deletions) or ordering. This dynamic aspect of the DataView makes it particularly well suited for data-binding scenarios.

Note

Although the DataView is similar to a classical database view, it differs in several significant ways.

• It cannot be used as if it were a table.

• It cannot provide a join of multiple tables.

• It cannot exclude columns that exist in the underlying table

• It cannot add additional columns (for example, computed columns) that do not exist in the underlying table.

You can immediately start using views by accessing the DefaultView property of the DataTable. Suppose that you want to have a view of the Customers table that only exposes customers whose last name begins with 'C' and orders them by zip code. To do so, just set the two corresponding property values:

dsCustomers.Tables("Customers").DefaultView().RowFilter = _ "LastName = 'Like C* '" dsCustomers.Tables("Customers").DefaultView().Sort = "Zip"

If, instead, you want to expose the current values of only those rows that have been modified (but not yet saved), you would reset the RowFilter property and set the RowState property

dsCustomers.Tables("Customers").DefaultView().RowFilter = "" dsCustomers.Tables("Customers").DefaultView().RowStateFilter = _

Note

The DataView also has the Find method to search for a single row and the FindRows method to search for and return multiple rows. These methods use the current setting of the Sort property of the key for their searches. If you're interested in retrieving the row or set of rows matching a specific criterion rather than maintaining a dynamic view of the data, using the Find or FindRows method (instead of setting the RowFilter property) returns only the rows of interest. It also provides better performance than setting the RowFilter property. The reason is that setting the RowFilter property causes the view's index to be rebuilt, whereas Find and FindRows use the already existing index.

DataViewRowState.ModifiedCurrent

Additional DataViews can also be created for a table. If you wanted to define another view onto the Customers table, you could create another view and set its properties as desired:

dvView2 = New DataView(dsCustomers.Tables("Customers"), "", _ "LastName", DataViewRowState.CurrentRows)

Of course, after creating the view, you can modify its settings:

dvView2.RowFilter = "LastName > 'F'" dvView2.Sort = "LastName DESC" dvView2.RowStateFilter = DataViewRowState.Current

Note

There is also a DataViewManager object. It provides a convenient centralized way of managing the settings for the default views of all the tables in a DataSet.

In most other ways, the DataView is very much like a DataTable. Individual rows and the column values of the view are accessed via the DataRowView object. This object also supports navigating relations that have been defined between the DataSet tables.

The DataView has an editing model similar to that of the DataTable. Once editing has been enabled, by setting the AllowNew, AllowEdit, or AllowDelete property to True, the corresponding editing operation(s) may be performed. The BeginEdit, EndEdit, and CancelEdit methods of the DataRowView control application of the changes to the underlying DataTable. EndEdit places the changes in the Current row version of the DataRow underlying the DataRowView. These changes are then accepted (or rejected) by the underlying DataTable when AcceptChanges (or RejectChanges) is called.

Let's now take a look at some of these concepts in action. We will add a new form to the DataSetCode project to provide two different views of a single Customers table. For each view a DataGrid will display the view data and a set of controls to specify the view sort and filter properties. To get this result we do the following.

1. On the frmDataSets form, add a button beneath the DataAdapterUpdates button.

2. Name the new button btnDataViews and set its Text property to Data Views.

3. Add a form, frmDataViews, to the DataSetCode project.

4. In the Properties window for frmDataViews, set its Text property to Dueling DataViews.

5. Enlarge the size of frmDataViews.

6. Add a DataGrid named DataGrid1, a textbox named txtFilter1, a combobox named cboSort1, a checkbox named chkDesc1, a combobox named cboRowState1, a button named btnApply1, and three labels to the upper portion of the frmDataViews form.

7. Set the checkbox's Text property to Descending, and the button's Text property to Apply. Set the DropDownStyle property for both comboboxes to DropDownList. Set the label Text properties to Filter:, Sort by Column:, and Row State.

8. Set the CaptionText property of DataGrid1 to Default DataView.

9. Arrange the controls as shown in Figure 7.2.

   Figure 7.2. Arrangement of controls on upper portion of frmDataViews

10. Select all the controls and copy them to the bottom portion of frmDataViews. Rename all the controls (except for the labels) so that they end in 2 instead of 1, as in btnApply2.

11. Set the CaptionText property of DataGrid2 to DataView2.

The final design of the form is shown in Figure 7.3, and the code for this form is shown in Listing 7.6

Listing 7.6 Code for two grids displaying different views of same data table

Imports System Imports System.Data Imports System.Data.SqlClient Public Class frmDataViews Inherits System.Windows.Forms.Form "Windows Form Designer generated code" Private dsCustomers As New DataSet() Private dvView2 As DataView Private Sub frmDataViews_Load (ByVal sender As System.Object,_ ByVal e As System.EventArgs) Handles MyBase.Load Dim i As Integer Dim col As DataColumn ' Initialize DataAdapter Dim daCustomers As SqlDataAdapter = New SqlDataAdapter ("select * from tblCustomer", _ "server=localhost;uid=sa;database=novelty") ' Fill only ONE table daCustomers.Fill (dsCustomers, "Customers") ' create second DataView dvView2 = New DataView (dsCustomers.Tables ("Customers"), _ "", "LastName", DataViewRowState.CurrentRows) ' Fill list of column names For Each col In dsCustomers.Tables ("Customers").Columns cboSort1.Items.Add (col.ColumnName) cboSort2.Items.Add (col.ColumnName) Next ' Fill list of DataViewRowState enumeration Dim names As String() names = DataViewRowState.None.GetNames (DataViewRowState.None.GetType) For i = 0 To names.GetUpperBound (0) cboRowState1.Items.Add (names (i)) cboRowState2.Items.Add (names (i)) Next ' set to default values txtFilter1.Text = "" txtFilter2.Text = "" cboSort1.SelectedItem = "ID" cboSort2.SelectedItem = "ID" chkDesc1.Checked = False chkDesc2.Checked = False cboRowState1.SelectedItem = "CurrentRows" cboRowState2.SelectedItem = "CurrentRows" dsCustomers.Tables ("Customers").DefaultView.Sort = "ID" dvView2.Sort = "ID" ' Bind grids to table DataGrid1.DataSource = _ dsCustomers.Tables ("Customers").DefaultView DataGrid2.DataSource = dvView2 End Sub Private Sub btnApply1_Click (ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnApply1.Click Dim sort As String Dim rowState As DataViewRowState ' Set Filter dsCustomers.Tables ("Customers").DefaultView.RowFilter = _ txtFilter1.Text ' Set sort sort = cboSort1.SelectedItem If chkDesc1.Checked Then sort = sort & "DESC" End If dsCustomers.Tables ("Customers").DefaultView.Sort = sort ' Set row state dsCustomers.Tables ("Customers").DefaultView.RowStateFilter = _ rowState.Parse (rowState.GetType, cboRowState1.SelectedItem) End Sub Private Sub btnApply2_Click (ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnApply2.Click Dim sort As String Dim rowState As DataViewRowState ' Set Filter dvView2.RowFilter = txtFilter2.Text ' Set sort sort = cboSort2.SelectedItem If chkDesc2.Checked Then sort = sort & "DESC" End If dvView2.Sort = sort ' Set row state dvView2.RowStateFilter = _ rowState.Parse (rowState.GetType, cboRowState2.SelectedItem) End Sub End Class

The frmDataViews_Load initializes the various objects on the form. The DataAdapter is created and then used to load the tblCustomer data into the DataSet's Customers table. For the two DataViews we will create a new one, dvView2, and use the table's default view as the other. Then dvView2 is initialized to expose all current rows and sorted by LastName.

The two sets of comboboxes are then initialized. The cboSort controls are loaded with the list of column names of the Customers table. The cboRowState controls are filled with the list of enumerated values for the DataViewRowState enumeration.

Note

Visual Basic.NET no longer supports the ItemData property. That's why we use the enumeration's GetNames method to convert from enumeration values to strings when loading the comboboxes. Similarly, the enumeration's Parse method is used to convert from strings to enumeration values when later assigning the selected values to the RowStateFilter property.

Default settings are then assigned to the criteria controls. Then the initial sort order for both views is set to the ID field. Finally, each of the two views is bound to one of the DataGrids, which results in both grids displaying all the current data.

The selected criteria settings are applied to the appropriate view when the corresponding Apply button is clicked on. The two routines, btnApply1_Click and btnApply2_Click, are identical except that they manipulate alternate sets of controls. The RowFilter is set from the text in the txtFilter textbox, the Sort property is set from the column selected in the combobox (with the optional addition of the DESC descending modifier), and the RowStateFilter is set from the value set in the combobox. Modifying the properties of the views that are bound to the grids causes the grids automatically to display the data per the new view specifications.

Now you can run the DataSetCode project. Click on the Data Views button, which displays the new form, frmDataViews. Make what ever changes you like to the criteria of either grid. Be sure to click on the Apply button to apply those changes to the associated grid. Figure 7.4 shows a sample display.

Experiment with the comboboxes to try different columns to sort by and different row states to display. Try different filters, including compound expressions such as "ID > 10 and ID < =18" or "LastName Like 'c*'" (don't type the double quotes in the textbox). For more information on the rules for the filter expression, see the Visual Studio help topic DataColumn.Expression Property.

In addition, the grids automatically support adding, modifying, and deleting rows, so try editing some rows and then select an appropriate row state (for example, Added, ModifiedCurrent, or Deleted) to display only those modified rows.

Note

Be sure to pay attention to how the two views displayed in the grids are actually displaying the same base table. If you add, modify, or delete a row in one view (and accept the change by moving to another row), the change automatically appears in the other view (unless, of course, that row is filtered out). Very cool!

Business Case 7.1: Viewing Data Combined from Different Sources

When Jones Novelties, Incorporated, set out to build its new data processing system, it already had bits and pieces of it sitting in different forms. For example, CEO Brad Jones had been maintaining order information in an Access database. Although he realized the benefits of developing a new system with SQL Server as the database, he wanted to be sure that nothing would get lost during the transition. He felt that it was important do make changes gradually, especially when it came to safeguarding all the company's historical order data.

What Jones wants to do is to develop the new system on SQL Server but to keep using the data stored in his Access database until the end of the transition. Thus some data would be stored in one database and other data would be stored in a second database. The two databases would need to share and join data even though they wouldn't be of the same database type!

Fortunately, this requirement doesn't pose a problem for ADO.NET. As we've shown in this chapter, the DataSet object doesn't care, or even know, where the data in its tables came from. That's true even if it comes from different sources. Jones's database developer can develop the application today by loading the tblOrder table from an Access database and the tblCustomer table from a SQL Server database. In the future, when she is ready to have the tblCustomer table reside in the SQL Server database as well, she just changes the connection string used by the DataAdapter to load tblCustomer, and everything else continues to function as before.

We will now build a form that shows how Jones's database developer can achieve that result. We do so as follows.

1. Launch Visual Studio.NET.

2. Create a new Visual Basic Windows Application project.

3. Name the project BusinessCase71.

4. Specify a path for saving the project files.

5. Enlarge the size of Form1.

6. In the Properties window for Form1, set its Name property to frmShowOrders and its Text property to Show Orders.

7. Add a textbox named txtCustomerID, a button named btnFind, a listbox named lst-Customer, and a DataGrid named grdOrders. Set the button's Text property to Find.

8. Arrange the controls as shown in Figure 7.5.

   Figure 7.5. Arrangement of the controls on frmShowOrders

9. From the Toolbox, add a DataSet component as an Untyped DataSet and set its name to dsCustOrders.

10. For tblCustomer, which resides in the SQL Server Novelty database, add a SqlDataAdapter. When the Configuration Wizard begins, choose the connection to the Novelty database being used throughout this chapter. Select Use SQL Statements for the Query Type.

11. Use Select * from tblCustomer as the SQL statement to load data into the DataSet.

12. When finished with the Configuration Wizard, change the name of the SqlDataAdapter to daCustomers. The Configuration Wizard has also placed a (properly configured) Sql-Connection component in the component tray.

13. For tblOrder, which resides in the Novelty.MDB Access database, add an OledbDataAdapter. When the Configuration Wizard begins, add a connection. When the New Connection button is clicked on and the Data Link tabbed dialog is displayed, click on the Provider tab and select the Microsoft Jet 4.0 OLE DB Provider.

14. Click on the Connection tab and enter or browse to the Novelty.MDB database file.

15. Select Use SQL Statements for the Query Type.

16. Use Select * from tblOrder as the SQL statement to load data into the DataSet.

17. When finished with the Configuration Wizard, change the name of the OledbDataAdapter to daOrders. The Configuration Wizard has also placed a (properly configured) OledbConnection component in your component tray.

The first piece of code to be added at the top of the file is

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.Oledb

Then, within the body of the class definition for frmOrders, we add the code shown in Listing 7.7.

Listing 7.7 Code to join data from two different types of data sources

Private dvOrders As New DataView() Private Sub frmShowOrders_Load (ByVal sender As system.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Dim rel As DataRelation ' Fill the DataSet table with data from database daCustomers.Fill (dsCustOrders, "Customers") daOrders.Fill (dsCustOrders, "Orders") ' Create relation between the tables rel = dsCustOrders.Relations.Add ("relCustOrders", _ dsCustOrders.Tables ("Customers").Columns ("ID"), _ dsCustOrders.Tables ("Orders").Columns ("CustomerID")) ' Set Primary Key on Customers table Dim pk (0) As DataColumn pk (0) = dsCustOrders.Tables ("Customers").Columns ("ID") dsCustOrders.Tables ("Customers").PrimaryKey = pk ' Set Default Sort to allow Find method to work dsCustOrders.Tables ("Customers").DefaultView.Sort = "ID" End Sub Private Sub btnFind_Click(ByVal sender As System.Object,_ ByVal e As System.EventArgs) Handles btnFind.Click Dim RowNum As Integer Dim dvRow As DataRowView Dim i As Integer If IsNumeric (txtCustID.Text) Then RowNum = dsCustOrders.Tables ("Customers"). _ DefaultView.Find (txtCustID.Text) If RowNum <> -1 Then dvRow = dsCustOrders.Tables ("Customers"). _ DefaultView (RowNum) ' Fill Listbox with Customer data fields lstCustomer.Items.Clear() For i = 0 To sCustOrders.Tables ( _ "Customers").Columns.Count - 1 lstCustomer.Items.Add (dvRow.Item(i)) Next grdOrders.CaptionText = _ "Orders for customer#" & txtCustID.Text ' Get related Child rows of selected Customer dvOrders = dvRow.CreateChildView ("relCustOrders") grdOrders.DataSource = dvOrders Else MessageBox.Show( _ "CustomerID not found Please try again.") txtCustID.Clear() End If Else Beep() End If End Sub

We set up everything in the frmShowOrders_Load routine. We then fill the two DataSet tables and create the DataRelation that joins them. Finally, we set the PrimaryKey and Sort properties on the Customers table and DefaultView so that we will be able to use the view's Find method, as we demonstrate shortly.

The interesting stuff happens in response to clicking on the Find button, which is implemented in the btnFind_Click routine. After verifying that the value in the txtCustID textbox is indeed numeric, we search for this value via the Customers DefaultView. If it is found, each of the columns of that Customer DataRowView is displayed in the listbox. We then create a new view of the child rows of this selected DataRowView and bind the view to the DataGrid.

Be sure that you appreciate and are appropriately impressed by what we have just done. We have created and navigated a relation that joins two tables from two different types of databases!

You can now run the BusinessCase7 project and see for yourself how the customer data is displayed in the listbox and the orders for that customer are displayed in the grid. Figure 7.6 shows a sample display.

Note

Don't be alarmed or confused by the fact that the Orders grid in Figure 7.6 has a column named OrderAmount rather than Amount, as we defined it in our SQL Server table. Remember, although the customer data is coming from the SQL Server database, the orders data is coming from a different, "legacy" MDB database. It is not uncommon to see the names of database objects change from one version of an application to the next.

If you're really bothered by OrderAmount, you can rectify it by using the AS clause to change the column name in the data returned by the Select statement for the daOrders DataAdapter, as follows:

SELECT CustomerID, ID, OrderAmount AS Amount, OrderDate FROM tblOrder

The need for data to be easily combined from several different data sources will become more prevalent as time goes on. Companies will deal with more trading partners, companies will merge, and data will come in different formats. For example, XML is becoming an increasing popular and easy way to transfer data such as orders and products lists between companies. To the DataSet, XML is just another way to fill its tables with data. In Chapter 10 we show how to use the DataSet's ability to load and save data as XML.