Lesson 2. Add a synchronizing data control

This tutorial is a continuation of the tutorial described in Lesson 1. Create a table viewer. The complete application can be found in the ADO.NET project samples-dir\SQLAnywhere\ADO.NET\SimpleViewer\SimpleViewer.sln.

In this tutorial, you add a datagrid control to the form developed in the previous tutorial. This control updates automatically as you navigate through the result set.

To add a datagrid control

  1. Start Visual Studio and load your MySimpleViewer project that you saved in the previous tutorial.

  2. Right-click DataSet1 in the Data Sources window and choose Edit DataSet With Designer.

    The DataSet Designer window opens.

  3. Right-click an empty area in the designer window and choose Add » TableAdapter.

    The TableAdapter Configuration Wizard appears.

    1. On the Choose Your Data Connection page, click Next.
    2. On the Choose A Command Type page, make sure Use SQL Statements is selected and then click Next.
    3. On the Enter A SQL Statement page, click Query Builder. The Query Builder and the Add Table windows appear.
    4. On the Add Table window, click the Views tab, select the ViewSalesOrders view, and click Add.
    5. Click Close to close the Add Table window.

  4. Stretch the Query Builder window so that all sections of the window are clearly visible.

    1. Stretch the ViewSalesOrders window so that all of the checkboxes are visible.
    2. Select Region.
    3. Select Quantity.
    4. Select ProductID.
    5. In the grid below the ViewSalesOrders window, clear the checkbox under Output for the ProductID column. You want to use this column in your query, but you do not want to display it.
    6. For the ProductID column, type a question mark (?) in the Filter cell. This generates a WHERE clause for ProductID.

    A SQL query has been built that looks like the following:

    SELECT   Region, Quantity
FROM     GROUPO.ViewSalesOrders
WHERE    (ProductID = :Param1)

  5. Modify the SQL query as follows:

    1. Change Quantity to SUM(Quantity) AS TotalSales.
    2. Add GROUP BY Region to the end of the query following the WHERE clause.

    The modified SQL query now looks like this:

    SELECT   Region, SUM(Quantity) as TotalSales
FROM     GROUPO.ViewSalesOrders
WHERE    (ProductID = :Param1)
GROUP BY Region

  6. Click OK to close the Query Builder window.

  7. Click Finish to close the TableAdapter Configuration Wizard.

    A new TableAdapter called ViewSalesOrders has been added to the DataSet Designer window.

  8. Click the form design tab (Form1).

    • Stretch the form to the right to make room for a new control.

  9. Expand ViewSalesOrders in the Data Sources window.

    1. Click ViewSalesOrders and choose DataGridView from the dropdown list.
    2. Click ViewSalesOrders and drag it to your form (Form1).
    Form1 incorporates a datagrid view control.

    A datagrid view control appears on the form.

  10. Build and run the project.

    • From the Visual Studio Build menu, choose Build Solution.
    • From the Visual Studio Debug menu, choose Start Debugging.
    • In the Param1 text box, enter a product ID number such as 300 and click Fill.

      The datagrid view displays a summary of sales by region for the product ID entered.

      The application, with a row in the Products table displayed and a sales summary for a given product.

    You can also use the other control on the form to move through the rows of the result set.

    It would be ideal, however, if both controls could stay synchronized with each other. The next few steps show how to accomplish this.

  11. Shut down the application and then save your project.

  12. Delete the Fill strip on the form since you do not need it.

    • On the design form (Form1), right-click the Fill strip to the right of the word Fill and choose Delete.

      The Fill strip is removed from the form.

  13. Synchronize the two controls as follows.

    1. On the design form (Form1), right-click the ID text box and choose Properties.
    2. Click the Events icon (it appears as a lightning bolt).
    3. Scroll down until you find the TextChanged event.
    4. Click TextChanged and choose FillToolStripButton_Click from the dropdown list. If you are using Visual Basic, the event is called FillToolStripButton_Click.
    5. Double-click FillToolStripButton_Click and the form's code window opens on the fillToolStripButton_Click event handler.
    6. Find the reference to param1ToolStripTextBox and change this to iDTextBox. If you are using Visual Basic, the text box is called IDTextBox.
    7. Rebuild and run the project.

  14. The application form now appears with a single navigation control.

    • The datagrid view displays an updated summary of sales by region corresponding to the current product as you move through the result set.
      The application, with a row in the Products table and a sales summary by region displayed.

    You have now added a control that updates automatically as you navigate through the result set.

  15. Shut down the application and then save your project.

In these tutorials, you saw how the powerful combination of Microsoft Visual Studio, the Server Explorer, and the SQL Anywhere .NET Data Provider can be used to create database applications.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0