NorthWind Orders Application

The Northwind orders sample application demonstrates several additional databinding examples including the use of DataGrids. Other highlights of this sample application include:

 

 

 

 

To learn more about this sample application:

 

 

 

Northwind Orders Code Examples

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.

 

Customizations made to DataGridMaster

 

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

 

 

 

Customizations to DataGridDetail

 

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

 

 

 

Create New Order

 

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.