The Northwind orders sample application demonstrates several additional databinding examples including the use of DataGrids. Other highlights of this sample application include:
Using QueryCriteria and FetchPath to retrieve selected data into an ORM.NET generated collection
Binding a collection to two different types of grids with related data. - A simple Master grid as well as a Detail grid which allows drilling down from Orders to OrderDetails
Use of Template and Column styles within the grids.
Customizing the generated data objects to affect how data is displayed within the grids
The use of Lookup objects to display static data in a Combo Box
Working with different data types
Creating new records which span multiple tables using CommitAll()
To learn more about this sample application:
If you have not already done so, review Use ORM.NET in Six Simple Steps to get a basic idea of how to work with ORM.NET to generate a Data Access Layer using the OleroTraining database
Review the Code Examples to learn the basic syntax to use when retrieving and/or modifying data using ORM.NET objects.
Refer to Creating the Employee Viewer application to see which customizations were made to the data object model within the ORM.NET object browser
The first two lines of the application will determine what data is displayed on the main form. The QueryCriteria statement will filter the Customers collection for all records where the Country column is either France or Germany.
' Find Customers who are in France or Germany
data.QueryCriteria.And(JoinPath.Customer.Columns.Country, "France").Or(JoinPath.Customer.Columns.Country, "Germany")
' Retrieve the Customers who satisfy the QueryCriteria along with related Order and OrderDetail information
customers = data.GetCustomerCollection(FetchPath.Customer.Order.OrderDetail)
There are two grids on this form. DataGridMaster displays a list of each customer. DataGridDetail displays the related Order and OrderDetail information for each customer that is selected by the Master DataGrid.
1. The DataSource property of the DataGrid DataGridMaster is set to the customers collection variable name.
Private customers As CustomerCollection
Public Sub New()
DataGridMaster.DataSource = customers
End Sub
2. The AllowNavigation property of the DataGrid is set to False so that only the root object - Customer - is displayed in this grid.
3. Click the ellipsis to the right of the TableStyles property to open the DataGridStyle Collection Editor.
4. Click the Add button a new DataGridStyle Member named DataGridStyleCustomer
4. Set the MappingName property to be CustomerCollection object
5. Click the on ellipsis to the right of the GridColumnStyles property to open up the DataGridColumn Style Editor.
6. Click the Add button to define a new GridColumnStyle named DataGridTextBoxCustomerID
7. Set the Header Text property to Customer Id
8. Set the MappingName to CustomerId
9. Repeat steps 6 through 8 to define the following GridColumnStyles.
Property |
Value |
Name |
DataGridTextBoxCompanyName |
Header Text Property |
Company Name |
MappingName |
CompanyName |
Property |
Value |
Name |
DataGridTextBoxContactName |
Header Text Property |
Contact Name |
MappingName |
ContactName |
Property |
Value |
Name |
DataGridTextBoxCity |
Header Text Property |
City |
MappingName |
City |
Property |
Value |
Name |
DataGridTextBoxCompanyName |
Header Text Property |
Postal Code |
MappingName |
PostalCode |
Property |
Value |
Name |
DataGridTextBoxCompanyName |
Header Text Property |
Country |
MappingName |
Country |
Property |
Value |
Name |
DataGridTextBoxLastOrderDate |
Header Text Property |
Last Order Date |
MappingName |
LastOrderDate |
7. The last DataGridColumn Style defined - LastOrderDate - is a custom property that was added to the Customer data object. This custom property allows us to determine and display the last time the selected Customer ordered products. The implementation of the LastOrderDate property is shown below.
Public ReadOnly Property LastOrderDate() As System.Data.SqlTypes.SqlDateTime
Get
If MyBase.Orders.Count > 0 Then
Return MyBase.Orders.SortByOrderDate(SortDirection.Descending)(0).OrderDate
Else
Return SqlDateTime.Null
End If
End Get
End Property
The DataGridDetail displays related Order and OrderDetail information about each selected customer in the DataGridMaster DataGrid. All of the information contained in this grid was retrieved at the same time as the Customer information by the GetCustomerCollection method call which specified the FetchPath statement as shown below.
'Retrieve Customer and related Order and OrderDetail information in ONE round trip to the db
customers = data.GetCustomerCollection(FetchPath.Customer.Order.OrderDetail)
The DataGridDetail has two DataGridTable Styles defined
1. Click on ellipses to the right of the TableStyles property of the DataGridDetail object to open the DataGridTableSytles Collection Editor.
2. Click the Add button to create a DataGridTableStyle named DataGridTableStyleOrders
3. Set the MappingName property to Orders.
4. Click the Ellipsis to the right of the GridColumnStyles property to open the DataGridColumn Styles Editor.
5. Add the following DataGridColumn Styles to the DataGridTableStyleOrders.
Property |
Value |
Name |
DataGridTextBoxOrderDate |
Header Text Property |
Order Date |
MappingName |
OrderDate |
Property |
Value |
Name |
DataGridTextBoxShippedDate |
Header Text Property |
Shipped Date |
MappingName |
ShippedDate |
Property |
Value |
Name |
DataGridTextBoxFreight |
Header Text Property |
Freight |
MappingName |
Freight |
Property |
Value |
Name |
DataGridTextBoxShipper |
Header Text Property |
Shipper |
MappingName |
Shippers |
The Shippers DataGridColumn maps to the Shippers table through the Order.ShipVia column which is the foreign key of Shippers.ShipperId.
Instead of displaying the ShipVia Id from Orders we can easily override the ToString method of the Shippers class to instead return the CompanyName column of the Shippers table. To implement this functionality we can add the following Function to the Shippers class.
Public Overrides Function ToString() As String
Return MyBase.CompanyName
End Function
Because the Shippers table object is defined as a Lookup the information from this table will be returned and cached within the Grid
4. Create another Table Style named DataGridTableStyleOrderDetails. Set the MappingName property of this TableStyle to OrderDetails
5. Click the Add button to add the following DataGridColumnStyles for the DataGridTableStyleOrderDetails style.
Property |
Value |
Name |
DataGridTextBoxProductName |
Header Text Property |
Product Name |
MappingName |
ProductName |
Property |
Value |
Name |
DataGridTextBoxUnitPrice |
Header Text Property |
Unit Price |
MappingName |
UnitPrice |
Property |
Value |
Name |
DataGridTextBoxQuantity |
Header Text Property |
Quantity |
MappingName |
Quantity |
6. The ToString method was overridden in the Products class to display the ProductName column from the Products table through the OrderDetails column ProductId relationship.
Public Overrides Function ToString() As String
Return MyBase.ProductName
End Function
Clicking the Create New Order button opens up a simple form which prompts a user to enter the required information so that a new Order can be created in the Northwind database. To make filling this information easier the user can select from a drop-down list to choose which Shippers and Products to use for this order.
The constructor of the CreateCustomerOrder form was modified to pass in the same DataManager instance we created in Northwind Orders form.
(From NorthwindOrders.vb)
' Create an instance of the form and pass in the DataManager object
Dim createOrder As New CreateCustomerOrder(data)
2. When the CreateCustomerOrder form loads it will set its own internal copy of DataManager
Public Sub New(ByVal dataManager As DataManager)
MyBase.New()
' Set the current DataManger context for this form
data = dataManager
3. Next, a Lookup object is created for both the Products and Shippers lookup are initialized and bound to the two ComboBoxes.
Lookups lookups = new Lookups(Config.Dsn);
comboBoxProduct.DataSource = lookups.Products;
comboBoxProduct.DisplayMember = "ProductName";
comboBoxShipVia.DataSource = lookups.Shippers;
comboBoxShipVia.DisplayMember = "CompanyName";
4. Once the new information is entered click the Save button. The Customer, Order and OrderDetail information are saved into the DataSet.
customer = data.NewCustomer(TextBoxCustomerID.Text, TextBoxCompanyName.Text)
' Set any additional information about this customer
customer.ContactName = TextBoxContactName.Text
customer.Country = "Germany"
' Create a new Child object Order
Dim order As Order = customer.NewOrder()
order.OrderDate = SqlDateTime.Parse(DateTimePicker1.Value.ToString)
order.Shippers = ComboBoxShippers.SelectedValue
'Create a New Child - OrderDetail
Dim orderDetail As OrderDetail = order.NewOrderDetail()
orderDetail.Products = ComboBoxProducts.SelectedValue
orderDetail.UnitPrice = SqlMoney.Parse(TextBoxUnitPrice.Text)
orderDetail.Quantity = SqlInt16.Parse(NumericUpDownQuantity.Value)
5. When the CreateNewOrderForm is closed a copy of the customer object will be passed back to the ViewOrders form through the Property NewCustomer
[CreateNewOrderForm.vb]
Public ReadOnly Property newCustomer() As customer
Get
Return customer
End Get
End Property
[ViewOrders.vb]
' Add the new information to the customers Collection
If dr = DialogResult.OK Then
customers.Add(createOrder.newcustomer)
End If
The new Customer, Order and OrderDetail information is now visible within the Grid as the last row.
6. Clicking the Persist Changes to DB will save the data to the database by calling the CommitAll() method of the DataManager object.
data.CommitAll()
MessageBox.Show(data.LastCommitText)
The LastCommitText will display the SQL that is generated to update the database.