| Finally we focus on the DataView class. This class is a helper that you can use to customize the behavior of the DataSet. The main functions of interest are sorts and filters, but there are other interesting methods and properties as well. We went through the properties and methods earlier in the chapter. Now let's see how we can use the class to quickly switch attributes of the DataSet which affect its appearance. Let's create a project called ADOBook03-04. This time, instead of using relations to customize our view of the data, we'll use the DataView class. In a real-world project, we would use a combination of both techniques, along with SQL to optimize performance. For example, we would not typically read all the rows in each table. We would probably use a where clause in our Select statements to limit the amount of data being transferred over the network. The top level statement would contain a where clause that would allow us to limit the results to a subset of the table. The Orders level would then use an in clause to limit the order headers to only those that match the results of the Customers query and so on down through the levels of detail. I will demonstrate this later in the chapter. For the first demo, we will use a DataSet similar to that used in the previous example. The difference this time is that we will eliminate the relations between the tables (for now). We are going to use DataViews to accomplish the same result as the relations did. As with everything in programming, there are always multiple solutions to any problem. That is why we have so much religion in the computer field. Many programmers think their way is the only right way. Please don't be this way with these demonstrations . I am aware that there might be better ways to solve this type of problem. My purpose here is to demonstrate the classes, not necessarily to find the best solution. This time, we will use two data grid controls so the user does not have to drill down. All of the data will be available all of the time. To save time, we'll look at the finished form, then we will describe how to make it work (see Figure 7.24). Figure 7.24. The project using two grids.  To create the form, place another panel control on the form to hold the two grids. To do this, set the Dock property of the existing grid to None. This will allow you to resize it. Temporarily resize it so that it takes up very little space. Now drop your panel on the form. Size the panel so it is a decent size . Next cut and paste the existing grid into the panel so it is a child control of the panel. Now add the new grid. Set the Dock property of the new panel to Fill, and set the borderstyle to Fixed 3D. Next set the Dock property of the existing grid and the new grid both to Fill. You may have to use a Move To Front command on the existing grid to get them to position properly. I trust that by now you can create the UI without too much coaching. I really want to concentrate on the code. Next add three DataView objects to the form. You can drag them from the toolbox. The idea behind this project is to automatically populate the upper grid with the orders for the customer displayed in the header, and then to display the order details in the lower grid for each order as the user navigates the upper grid. The DataView objects will be used to customize the view of the DataSet for each section of the form. We will do this by binding the controls to the DataView objects instead of directly to the DataSet. Yes, DataView is a bindable class that can act as a DataSource. Create your three DataAdapter objects as we did in the previous chapter. The only thing we will do differently is to create a join in SqlDataAdapter3 so we can display the product name in the lower grid. This just makes it a little friendlier for the user. You can generate your DataSet and call it dsOrders as before. This time we will not create the relations between the tables. Each DataView object will be bound to one of the DataTables in the DataSet. Bind DataView1 to the Customers table, DataView2 to the Orders table, and DataView3 to the Order Details table. Now, change the bindings of the three text boxes in the screen header to bind to DataView1, and then the appropriate column. Do the same for the data grid controls. To complete the project, let's add the following code to the form: ' This procedure fills the DataSet. Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Try SqlDataAdapter1.Fill(DsOrders1.Customers) SqlDataAdapter2.Fill(DsOrders1.Orders) SqlDataAdapter3.Fill(DsOrders1.Order_Details) DsOrders1.AcceptChanges() SetRowPos(0) Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Sub ' Move to the next row. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim intPos As Integer Try intPos = Me.BindingContext(DataView1).Position intPos += 1 If intPos > DataView1.Count - 1 Then Exit Sub SetRowPos(intPos) Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Sub ' Moves to the previous row. Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim intPos As Integer Try intPos = Me.BindingContext(DataView1).Position intPos -= 1 If intPos < 0 Then Exit Sub SetRowPos(intPos) Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Sub ' Sets the row filter for the Orders grid. Private Sub SetRowPos(ByVal intPos As Integer) Dim intOrdPos As Integer Me.BindingContext(DataView1).Position = intPos DataView2.RowFilter = "CustomerID = '" & Trim(TextBox1.Text) & "'" intOrdPos = DataGrid1.CurrentRowIndex SetDetailPos(intOrdPos) End Sub ' Sets the row filter for the Details grid. Private Sub SetDetailPos(ByVal intOrdPos As Integer) DataView3.RowFilter = "OrderID = " & DataGrid1.Item(intOrdPos, 0) End Sub Private Sub DataGrid1_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.CurrentCellChanged SetDetailPos(DataGrid1.CurrentRowIndex) End Sub By setting the RowFilter property, we immediately change the display in the grid. Isn't that easy? The DataView class is very powerful. When we run the project, each time we move to a new Customer row, the other grids automatically fill with the data for that customer. Likewise, as we navigate through the orders grid, the detail items change. Since we are only applying a filter, there is no round trip to the server required. This makes it perform very efficiently . Using SQL to Enhance Performance The last modification we want to make is to optimize performance by limiting the rows returns from the server. We can do this by adding a couple of parameters to the SQL queries in the DataAdapters. Let's modify our form to add one more field to serve as the look-up field. Your form should now look like Figure 7.25. Figure 7.25. Form with the search field added.  We will allow our users to use a wildcard search to look up customers by customer ID. We can use SQL to do all of the selecting for us. For SqlDataAdapter1.Selectcommand use the following SQL: SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID LIKE @CustID + '%') This will fill the Customers table based on the wildcard provided by the user. Now change the SQL for SqlDataAdapter2 to read: SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE (CustomerID IN (SELECT CustomerID FROM Customers WHERE CustomerID LIKE @CustID + '%')) Notice that we are using the SQL itself to select the correct rows from the Orders table based on what we found in the Customers table. We will now do a similar thing with the order details: SELECT [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID WHERE ([Order Details].OrderID IN (SELECT OrderID FROM Orders WHERE CustomerID LIKE @CustID + '%')) When we run these queries, we will have all of the rows needed for each table in the DataSet. We then can use the same technique with the DataViews to populate the grid controls. As a matter of fact, only one procedurethe Read Data button click eventneeds to be changed. Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim strSearch As String Try DsOrders1.Clear() strSearch = Trim(TextBox4.Text) SqlDataAdapter1.SelectCommand.Parameters("@CustID").Value = strSearch SqlDataAdapter1.Fill(DsOrders1.Customers) SqlDataAdapter2.SelectCommand.Parameters("@CustID").Value = strSearch SqlDataAdapter2.Fill(DsOrders1.Orders) SqlDataAdapter3.SelectCommand.Parameters("@CustID").Value = strSearch SqlDataAdapter3.Fill(DsOrders1.Order_Details) DsOrders1.AcceptChanges() SetRowPos(0) Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Sub Now we have the best of both worlds , the convenience of the DataView class and the efficiency of SQL. | 
