Using the partial classes for business logic will work for minor code that deals directly with the data. Most enterprise developers prefer to have a separate class to contain the logic for each entity. I am currently working with the Mere Mortals Framework by Kevin McNeish which heavily uses business objects. It is fairly easy to use a Typed DataSet as we have in this example while also instantiating the separate business object classes and pointing them to a DataTable in the existing DataSet. In his way, you get the best of both worlds—the power and reusability of business object classes with all the business logic and the data sources wizards for ease of building screens.
Business Logic
As we saw in Part 2 of this series, one of the important parts of business logic is providing a pass-through for the application to call the Data Access layer. Each Table Adapter needs to be instantiated and a method provided to fill the DataTable in the DataSet. For those tables that are updateable, a method should also be provided to pass updates back to the database. For the Northwind DataSet containing 5 tables in the Part 2 example, here is the Data Access code needed:
Imports ta = NorthwindDataSetTableAdapters
Partial Public Class NorthwindDataSet
Private taOrders As New ta.OrdersTableAdapter
Private taOrderDetail As New ta.Order_DetailsTableAdapter
Private taCustomer As New ta.CustomersTableAdapter
Private taEmployee As New ta.EmployeesTableAdapter
Private taProduct As New ta.ProductsTableAdapter
Public Sub FillDataSetAll()
Me.taOrders.Fill(Me.Orders)
Me.taOrderDetail.Fill(Me.Order_Details)
Me.taCustomer.Fill(Me.Customers)
Me.taEmployee.Fill(Me.Employees)
Me.taProduct.Fill(Me.Products)
End Sub
Public Sub UpdateOrders()
Me.taOrders.Update(Me.Orders)
End Sub
Public Sub UpdateOrderDetails()
Me.taOrderDetails.Update(Me.Order_Details)
End Sub
End Class
This is a simplistic example where the DataTables are filled with all the records in the database. For larger databases, the fills can be done by passing a filter parameter.
Parameterized Queries
One of the options in the DataSet Designer that we have not covered yet is the ability to add multiple parameterized queries through which data can be retrieved from the database if you want something less than all the records in the table. If you open the dataset and right-click on one of the table adapter headers, you see a list of functions available. The Add Query option opens the Table Adapter Configuration Wizard with the options to use SQL statements, create new stored procedures, or use existing stored procedures. For simple queries you can use SQL statements but in a secure environment, it is best to use stored procedures to access the data. The ones generated by the Wizard are adequate, but you may use some that are generated by your DBA. If you do use existing SPs the next screen will allow you to select them for a list of all stored procedures in the database.
Figure 2 - Table Adapter Command Type
The next screen offers the choice of what the query will be used for. The options are: Select a set of rows, return a single value, Update, Delete, or Insert. Most often the query will be to return a set of rows, but the option to change the way an update or delete happens is interesting also.
Figure 3 - Table Adapter Query Type
Each query is defined by a SQL Statement which can be typed in here or built visually using the standard Visual Query Builder.
Figure 4 - Table Adapter Select Statement
At this point you can add a filter parameter to the query to only get orders for a specific customer
Figure 5 - Visual Query Builder
Figure 6 - Table Adapter Method Names
The next screen in the Wizard allows you to edit the names for the FillBy (for existing DataTables) and GetDataBy (returns an DataTable object) types of methods. Clicking the Finish button will complete the query.
The DataSet Designer will add another line to the Table Adapter section of the table representation showing the new method. Now the DataTable can be filled with only part of the orders by calling the FillByCustomerID and passing a CustomerID parameter.
Using Optional Parameters
Another way to provide flexibility in your table adapters is to use optional parameters and allow the user the choice of several different parameters with one call. In this example, we could make one query that would allow the user to fill by either the CustomerID or the EmployeeID by adding to the WHERE clause "OR @Parameter IS NULL" as follows.
SELECT *
FROM Orders
WHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (EmployeeID = @EmployeeID OR @EmployeeID IS NULL)
AND NOT (@CustomerID IS NULL AND @EmployeeID IS NULL)
In this example you can pass any or both parameters but if you don't pass any, you will get an empty result set.
Typed DataSets handle optional parameters fairly well. The generator will create Nullable(Of Type) parameters for value types and correctly convert to dbnull.
The only disadvantage with optional parameters in Typed DataSets is you have to take care of the parameters collection yourself. Most of the time you have to set the AllowDBNull property by hand, and the Wizard will reset its state every time you reconfigure the query.
Filling a Table by Relation
Since we have limited the number of records in the Orders table, we should also limit the rows when filling the Order Details table. It would be nice if Microsoft added a feature for filling by relationship, but it did not make it into this version. So we need to come up with a work around.
If we were using stored procedures, we could use the Multiple Active Result Sets (MARS) feature of SQL Server 2005 to return the data for both tables at once. You have to add some extra code to map the result sets into the correct Data Tables, but it is not very difficult. Using the partial class for the Table Adapter is a good place to write this code since you have access to the Data Adapter object to add the TableMappings method.
Figure 7 - Query to Fill Order Details by Relation
Another approach would be to loop through the records in the Orders table and create a string of delimited OrderIDs to pass as a parameter to be used by an IN() clause filter. But the IN() clause does note accept a variable, so you would have to build the SQL Statement concatenating the list of IDs into the statement and using an EXEC function to execute it at runtime.
The easiest solution is to add another query to the Order Details Table Adapter that joins the tables together and filters on the same CompanyID as shown in the Query Builder of Figure 7. If this is the primary query defining the Order Details table, this will not work since it is hard for the Update to know which table to update. But as a secondary query, it should work fine.
Updating Multiple Tables
In the previous article, I showed a very simple procedure for updating the dataset back to the database. But the update method requires special concern when updating related tables. If the tables are updated in the wrong order, you could end up trying to delete parent records before child record or inserting child record before inserting parent records.
The correct sequence for updating related tables is to first send the deleted children, then update the parent table, and then update and add the children records. The GetChanges method of the DataTable allows for this functionality. First we create new temporary DataTables and define them as subsets of the current Data Table based on which records have been added or deleted. Then each of these temporary Data Tables is updated in the correct sequence. (Note: The format for this code can be found in the VB Code Snippets insert.)
Public Sub UpdateDB()
Dim DeletedChildRecords As DataTable = _
Me.Order_Details.GetChanges(DataRowState.Deleted)
Dim NewChildRecords As DataTable = _
Me.Order_Details.GetChanges(DataRowState.Added)
Dim ModifiedChildRecords As DataTable = _
Me.Order_Details.GetChanges(DataRowState.Modified)
Try
If Not DeletedChildRecords Is Nothing Then
taOrderDetail.Update(DeletedChildRecords)
DeletedChildRecords.Dispose()
End If
taOrders.Update(Me.Orders)
If Not ModifiedChildRecords Is Nothing Then
taOrderDetail.Update(ModifiedChildRecords)
ModifiedChildRecords.Dispose()
End If
If Not NewChildRecords Is Nothing Then
taOrderDetail.Update(NewChildRecords)
NewChildRecords.Dispose()
End If
Me.AcceptChanges()
Catch ex As Exception
Throw ex
End Try
End Sub
Adding Other Business Logic
The partial class of the DataSet is a great place to implement any business logic that has to do with actual data items. We used the partial class for the dataset to instantiate the table adapters, but there are also sub partial classes for each of the tables (row collections), individual rows of a table, and even the row change event. Outside of the dataset partial class, you can also access the partial class for each of the Table Adapters.
For example, if you wanted to create method that would consolidate two orders for the same customer by moving all the Order Detail records from one Order to another, you could create the following class inside the DataSet partial class (sub-class):
Partial Public Class OrdersDataSet
...
Partial Public Class Order_DetailsDataTable
Public Sub MoveDetailRecords(ByVal FromOrderID As Integer,
ByVal ToOrderID As Integer)
For Each row As Order_DetailsRow In Me.Select("OrderID=" & FromOrderID)
row.OrderID = ToOrderID
Next
End Sub
End Class
...
End Class
Back in the form code the new method is accessible on the Intellisense list.
Figure 8 - Customized Method in Intellisense
Another example might be the need for a method that will copy the address information from the customer to the shipping section of the order.
Partial Public Class OrdersRow
Public Sub ShipToCustomer()
If Me.CustomerID Is Nothing Then
MsgBox("Customer not defined") 'TODO: should throw an exception
Return
End If
Dim CustTable As CustomersDataTable = Me.Table.DataSet.Tables("Customers")
Dim CustRow As CustomersRow = CustTable.FindByCustomerID(Me.CustomerID)
Me.ShipName = CustRow.ContactName
Me.ShipAddress = CustRow.Address
Me.ShipCity = CustRow.City
Me.ShipRegion = CustRow.Region
Me.ShipPostalCode = CustRow.PostalCode
Me.ShipCountry = CustRow.Country
End Sub
End Class
Back in the form code, when you get a reference to an OrderRow object, the method is available to copy the shipping address.
These are just a couple examples of how to build business logic in the partial class of the DataSet. The advantage is that all of the DataSet objects are available in context. If you were to create another specific class as your business object, you would always have to pass a reference to the dataset to access the data objects. In the above examples, the data object can be referenced as "Me" since the coding is inside the dataset object.
