Walkthrough: Connecting to Data in Objects (Windows Forms)

Article
08/19/2012

This walkthrough creates objects to hold customer and order data, together with an object data source for each object. The object data source appears in the Data Sources window: items are dragged from that window onto a form to create controls that are bound to the data in the public properties for each object. The walkthrough also shows how to use TableAdapters to fetch data from the database and populate the objects.

The object data source is created by running the Data Source Configuration Wizard and selecting Object as the type of data source. After you complete the Data Source Configuration Wizard, the public properties of the object are available in the Data Sources Window and they can be dragged onto your form.

Tasks illustrated in this walkthrough include the following:

Creating a new Windows Forms Application project.
Creating custom objects to represent customers and orders.
Creating and configuring an object data source that is based on the custom objects by using the Data Source Configuration Wizard.
Adding controls to a form that are bound to the data in the custom objects.
Creating a dataset with TableAdapters to move data between your objects and the database.
Editing a TableAdapter's main query.
Adding queries to a TableAdapter.
Populating your objects with data from the database.

Creating the Project

To create the new Windows Forms Application project

From the File menu, create a New Project.
In the Project Types pane, under the node for the language you want to use, click Windows.
In the Templates pane, click Windows Forms Application.
In the Name box, type ObjectBindingWalkthrough and click OK.

The ObjectBindingWalkthrough project is created and added to Solution Explorer.

This walkthrough needs some objects to bind to. The first step is to create some sample objects to represent customers and orders. To represent customers we will create a Customer object that represents a single customer. To represent orders we will create an Order object, which represents a single order, and an Orders object, which represents a collection of Order objects. For the collection of Customer objects we will use the built-in collection in the BindingSource class (explained later in this walkthrough).

Creating the Customer Object

To create the Customer object

On the Project menu, click Add Class.
Name the new class Customer and click Add.

Replace the code in the Customer class file with the following code:

Note

The Customer object contains an ordersCollection property of the type Orders. The editor will display a message stating Type 'Orders' is not defined. This message is expected and will disappear when you create the Order and Orders classes in the next section.

''' <summary> 
''' A single customer 
''' </summary> 
Public Class Customer

    Public Sub New()
    End Sub 

    ''' <summary> 
    ''' Creates a new customer 
    ''' </summary> 
    ''' <param name="customerId">The ID that uniquely identifies this customer</param> 
    ''' <param name="companyName">The name for this customer</param> 
    ''' <param name="contactName">The name for this customer's contact</param> 
    ''' <param name="contactTitle">The title for this contact</param> 
    ''' <param name="address">The address for this customer</param> 
    ''' <param name="city">The city for this customer</param> 
    ''' <param name="region">The region for this customer</param> 
    ''' <param name="postalCode">The postal code for this customer</param> 
    ''' <param name="country">The country for this customer</param> 
    ''' <param name="phone">The phone number for this customer</param> 
    ''' <param name="fax">The fax number for this customer</param> 
    Public Sub New(ByVal customerId As String,
                   ByVal companyName As String,
                   ByVal contactName As String,
                   ByVal contactTitle As String,
                   ByVal address As String,
                   ByVal city As String,
                   ByVal region As String,
                   ByVal postalCode As String,
                   ByVal country As String,
                   ByVal phone As String,
                   ByVal fax As String)
        customerIDValue = customerId
        companyNameValue = companyName
        contactNameValue = contactName
        contactTitleValue = contactTitle
        addressValue = address
        cityValue = city
        regionValue = region
        postalCodeValue = postalCode
        countryValue = country
        phoneValue = phone
        faxValue = fax
    End Sub 

    Private customerIDValue As String 
    ''' <summary> 
    ''' The ID that uniquely identifies this customer 
    ''' </summary> 
    Public Property CustomerID() As String 
        Get 
            Return customerIDValue
        End Get 
        Set(ByVal value As String)
            customerIDValue = value
        End Set 
    End Property 

    Private companyNameValue As String 
    ''' <summary> 
    ''' The name for this customer 
    ''' </summary> 
    Public Property CompanyName() As String 
        Get 
            Return companyNameValue
        End Get 
        Set(ByVal Value As String)
            companyNameValue = Value
        End Set 
    End Property 

    Private contactNameValue As String 
    ''' <summary> 
    ''' The name for this customer's contact 
    ''' </summary> 
    Public Property ContactName() As String 
        Get 
            Return contactNameValue
        End Get 
        Set(ByVal Value As String)
            contactNameValue = Value
        End Set 
    End Property 

    Private contactTitleValue As String 
    ''' <summary> 
    ''' The title for this contact 
    ''' </summary> 
    Public Property ContactTitle() As String 
        Get 
            Return contactTitleValue
        End Get 
        Set(ByVal Value As String)
            contactTitleValue = Value
        End Set 
    End Property 

    Private addressValue As String 
    ''' <summary> 
    ''' The address for this customer 
    ''' </summary> 
    Public Property Address() As String 
        Get 
            Return addressValue
        End Get 
        Set(ByVal Value As String)
            addressValue = Value
        End Set 
    End Property 

    Private cityValue As String 
    ''' <summary> 
    ''' The city for this customer 
    ''' </summary> 
    Public Property City() As String 
        Get 
            Return cityValue
        End Get 
        Set(ByVal Value As String)
            cityValue = Value
        End Set 
    End Property 

    Private regionValue As String 
    ''' <summary> 
    ''' The region for this customer 
    ''' </summary> 
    Public Property Region() As String 
        Get 
            Return regionValue
        End Get 
        Set(ByVal Value As String)
            regionValue = Value
        End Set 
    End Property 

    Private postalCodeValue As String 
    ''' <summary> 
    ''' The postal code for this customer 
    ''' </summary> 
    Public Property PostalCode() As String 
        Get 
            Return postalCodeValue
        End Get 
        Set(ByVal Value As String)
            postalCodeValue = Value
        End Set 
    End Property 

    Private countryValue As String 
    ''' <summary> 
    ''' The country for this customer 
    ''' </summary> 
    Public Property Country() As String 
        Get 
            Return countryValue
        End Get 
        Set(ByVal Value As String)
            countryValue = Value
        End Set 
    End Property 


    Private phoneValue As String 
    ''' <summary> 
    ''' The phone number for this customer 
    ''' </summary> 
    Public Property Phone() As String 
        Get 
            Return phoneValue
        End Get 
        Set(ByVal Value As String)
            phoneValue = Value
        End Set 
    End Property 

    Private faxValue As String 
    ''' <summary> 
    ''' The fax number for this customer 
    ''' </summary> 
    Public Property Fax() As String 
        Get 
            Return faxValue
        End Get 
        Set(ByVal Value As String)
            faxValue = Value
        End Set 
    End Property 

    Private ordersCollection As New System.ComponentModel.BindingList(Of Order)
    ''' <summary> 
    ''' The orders for this customer 
    ''' </summary> 
    Public Property Orders() As System.ComponentModel.BindingList(Of Order)
        Get 
            Return ordersCollection
        End Get 
        Set(ByVal value As System.ComponentModel.BindingList(Of Order))
            ordersCollection = value
        End Set 
    End Property 


    Public Overrides Function ToString() As String 
        Return Me.CompanyName & " (" & Me.CustomerID & ")" 
    End Function 

End Class

namespace ObjectBindingWalkthrough
{
    /// <summary> 
    /// A single customer 
    /// </summary> 
    public class Customer
    {
        /// <summary> 
        /// Creates a new customer 
        /// </summary> 
        public Customer()
        {
        }

        /// <summary> 
        /// Creates a new customer 
        /// </summary> 
        /// <param name="customerID"></param>
        /// <param name="companyName"></param>
        /// <param name="contactName"></param>
        /// <param name="contactTitle"></param>
        /// <param name="address"></param>
        /// <param name="city"></param>
        /// <param name="region"></param>
        /// <param name="postalCode"></param>
        /// <param name="country"></param>
        /// <param name="phone"></param>
        /// <param name="fax"></param>
        public Customer(string customerID, string companyName,
           string contactName, string contactTitle,
           string address, string city, string region,
           string postalCode, string country,
           string phone, string fax)
        {
            customerIDValue = customerID;
        }

        private string customerIDValue;
        /// <summary> 
        /// The ID that uniquely identifies this customer 
        /// </summary> 
        public string CustomerID
        {
            get { return customerIDValue; }
            set { customerIDValue = value; }
        }

        private string companyNameValue;
        /// <summary> 
        /// The name for this customer 
        /// </summary> 
        public string CompanyName
        {
            get { return companyNameValue; }
            set { companyNameValue = value; }
        }

        private string contactNameValue;
        /// <summary> 
        /// The name for this customer's contact 
        /// </summary> 
        public string ContactName
        {
            get { return contactNameValue; }
            set { contactNameValue = value; }
        }

        private string contactTitleValue;
        /// <summary> 
        /// The title for this contact 
        /// </summary> 
        public string ContactTitle
        {
            get { return contactTitleValue; }
            set { contactTitleValue = value; }
        }

        private string addressValue;
        /// <summary> 
        /// The address for this customer 
        /// </summary> 
        public string Address
        {
            get { return addressValue; }
            set { addressValue = value; }
        }

        private string cityValue;
        /// <summary> 
        /// The city for this customer 
        /// </summary> 
        public string City
        {
            get { return cityValue; }
            set { cityValue = value; }
        }

        private string regionValue;
        /// <summary> 
        /// The region for this customer 
        /// </summary> 
        public string Region
        {
            get { return regionValue; }
            set { regionValue = value; }
        }

        private string postalCodeValue;
        /// <summary> 
        /// The postal code for this customer 
        /// </summary> 
        public string PostalCode
        {
            get { return postalCodeValue; }
            set { postalCodeValue = value; }
        }

        private string countryValue;
        /// <summary> 
        /// The country for this customer 
        /// </summary> 
        public string Country
        {
            get { return countryValue; }
            set { countryValue = value; }
        }

        private string phoneValue;
        /// <summary> 
        /// The phone number for this customer 
        /// </summary> 
        public string Phone
        {
            get { return phoneValue; }
            set { phoneValue = value; }
        }

        private string faxValue;
        /// <summary> 
        /// The fax number for this customer 
        /// </summary> 
        public string Fax
        {
            get { return faxValue; }
            set { faxValue = value; }
        }

        private System.ComponentModel.BindingList<Order> ordersCollection = 
            new System.ComponentModel.BindingList<Order>();

        public System.ComponentModel.BindingList<Order> Orders
        {
            get { return ordersCollection; }
            set { ordersCollection = value; }
        }

        public override string ToString()
        {
            return this.CompanyName + " (" + this.CustomerID + ")";
        }
    }
}

Creating the Order Objects

To create the Order object and Orders collection

On the Project menu, select Add Class.
Name the new class Order and click Add.

Replace the code in the Order class file with the following code:

''' <summary> 
''' A single order 
''' </summary> 
Public Class Order

    Public Sub New()
    End Sub 

    ''' <summary> 
    ''' Creates a new order 
    ''' </summary> 
    ''' <param name="orderid">The identifier for this order</param> 
    ''' <param name="customerID">The customer who placed this order</param> 
    ''' <param name="employeeID">The ID of the employee who took this order</param> 
    ''' <param name="orderDate">The date this order was placed</param> 
    ''' <param name="requiredDate">The date this order is required</param> 
    ''' <param name="shippedDate">The date the order was shipped</param> 
    ''' <param name="shipVia">The shipping method for this order</param> 
    ''' <param name="freight">The freight charge for this order</param> 
    ''' <param name="shipName">The name of the recipient for this order</param> 
    ''' <param name="shipAddress">The address to ship this order to</param> 
    ''' <param name="shipCity">The city to ship this order to</param> 
    ''' <param name="shipRegion">The region to ship this order to</param> 
    ''' <param name="shipPostalCode">The postal code to ship this order to</param> 
    ''' <param name="shipCountry">The country to ship this order to</param> 
    Public Sub New(ByVal orderid As Integer,
                   ByVal customerID As String,
                   ByVal employeeID As Nullable(Of Integer),
                   ByVal orderDate As Nullable(Of DateTime),
                   ByVal requiredDate As Nullable(Of DateTime),
                   ByVal shippedDate As Nullable(Of DateTime),
                   ByVal shipVia As Nullable(Of Integer),
                   ByVal freight As Nullable(Of Decimal),
                   ByVal shipName As String,
                   ByVal shipAddress As String,
                   ByVal shipCity As String,
                   ByVal shipRegion As String,
                   ByVal shipPostalCode As String,
                   ByVal shipCountry As String)
        orderIDValue = orderid
        customerIDValue = customerID
        employeeIDValue = employeeID
        orderDateValue = orderDate
        requiredDateValue = requiredDate
        shippedDateValue = shippedDate
        shipViaValue = shipVia
        freightValue = freight
        shipAddressValue = shipAddress
        shipCityValue = shipCity
        shipRegionValue = shipRegion
        shipPostalCodeValue = shipPostalCode
        shipCountryValue = shipCountry
    End Sub 

    Private orderIDValue As Integer 
    ''' <summary> 
    ''' Identifier for this order 
    ''' </summary> 
    Public Property OrderID() As Integer 
        Get 
            Return orderIDValue
        End Get 
        Set(ByVal value As Integer)
            orderIDValue = value
        End Set 
    End Property 

    Private customerIDValue As String 
    ''' <summary> 
    ''' The customer who placed this order 
    ''' </summary> 
    Public Property CustomerID() As String 
        Get 
            Return customerIDValue
        End Get 
        Set(ByVal Value As String)
            customerIDValue = Value
        End Set 
    End Property 

    Private employeeIDValue As Nullable(Of Integer)
    ''' <summary> 
    ''' The ID of the employee who took this order 
    ''' </summary> 
    Public Property EmployeeID() As Nullable(Of Integer)
        Get 
            Return employeeIDValue
        End Get 
        Set(ByVal Value As Nullable(Of Integer))
            employeeIDValue = Value
        End Set 
    End Property 


    Private orderDateValue As Nullable(Of DateTime)

    ''' <summary> 
    ''' The date this order was placed 
    ''' </summary> 
    Public Property OrderDate() As Nullable(Of DateTime)
        Get 
            Return orderDateValue
        End Get 
        Set(ByVal Value As Nullable(Of DateTime))
            orderDateValue = Value
        End Set 
    End Property 

    Private requiredDateValue As Nullable(Of DateTime)
    ''' <summary> 
    ''' The date this order is required 
    ''' </summary> 
    Public Property RequiredDate() As Nullable(Of DateTime)
        Get 
            Return requiredDateValue
        End Get 
        Set(ByVal Value As Nullable(Of DateTime))
            requiredDateValue = Value
        End Set 
    End Property 


    Private shippedDateValue As Nullable(Of DateTime)
    ''' <summary> 
    ''' The date this order was shipped 
    ''' </summary> 
    Public Property ShippedDate() As Nullable(Of DateTime)
        Get 
            Return shippedDateValue
        End Get 
        Set(ByVal Value As Nullable(Of DateTime))
            shippedDateValue = Value
        End Set 
    End Property 

    Private shipViaValue As Nullable(Of Integer)
    ''' <summary> 
    ''' The shipping method for this order 
    ''' </summary> 
    Public Property ShipVia() As Nullable(Of Integer)
        Get 
            Return shipViaValue
        End Get 
        Set(ByVal Value As Nullable(Of Integer))
            shipViaValue = Value
        End Set 
    End Property 


    Private freightValue As Nullable(Of Decimal)
    ''' <summary> 
    ''' The freight charge for this order 
    ''' </summary> 
    Public Property Freight() As Nullable(Of Decimal)
        Get 
            Return freightValue
        End Get 
        Set(ByVal Value As Nullable(Of Decimal))
            freightValue = Value
        End Set 
    End Property 

    Private shipNameValue As String 
    ''' <summary> 
    ''' The name of the recipient for this order 
    ''' </summary> 
    Public Property ShipName() As String 
        Get 
            Return shipNameValue
        End Get 
        Set(ByVal Value As String)
            shipNameValue = Value
        End Set 
    End Property 


    Private shipAddressValue As String 
    ''' <summary> 
    ''' The address to ship this order to 
    ''' </summary> 
    Public Property ShipAddress() As String 
        Get 
            Return shipAddressValue
        End Get 
        Set(ByVal Value As String)
            shipAddressValue = Value
        End Set 
    End Property 

    Private shipCityValue As String 
    ''' <summary> 
    ''' The city to ship this order to 
    ''' </summary> 
    Public Property ShipCity() As String 
        Get 
            Return shipCityValue
        End Get 
        Set(ByVal Value As String)
            shipCityValue = Value
        End Set 
    End Property 

    Private shipRegionValue As String 
    ''' <summary> 
    ''' The region to ship this order to 
    ''' </summary> 
    Public Property ShipRegion() As String 
        Get 
            Return shipRegionValue
        End Get 
        Set(ByVal Value As String)
            shipRegionValue = Value
        End Set 
    End Property 

    Private shipPostalCodeValue As String 
    ''' <summary> 
    ''' The postal code to ship this order to 
    ''' </summary> 
    Public Property ShipPostalCode() As String 
        Get 
            Return shipPostalCodeValue
        End Get 
        Set(ByVal Value As String)
            shipPostalCodeValue = Value
        End Set 
    End Property 

    Private shipCountryValue As String 
    ''' <summary> 
    ''' The country to ship this order to 
    ''' </summary> 
    Public Property ShipCountry() As String 
        Get 
            Return shipCountryValue
        End Get 
        Set(ByVal Value As String)
            shipCountryValue = Value
        End Set 
    End Property 


    Private customerValue As Customer
    ''' <summary> 
    ''' The customer this order belongs to 
    ''' </summary> 
    Public Property Customer() As Customer
        Get 
            Return customerValue
        End Get 
        Set(ByVal Value As Customer)
            customerValue = Value
        End Set 
    End Property 


End Class 

''' <summary> 
''' A collection of Orders 
''' </summary> 
Public Class Orders
    Inherits System.ComponentModel.BindingList(Of Order)

End Class

using System;

namespace ObjectBindingWalkthrough
{
    /// <summary> 
    /// A single order 
    /// </summary> 
    public class Order
    {
        /// <summary> 
        /// Creates a new order 
        /// </summary> 
        public Order()
        {
        }

        /// <summary> 
        /// Creates a new order 
        /// </summary> 
        /// <param name="orderid"></param>
        /// <param name="customerID"></param>
        /// <param name="employeeID"></param>
        /// <param name="orderDate"></param>
        /// <param name="requiredDate"></param>
        /// <param name="shippedDate"></param>
        /// <param name="shipVia"></param>
        /// <param name="freight"></param>
        /// <param name="shipName"></param>
        /// <param name="shipAddress"></param>
        /// <param name="shipCity"></param>
        /// <param name="shipRegion"></param>
        /// <param name="shipPostalCode"></param>
        /// <param name="shipCountry"></param>
        public Order(int orderid, string customerID,
           Nullable<int> employeeID, Nullable<DateTime> orderDate,
           Nullable<DateTime> requiredDate, Nullable<DateTime> shippedDate,
           Nullable<int> shipVia, Nullable<decimal> freight,
           string shipName, string shipAddress,
           string shipCity, string shipRegion,
           string shipPostalCode, string shipCountry)
        {

        }

        private int orderIDValue;
        /// <summary> 
        /// The ID that uniquely identifies this order 
        /// </summary> 
        public int OrderID
        {
            get { return orderIDValue; }
            set { orderIDValue = value; }
        }

        private string customerIDValue;
        /// <summary> 
        /// The customer who placed this order 
        /// </summary> 
        public string CustomerID
        {
            get { return customerIDValue; }
            set { customerIDValue = value; }
        }

        private Nullable<int> employeeIDValue;
        /// <summary> 
        /// The ID of the employee who took this order 
        /// </summary> 
        public Nullable<int> EmployeeID
        {
            get { return employeeIDValue; }
            set { employeeIDValue = value; }
        }

        private Nullable<DateTime> orderDateValue;
        /// <summary> 
        /// The date this order was placed 
        /// </summary> 
        public Nullable<DateTime> OrderDate
        {
            get { return orderDateValue; }
            set { orderDateValue = value; }
        }

        private Nullable<DateTime> requiredDateValue;
        /// <summary> 
        /// The date this order is required 
        /// </summary> 
        public Nullable<DateTime> RequiredDate
        {
            get { return requiredDateValue; }
            set { requiredDateValue = value; }
        }

        private Nullable<DateTime> shippedDateValue;
        /// <summary> 
        /// The date this order was shipped 
        /// </summary> 
        public Nullable<DateTime> ShippedDate
        {
            get { return shippedDateValue; }
            set { shippedDateValue = value; }
        }

        private Nullable<int> shipViaValue;
        /// <summary> 
        /// The shipping method of this order 
        /// </summary> 
        public Nullable<int> ShipVia
        {
            get { return shipViaValue; }
            set { shipViaValue = value; }
        }

        private Nullable<decimal> freightValue;
        /// <summary> 
        /// The freight charge for this order 
        /// </summary> 
        public Nullable<decimal> Freight
        {
            get { return freightValue; }
            set { freightValue = value; }
        }

        private string shipNameValue;
        /// <summary> 
        /// The name of the recipient for this order 
        /// </summary> 
        public string ShipName
        {
            get { return shipNameValue; }
            set { shipNameValue = value; }
        }

        private string shipAddressValue;
        /// <summary> 
        /// The address to ship this order to 
        /// </summary> 
        public string ShipAddress
        {
            get { return shipAddressValue; }
            set { shipAddressValue = value; }
        }

        private string shipCityValue;
        /// <summary> 
        /// The city to ship this order to 
        /// </summary> 
        public string ShipCity
        {
            get { return shipCityValue; }
            set { shipCityValue = value; }
        }

        private string shipRegionValue;
        /// <summary> 
        /// The region to ship this order to 
        /// </summary> 
        public string ShipRegion
        {
            get { return shipRegionValue; }
            set { shipRegionValue = value; }
        }

        private string shipPostalCodeValue;
        /// <summary> 
        /// The postal code to ship this order to 
        /// </summary> 
        public string ShipPostalCode
        {
            get { return shipPostalCodeValue; }
            set { shipPostalCodeValue = value; }
        }

        private string shipCountryValue;
        /// <summary> 
        /// The country to ship this order to 
        /// </summary> 
        public string ShipCountry
        {
            get { return shipCountryValue; }
            set { shipCountryValue = value; }
        }

    }


    /// <summary> 
    /// A collection of Order objects 
    /// </summary> 
    class Orders : System.ComponentModel.BindingList<Order>
    {

    }
}

From the File menu, choose Save All.

Creating the Object Data Source

You can create a data source based on the objects created in the previous step by running the Data Source Configuration Wizard.

To create the object data source

Build your project.

Note

You must build the project before objects in the project can be selected in the Data Source Configuration Wizard.
Open the Data Sources window by clicking the Data menu and selecting Show Data Sources.
Click Add New Data Source in the Data Sources window.

The Data Source Configuration Wizard starts.
On the Choose a Data Source Type page, select Object, and then click Next.
On the Select the Data Objects page, expand the ObjectBindingWalkthrough nodes and select the check box next to the Customer object.
Click Finish.

The Customer object appears in the Data Sources window.

Creating a Data-bound Form

Controls bound to the Customer object are created by dragging items from the Data Sources window onto a form.

To create a form with controls bound to the object properties

In Solution Explorer, select Form1, and click View Designer.
Drag the Customer node from the Data Sources window onto Form1.
Expand the Customer node and drag the Orders node from the Data Sources window onto Form1.

Creating TableAdapters to Load Data from the Database into the Custom Objects

To move data between the objects and the database, we will use TableAdapters. You can create TableAdapters for the Customers and Orders tables by using the Data Source Configuration Wizard.

To create the TableAdapters

From the Data menu, choose Add New Data Source.
On the Choose a Data Source Type page, select Database, and then click Next.
On the Choose a Database Model page, select Dataset, and then click Next.
On the Choose Your Data Connection page, use one of the following procedures:
- If a data connection to the Northwind sample database is available in the drop-down list, select it.
  
  -or-
- Select New Connection to configure a new data connection to the Northwind database. For more information, see How to: Connect to Data in a Database.
After you select a data connection, click Next.
On the Save connection string to the Application Configuration file page, click Next.
On the Choose your Database Objects page, expand the Tables node.
Select the Customers and Orders tables, and then click Finish.

The NorthwindDataSet is added to your project and the Customers and Orders tables appear in the Data Sources window under the NorthwindDataSet node.

Adding the Dataset and TableAdapters to Form1

You can add instances of the CustomersTableAdapter, OrdersTableAdapter, and NorthwindDataSet to the form by dragging their representative components from the Toolbox.

To fill the Customer objects with data from the Customers table

From the Build menu, select Build Solution.
Drag a NorthwindDataSet from the Toolbox onto Form1.
Drag a CustomersTableAdapter from the Toolbox onto Form1.
Drag an OrdersTableAdapter from the Toolbox onto Form1.

Adding a Query to the CustomersTableAdapter to Return Only a Few Customers

In real-world applications, you will likely never return the whole table of data. For this walkthrough we will return the top five customers.

Note

You would typically pass in a parameter to select which customers you want to return, but for brevity in this walkthrough we will hard-code the query to return only five customers and eliminate the need of creating a user interface for inputting parameter values.

To add an additional query to the CustomersTableAdapter

In Solution Explorer, double-click the NorthwindDataSet.xsd file.

The NorthwindDataSet opens in the Dataset Designer.
Right-click the CustomersTableAdapter and select Add Query.

The TableAdapter Query Configuration Wizard opens.
Leave the default of Use SQL statements and click Next.
Leave the default of SELECT which returns rows and click Next.

Replace the SQL statement with the following and click Next:

SELECT Top 5 CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax 
FROM Customers

Clear the Fill a DataTable check box.
Name the Return a DataTable method GetTop5Customers and click Finish.

The GetTop5Customers query is added to the CustomersTableAdapter.

Modifying the Query on the OrdersTableAdapter To Return Only Orders for the Desired Customer

When fetching orders from the database, we do not want to return the whole table of orders; we only want the orders for a specific customer. The following procedure details how to reconfigure a TableAdapter with a new query (as opposed to adding an additional query as we did to the CustomersTableAdapter in the previous step).

To reconfigure the TableAdapter's main query to return a single customer's orders

Right-click the OrdersTableAdapter and choose Configure.

The TableAdapter Query Configuration Wizard opens.

Replace the SQL statement with the following and click Next:

SELECT OrderID, CustomerID, EmployeeID, OrderDate, 
RequiredDate, ShippedDate, ShipVia, Freight, 
ShipName, ShipAddress, ShipCity, ShipRegion, 
ShipPostalCode, ShipCountry 
FROM Orders 
WHERE CustomerID = @CustomerID

Clear the Fill a DataTable check box.
Name the Return a DataTable method GetDataByCustomerID and click Finish.

The OrdersTableAdapter's main Fill query is replaced with the GetDataByCustomerID query.
Build the project by selecting Build Solution from the Build menu.

Adding Code to Load Data into the Customer and Order Objects

To load data into our custom objects, you execute the TableAdapter queries that return new data tables (rather than using TableAdapter queries that fill existing data tables). The code then loops through the table and populates each Customer object with the customer information, in addition to populating all orders in each Customer.Orders collection. Notice how each Customer object is added to the internal collection of the CustomerBindingSource (CustomerBindingSource.Add(currentCustomer)). The BindingSource provides a built-in strongly typed collection of Customers that can be accessed through the List property.

To load the objects with data

In Solution Explorer, select Form1, and click View Code.

Replace the code in Form1 with the following code:

Public Class Form1
    Private Sub LoadCustomers()
        Dim customerData As NorthwindDataSet.CustomersDataTable =
            CustomersTableAdapter1.GetTop5Customers()

        Dim customerRow As NorthwindDataSet.CustomersRow

        For Each customerRow In customerData
            Dim currentCustomer As New Customer()
            With currentCustomer

                .CustomerID = customerRow.CustomerID
                .CompanyName = customerRow.CompanyName

                If Not customerRow.IsAddressNull Then
                    .Address = customerRow.Address
                End If 

                If Not customerRow.IsCityNull Then
                    .City = customerRow.City
                End If 

                If Not customerRow.IsContactNameNull Then
                    .ContactName = customerRow.ContactName
                End If 

                If Not customerRow.IsContactTitleNull Then
                    .ContactTitle = customerRow.ContactTitle
                End If 

                If Not customerRow.IsCountryNull Then
                    .Country = customerRow.Country
                End If 

                If Not customerRow.IsFaxNull Then
                    .Fax = customerRow.Fax
                End If 

                If Not customerRow.IsPhoneNull Then
                    .Phone = customerRow.Phone
                End If 

                If Not customerRow.IsPostalCodeNull Then
                    .PostalCode = customerRow.PostalCode
                End If 

                If Not customerRow.Is_RegionNull Then
                    .Region = customerRow._Region
                End If 

            End With

            LoadOrders(currentCustomer)
            CustomerBindingSource.Add(currentCustomer)
        Next 
    End Sub 

    Private Sub LoadOrders(ByRef currentCustomer As Customer)
        Dim orderData As NorthwindDataSet.OrdersDataTable =
            OrdersTableAdapter1.GetDataByCustomerID(currentCustomer.CustomerID)

        Dim orderRow As NorthwindDataSet.OrdersRow

        For Each orderRow In orderData
            Dim currentOrder As New Order()
            With currentOrder
                .OrderID = orderRow.OrderID
                .Customer = currentCustomer

                If Not orderRow.IsCustomerIDNull Then
                    .CustomerID = orderRow.CustomerID
                End If 

                If Not orderRow.IsEmployeeIDNull Then
                    .EmployeeID = orderRow.EmployeeID
                End If 

                If Not orderRow.IsFreightNull Then
                    .Freight = orderRow.Freight
                End If 

                If Not orderRow.IsOrderDateNull Then
                    .OrderDate = orderRow.OrderDate
                End If 

                If Not orderRow.IsRequiredDateNull Then
                    .RequiredDate = orderRow.RequiredDate
                End If 

                If Not orderRow.IsShipAddressNull Then
                    .ShipAddress = orderRow.ShipAddress
                End If 

                If Not orderRow.IsShipCityNull Then
                    .ShipCity = orderRow.ShipCity
                End If 

                If Not orderRow.IsShipCountryNull Then
                    .ShipCountry = orderRow.ShipCountry
                End If 

                If Not orderRow.IsShipNameNull Then
                    .ShipName = orderRow.ShipName
                End If 

                If Not orderRow.IsShippedDateNull Then
                    .ShippedDate = orderRow.ShippedDate
                End If 

                If Not orderRow.IsShipPostalCodeNull Then
                    .ShipPostalCode = orderRow.ShipPostalCode
                End If 

                If Not orderRow.IsShipRegionNull Then
                    .ShipRegion = orderRow.ShipRegion
                End If 

                If Not orderRow.IsShipViaNull Then
                    .ShipVia = orderRow.ShipVia
                End If 
            End With
            currentCustomer.Orders.Add(currentOrder)
        Next 

    End Sub 

    Private Sub Form1_Load(ByVal sender As System.Object, 
                           ByVal e As System.EventArgs) Handles MyBase.Load

        LoadCustomers()
    End Sub 
End Class

using System;
using System.Windows.Forms;

namespace ObjectBindingWalkthrough
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.Load += Form1_Load;
        }

        private void LoadCustomers()
        {
            NorthwindDataSet.CustomersDataTable customerData = 
                customersTableAdapter1.GetTop5Customers();

            foreach (NorthwindDataSet.CustomersRow customerRow in customerData)
            {
                Customer currentCustomer = new Customer();
                currentCustomer.CustomerID = customerRow.CustomerID;
                currentCustomer.CompanyName = customerRow.CompanyName;

                if (customerRow.IsAddressNull() == false)
                {
                    currentCustomer.Address = customerRow.Address;
                }

                if (customerRow.IsCityNull() == false)
                {
                    currentCustomer.City = customerRow.City;
                }

                if (customerRow.IsContactNameNull() == false)
                {
                    currentCustomer.ContactName = customerRow.ContactName;
                }

                if (customerRow.IsContactTitleNull() == false)
                {
                    currentCustomer.ContactTitle = customerRow.ContactTitle;
                }

                if (customerRow.IsCountryNull() == false)
                {
                    currentCustomer.Country = customerRow.Country;
                }

                if (customerRow.IsFaxNull() == false)
                {
                    currentCustomer.Fax = customerRow.Fax;
                }

                if (customerRow.IsPhoneNull() == false)
                {
                    currentCustomer.Phone = customerRow.Phone;
                }

                if (customerRow.IsPostalCodeNull() == false)
                {
                    currentCustomer.PostalCode = customerRow.PostalCode;
                }

                if (customerRow.IsRegionNull() == false)
                {
                    currentCustomer.Region = customerRow.Region;
                }

                LoadOrders(currentCustomer);
                customerBindingSource.Add(currentCustomer);
            }
        }


        private void LoadOrders(Customer currentCustomer)
        {
            NorthwindDataSet.OrdersDataTable orderData = 
                ordersTableAdapter1.GetDataByCustomerID(currentCustomer.CustomerID);

            foreach (NorthwindDataSet.OrdersRow orderRow in orderData)
            {
                Order currentOrder = new Order();
                currentOrder.OrderID = orderRow.OrderID;

                if (orderRow.IsCustomerIDNull() == false)
                {
                    currentOrder.CustomerID = orderRow.CustomerID;
                }

                if (orderRow.IsEmployeeIDNull() == false)
                {
                    currentOrder.EmployeeID = orderRow.EmployeeID;
                }

                if (orderRow.IsFreightNull() == false)
                {
                    currentOrder.Freight = orderRow.Freight;
                }

                if (orderRow.IsOrderDateNull() == false)
                {
                    currentOrder.OrderDate = orderRow.OrderDate;
                }

                if (orderRow.IsRequiredDateNull() == false)
                {
                    currentOrder.RequiredDate = orderRow.RequiredDate;
                }

                if (orderRow.IsShipAddressNull() == false)
                {
                    currentOrder.ShipAddress = orderRow.ShipAddress;
                }

                if (orderRow.IsShipCityNull() == false)
                {
                    currentOrder.ShipCity = orderRow.ShipCity;
                }

                if (orderRow.IsShipCountryNull() == false)
                {
                    currentOrder.ShipCountry = orderRow.ShipCountry;
                }

                if (orderRow.IsShipNameNull() == false)
                {
                    currentOrder.ShipName = orderRow.ShipName;
                }

                if (orderRow.IsShippedDateNull() == false)
                {
                    currentOrder.ShippedDate = orderRow.ShippedDate;
                }

                if (orderRow.IsShipPostalCodeNull() == false)
                {
                    currentOrder.ShipPostalCode = orderRow.ShipPostalCode;
                }

                if (orderRow.IsShipRegionNull() == false)
                {
                    currentOrder.ShipRegion = orderRow.ShipRegion;
                }

                if (orderRow.IsShipViaNull() == false)
                {
                    currentOrder.ShipVia = orderRow.ShipVia;
                }
                currentCustomer.Orders.Add(currentOrder);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadCustomers();
        }

    }
}

Testing the Application

To test the application

Press F5 to run the application.
The form opens and the DataGridView controls are populated with the sample data.
Navigate the customers in the DataGridView to display their associated orders.

Next Steps

To add functionality to your application

Add code to send data from the custom objects back to the database. For more information, see How to: Directly Access the Database with a TableAdapter.
Rearrange the columns in the DataGridView by selecting Edit Columns on the DataGridView smart tag.

Share via