When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Teach Yourself ADO.NET in 24 Hours
Hour 9: Binding Data to List Controls

Complex Data Binding in Windows Forms

In the next example, you will build a quick-and-dirty Windows Form that will display all customers in a boundListBox and the customer's associated Orders in a boundDataGrid. A more complexDataSet will be needed that provides the relationship path to traverse from customers to orders using the primary key of Customers (CustomerID) to foreign key in Orders (CustomerID). You will have to do data bindings for both theListBox control and theDataGrid control. This example is one of the classic data binding scenarios described earlier.

Create a New Project in VS .NET

  1. Create a new project in VS .NET by choosing File, New, and then choosing the Project option. Or, you can just modify the "ADO.NET24hoursDB" that we just created for simple binding. If you choose to modify this one, delete all of the Forms objects, methods, and so on that we added so that you start from a clean slate. This will include deleting theDataSet schema that is shown in the Solution Explorer.

  2. When the New Project dialog box appears, choose Visual Basic Projects (or Visual C# Projects) and Windows Applications. Name this project "ADO.NET24hoursE". This creates a default form for you to start from.

Add the Data Connection and Two Data Adapters

You will need to access both the Customers table and the Orders table, so two data adapters will be created, each populating different controls but using one database connection.

  1. From the Data tab of the Toolbox, drag aSQLDataAdapter object into your form. This will automatically invoke the Data Adapter Configuration Wizard. Both the data connection and the data adapter can be fully configured here.

    1. The wizard starts with the Choose Your Data Connection dialog box. If you already have a connection defined in your project, it will be placed in the dialog box; otherwise, choose to create a new connection and specify the appropriate connection information (test the connection as well).

    2. Choose the Use SQL Statementsoption.

    3. You will be presented with a Generate the SQL Statements dialog box where you will simply type in a valid SQL statement, or you can use the Query Builder option to formulate the SQL query. For our example, just type in the following query:

      SELECT CustomerID, CompanyName FROM Customers  
    4. Finally, the wizard will show you the tasks that it has done and indicate whether the SqlDataAdapter has been configured successfully (it should be named SqlDataAdapter1 along with a SqlConnection name SqlConnection1.

  2. Okay, we need one more data adapter for access to the Orders table. Drag anotherSqlDataAdapter object onto the form.

    1. Again, the wizard starts with the Choose Your Data Connection dialog box.

    2. Choose the Use SQL Statementsoption.

    3. You will be presented with a Generate the SQL Statements dialog box where you will type the following query:

        SELECT OrderID, CustomerID, OrderDate, ShipVia, Freight, ShipName   FROM Orders
  3. And lastly, the wizard will show you the tasks that it has done and indicate whether the SqlDataAdapter has been configured successfully (it should be named SqlDataAdapter2).

Generate aDataSet

Now that theSqlDataAdapter andDataConnection objects have been configured and added to the form, you must generate aDataSet and then add an instance of thisDataSet to the form.

  1. From the Data menu in Visual Studio, simply choose the Generate Dataset option. The resulting dialog box can be seen inFigure 9.6.

Figure 9.6
Generating a new dataset for the form.

  1. Now, just choose to create a new dataset using the name "CustOrdDS" (as also seen inFigure 9.6) that it provides (DataSet1). Make sure you have checked the Customers table, Orders table, and checked the Add Dataset to Designer box. Click OK.

  2. When the process finishes, aDataSet instance namedCustOrdDS1 will be on the form and a dataset schema will be in the Solutions Explorer (named CustOrdDS.xsd).

  3. You're not quite done yet. There must also be a way for the schema to know that there is a parent/child relationship between Customers and Orders. You do this by adding aRelation object onto the Orders table in the schema. First, double-click on the CustOrdDS.xsd schema file in the Solutions Explorer. This takes you immediately into the XML Schema editor.Figure 9.7 shows the two tables that are part of the CustOrdDS.xsd schema.

  4. Next, drag aRelation object onto the Orders table (this is the child side of the parent/child relationship). You are immediately put into the Edit Relation dialog box.

  5. Verify that the name of theRelation defaults to CustomersOrders, the Parent element is Customers, the Child element is Orders, and the Key Fields and Foreign Key Fields both are CustomerID. Nothing else should be checked at this point.Figure 9.8 shows the values in the Edit Relation dialog box. Click OK when you are satisfied that all is correct.

Figure 9.7
XML Schema editor—CustOrdDS.xsd schema file.

Figure 9.8
The Edit Relation dialog box for the CustomersOrders relation.

  1. The XML Schema editor should now show a one-to-many relationship line between the Customers and Orders table as seen inFigure 9.9. The following code is the content of the CustOrdDS.xsd XML Schema file that now reflects the new relationship:

    <?xml version="1.0" standalone="yes" ?>
    <xs:schema id="CustOrdDS" targetNamespace=
    xmlns:mstns="http://www.tempuri.org/CustOrdDS.xsd" xmlns=
    xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata=
    attributeFormDefault="qualified" elementFormDefault="qualified">
     <xs:element name="CustOrdDS" msdata:IsDataSet="true">
      <xs:choice maxOccurs="unbounded">
      <xs:element name="Customers">
        <xs:element name="CustomerID" type="xs:string" />
        <xs:element name="CompanyName" type="xs:string" />
     <xs:element name="Orders">
       <xs:element name="OrderID" msdata:ReadOnly="true" 
    type="xs:int" />
      <xs:element name="CustomerID" type="xs:string" minOccurs="0" />
      <xs:element name="OrderDate" type="xs:dateTime" minOccurs="0" />
      <xs:element name="ShipVia" type="xs:int" minOccurs="0" />
      <xs:element name="Freight" type="xs:decimal" minOccurs="0" />
      <xs:element name="ShipName" type="xs:string" minOccurs="0" />
    <xs:unique name="Constraint1" msdata:PrimaryKey="true">
     <xs:selector xpath=".//mstns:Customers" />
     <xs:field xpath="mstns:CustomerID" />
    <xs:unique name="Orders_Constraint1" msdata:ConstraintName="Constraint1" 
     <xs:selector xpath=".//mstns:Orders" />
     <xs:field xpath="mstns:OrderID" />
    <xs:keyref name="CustomersOrders" refer="mstns:Constraint1">
     <xs:selector xpath=".//mstns:Orders" />
     <xs:field xpath="mstns:CustomerID" />
    </xs :schema>

Figure 9.9
The XML Schema editor, showing the new relationship between Customers and Orders.

  • Read Part 4

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article