Walkthrough: Filtering and Updating in an N-Tier Data Application

• Download starter project - 292.86 KB
• Download finished example - 343.99 KB

Introduction

If you're new to Visual Studio 2008, and also just getting your feet wet with database programming, then undoubtedly you've worked through some of the excellent walkthroughs available in the Visual Studio documentation and on MSDN. One of these tutorials that is great for intermediate beginners is the article Walkthrough: Creating an N-Tier Data Application located on MSDN Library Online.

The Microsoft walkthrough steps you through the process of accessing the Customers and Orders tables in the Northwind database using Visual Studio 2008's powerful new database features. Our article takes what Microsoft has done and extends it to add common database operations to the user interface, such as filtering and updating. This is not a trivial undertaking because we are dealing with changes that, in some instances, must be made across all or most of the various tiers of this N-Tier data application.

The structure of this article is as follows. In the next section, What this Article Shows, we give an overview of the concepts demonstrated in the procedures outlined. The following section, Adding Common Database Features to the NTierWalkthrough UI, presents the main walkthrough that builds on the tutorial presented by Microsoft. Next, we give concluding remarks and review highlights in the Points of Interest section. Finally, a History section keeps a running account of the changes, updates, and improvements to this article.

I wrote this article using Windows Vista Business Service Pack 1, Microsoft .NET Framework 3.5 SP1, Microsoft Visual Studio 2008 Professional Edition SP1, and Microsoft SQL Server 2008 Express Edition with Tools. The SQL Server instance I am using is given the name MSSQLSERVER2008. It may be different in your configuration.

Please feel free to e-mail me with questions to this article. I am also available for database, GUI, and interprocess communications (with WCF) consulting services. I am happy to offer my services either by remote or in person in the local metro area. Please inquire to my email address with questions, comments, or suggestions. If we can do a project together, let me know what you have in mind!

What this Article Shows

Notice the two pictures above. Both pictures illustrate the screen the user sees when the user starts up the presentation layer form built in the tutorial.

The image on the left above shows the form as produced by the Microsoft walkthrough. The image on the right, the 'after' image, shows the form after we have added extensions to allow the user to save changes they make to the tables back to the data source, and shows how to allow the user to filter the Orders table so only the orders placed by a certain customer are shown. Optionally, the user has a choice to show all the records in the Orders table, by clicking the how All button. Futhermore, the user has the option to refresh the display if the user makes a change to a field but wants to fetch data from the data source once again.

Adding Common Database Features to the NTierWalkthrough UI

To begin our extention of the walkthrough, either download the NTierWalkthrough_before project using the link above and follow along, or visit the Walkthrough: Creating an N-Tier Data Application page on MSDN and follow all the steps listed there. Once you have completed all the steps, you are ready to begin following the steps of our extended walkthrough.

To extend the Creating an N-Tier Data Application walkthrough, the following features will be added:

• Save Data and Refresh buttons to the ToolStrip hosted on the form to allow updating the database and refreshing the view;
• A filter so the user can, by selecting a particular CustomerID from the Customers table, filter the Orders table to see all the orders for that customer.
• A Show All button to display all orders from all customers.

In this section, we first do some steps to prepare to begin our Extended Walkthrough. Everything is explained in a step-by-step manner. Next, we add the ability to save data and refresh the view. Next, we break for a short aside while we anchor the sides of controls to the resizable form for ease-of-use. Finally, we dive into the meat of this extended walkthrough, which involves implementing a filter for the data and modifying the user interface of the presentation layer in order to assist the user in accessing the filtered data.

Extended Walkthrough: Preparation

To prepare for the extension of the Walkthrough: Creating an N-Tier Data Application article presented here, we need to rename the project from the walkthrough's version so as to differentiate between versions:

1. Starting with the end result of the article Walkthrough: Creating an N-Tier Data Application, open Visual Studio and open the NTierWalkthrough solution in Visual Studio.
2. Rename the NTierWalkthrough solution to NTierWalkthrough_extended.
   
   
   • In Solution Explorer, right click the text Solution 'NTierWalkthrough' and click Rename in the context menu that appears. Type the new name of the solution, and press the ENTER key on your keyboard.

Adding the Ability to Save Data and Refresh the View

In the PresentationTier project in the walkthrough, the user is able to view the contents of both the Customers and Orders tables in the Northwind database. The user is also able to edit the information in place and even add new records to each table.

The Microsoft walkthrough does not add functionality to save data and changes to the Customers and Orders tab tables back to the data source, and there is also no functionality to refresh the view; i.e., to grab data from the data source to the form without saving. This is useful for the case where you make a change to a table cell, change your mind, and want to cancel your update.

In this section, we present the steps first to save data to the data source from the form. We then implement the Refresh button that loads data from the data source and updates the form.

Saving Data to the Data Source from the Form

As it stands, there is no functionality to update the database with the various changes the user makes. In the default walkthrough project, the toolbar contains a Save Data button. This button is disabled and no functionality is added for it in the walkthrough. It is curious that Microsoft would have left off such a key piece of functionality.

To provide this functionality, three main steps must be followed. First, we must enable the Save Data button on the form's ToolStrip control. Second, we add components to the form that come by way of the reference we have to the DataAccessTier project that drive the saving and management of the data displayed in the form. Lastly, we connect the Save Data button to an implementation event handler and use the DataAccessTier components to do the transfer of data from the form to the data source.

To enable the Save Data but button on the DataGridView toolbar, do the following:

1. In Solution Explorer, navigate to the PresentationTier project.
2. Double-click Form1.cs to open the user interface in the Designer.
3. On the toolbar icon for the Save Data button, right-click the button, and then click Enabled. This is shown in Figure 2.
   
   
   • Any ToolStrip control's buttons can be enabled or disabled in this fashion.

Now that the Save Data button is enabled, we would normally add code to update the database with the user's changes. First, we need to add certain components to the form in the Designer, and then wire those elements to code.

To provide the functionality of the Save Data button, do the following:

1. In Solution Explorer, navigate to the PresentationTier project.
2. Double-click Form1.cs to open the user interface in the Designer.
3. From the Toolbox, open the DataAccessTier Components tab, as shown in Figure 3.

   

   Figure 3. The DataAcessTier Components tab shown opened in the Toolbox, with the controls to add highlighted in a red circle.
4. Drag the CustomersTableAdapter, OrdersTableAdapter, and TableAdapterManager components onto any blank area of the design surface.
   
   
   • Icons for customersTableAdapter1, ordersTableAdapter1 and tableAdapterManager1 objects appear in the Component Tray below the form, as shown below in Figure 4.

   

   Figure 4. Icons for the customersTableAdapter1, ordersTableAdapter1 and tableAdapterManager1 objects, shown in red circles for emphasis, in the Component Tray of the Forms Designer.
5. In the Properties window, rename the (Name) property of the customersTableAdapter1, ordersTableAdapter1 and tableAdapterManager1 objects, removing the numeral '1' from the end of the names so their names are now customersTableAdapter, ordersTableAdapter and tableAdapterManager objects objects. Just a personal niggle on my part.
6. Right-click the tableAdapterManager object in the Component Tray, and then click Properties in the menu that appears. Make sure the CustomersTableAdapter property is set to customersTableAdapter, and make sure the OrdersTableAdapter property is set to ordersTableAdapter.
7. Next, double-click the Save Data button in the ToolStrip at the top of the form.
   
   
   • A skeleton implementation handler is generated and Visual Studio moves you to the location in code to begin filling in the handler's implementation.
8. Add the code shown in Listing 1 to the handler.
   C#

   private void customersBindingNavigatorSaveItem_Click(object sender, EventArgs e)
   {
       try
       {
           this.UseWaitCursor = true;
   
           this.Validate();
           this.customersBindingSource.EndEdit();    // Take the form out of Edit mode
           this.ordersBindingSource.EndEdit();	    // Take the form out of Edit mode
   
           this.tableAdapterManager.UpdateAll(this.northwindDataSet);
   
           this.UseWaitCursor = false;
       }
       catch (Exception ex)
       {
           this.UseWaitCursor = false;
           ShowStopError(ex.Message);
       }
   }

   Listing 1. Code to update changes made on the form to the data source.

   The code in Listing 1 makes use of Structured Exception Handling (SEH). Our most basic way to handle an exception in this instance is to turn off the wait cursor and then show a message box with the exception's error text with a ShowStopError helper function.

9. Add code for the ShowStopError helper function, as shown in Listing 2. This is just a personal preference; I find myself showing a message box in response to my caught exceptions so much, I prefer to set up a helper function to handle the message. Of course, I also prefer to do adequate debugging and testing, so the user never has to deal with caught exceptions; however, one can never anticipate 100% of fail cases. You can feel free to do what you think is best for your implementation.
   C#

   private void ShowStopError(string Message)
   {
       MessageBox.Show(Message, "NTierWalkthrough",
           MessageBoxButtons.OK, MessageBoxIcon.Stop,
           MessageBoxDefaultButton.Button1);
   }

   Listing 2. Code to show a message box in response to a caught exception.

10. Press F5 to build and run the program. Make a change to a cell(s) or add row(s) to either of the tables. Then click the Save Data button. Close the form, and press F5 once again. You will find that the changes you made will still be visible, because the changes have been saved to the data source.
    
    
    • This mode of operation is exactly as how Microsoft Access acts when you click its Save Data button.

We have now seen how to update data on the data source in the N-Tier Data Application walkthrough. Next, we move on to refreshing data from the data source to the view, a feature to be used, say, to revert the form to erase changes made in editing that the user does not wish to commit to the data source.

Refreshing Data From the Data Source

Say I have a company whose database is the Northwind database. Say I am a sales representative, and I make several changes to the Customers and Orders tables. However, the company is migrating to a new database system and the data has already been copied, and a memo was sent to all sales representatives regarding the change and to use the new database and to cease using the Northwind database.

Now that I have just remembered this, I need to revert my changes, otherwise the ongoing migration process may fail due to a compromise of the database's referential integrity. I can do this by a) NOT hitting the Save Data button; and b) fetching the data from the data source anew, as when the form was initially loaded.

To implement refreshing, we will follow two main steps. First, we will add a button to the form's ToolStrip control, and then we will add a handler for this button to the form's code. The handler will bear a striking resemblance to the Form1_Load event handler added in the Microsoft walkthrough. To implement this functionality, you are going to need an image for the toolbar button. I have provided one in Figure 5, which I have taken from a screenshot of another program's user interface. It seemed apt.

Next, do the following:

1. In your browser, right-click the image in Figure 5 and save it to a file on your computer, preferably in the PresentationTier project folder.
2. Open Solution Explorer and double-click the Form1.cs icon to open the form in the Designer.
3. Click the blank space to the right of the Save Data button in the form's ToolStrip control. A new toolstrip button appears.
4. Right-cllick the new button, and then click Set Image. The Select Resource dialog box appears. Click the Import button, browse to where the button image you downloaded is stored on your computer, and then click OK until you are back on the form.
5. Right-click the new button and then click Properties. Set its properties as follows:
   
   
   • Set the (Name) property to refreshButton.
   • Set the Text and ToolTip Text properties to both have the value Refresh.
   • Set the ImageTransparentColor property to White.
6. Double-click the new button to jump to the code editor at the position of the newly-added Click event handler for the button.
7. Add the code shown below in Listing 3 to implement the button.
   C#

   private void refreshButton_Click(object sender, EventArgs e)
   {
       try
       {
           // If the user happens to have a row in Edit mode at the time they click
           // Refresh, end the edit mode so there are no database conflicts.
           this.customersBindingSource.EndEdit();
           this.ordersBindingSource.EndEdit();
   
           // Now we connect to the WCF Service and, through the service
           // access the data source and merge the data from the data source
           // into the DataGridView
           ServiceReference1.Service1Client DataSvc
               = new ServiceReference1.Service1Client();
   
           northwindDataSet.Merge(DataSvc.GetCustomers());
           northwindDataSet.Merge(DataSvc.GetOrders());
   
       }
       catch (Exception ex)
       {
           ShowStopError(ex.Message);
       }
   }

   Listing 3. Code to implement the Refresh button.

8. Press F5 to build and run the program. Make a change to a cell(s) or add row(s) to either of the tables. Then click the Refresh button. Any changes you made will be lost and the tables will display the data exactly as how it looks in the data source itself.

Aside: Resizing the Controls with the Form

You may have noticed, the last two times we have built and run the code by pressing F5, you can resize the form by dragging the border. However, the DataGridView controls do not change size along with the window. It may be desirable to pause for a moment and add this functionality, as both grid controls scroll, and perhaps one might resize the form in order to show more information in the same view.

To resize the grid controls with the form, do the following:

1. In Visual Studio, open Solution Explorer.
2. In Solution Explorer, find and double-click the Form1.cs icon to open the project's form in the Designer.
3. Right-click the DataGridView that displays the data from the Northwind database's Customers table, and then click Properties.
4. Set the Anchor property to Top, Left, Right.
5. Right-click the DataGridView control that displays the data from the Northwind database's Orders table (I put mine on the bottom of the form) and then click Properties.
6. Set the Anchor property to Top, Bottom, Left, Right.
7. Press F5 to build the project, execute the program and display the form.

When testing the project thus far, notice that when you resize the form by dragging its border, the Customers and Orders grid views resize along with it. You can also save changes to the data source by clicking the new Save Data button, and you can refresh data from the data source with the Refresh button. We're almost done. Now we add filtering.

Filtering the Data

Notice the column CompanyName in the Customers table. Each company is a customer of Northwind, and each has placed various orders with Northwind. I would like to select a certain company and call up just those records from the Orders table that contain the orders for just that customer. However, we should also retain the ability to view all the Orders table's records, just in case.

To do this, we will have to move around the different tiers of the application and update the code throughout. First, we update the design of the dataset to add a new query to the OrdersTableAdapter in the DataAccessTier project, and then we add methods to the WCF service to get orders filtered by customer ID, add a method to fetch all the records in the Orders table. Lastly, we update the user interface of the PresentationTier project's form to allow the user to select a customer to filter on, and finally, wire everything together in code.

Let's get to work. Please do the following:

1. Click the Window menu of Visual Studio, and then click Close All Documents.
2. In Solution Explorer, double-click the NorthwindDataSet.xsd file to open the dataset in the Designer. Figure 6 shows the opened data set.

   

   Figure 6. The NorthwindDataSet.xsd file open in the Designer.
3. Right-click the OrdersTableAdapter and then click Add Query.
   
   
   • The TableAdapter Query Configuration Wizard appears.
4. Click Next twice to except the default settings.
5. In the Specify a SQL SELECT Statement page, alter the SQL query shown to look like the one in Listing 4.
   SQL

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

   Listing 4. The SQL query string to select the data for our filter. Notice the @CustomerID parameter in the WHERE clause. In this wizard, you can add parameters to queries just by typing the parameter with an @ sign. This parameter gives us a placeholder for the information to be specified by the user in later steps.

6. Click Next to accept the query string. In the Choose Methods to Generate page, replace the default method names with FillByCustomerID and GetOrdersByCustomerID, respectively.
7. Click Finish to exit the wizard. Click the File menu, and then click Save All.
8. Update the files across the solution by clicking the Build menu, and then click Build Solution.
9. In Solution Explorer, under the DataService project, double-click the IService1.cs and Service1.cs files to open both of them in the code editor.
10. In the IService1.cs file, add the lines of code shown in bold (it should already look like this from when you completed the Microsoft walkthrough project) in Listing 5.
    C#

    namespace DataService
    {
        [ServiceContract]
        public interface IService1
        {
            [OperationContract]
            DataEntityTier.NorthwindDataSet.CustomersDataTable GetCustomers();
    
            [OperationContract]
            DataEntityTier.NorthwindDataSet.OrdersDataTable GetOrders();
    
            [OperationContract]
            DataEntityTier.NorthwindDataSet.OrdersDataTable GetOrdersByCustomerID(
                string CustomerID);
        }
    }

    Listing 5. Code to add to the IService1 data service interface to fetch all orders with a certain CustomerID.

11. Open the Service1.cs file, and add the following code to the end of the class implementation:
    C#

    public DataEntityTier.NorthwindDataSet.OrdersDataTable GetOrdersByCustomerID(
        string CustomerID)
    {
        DataAccessTier.NorthwindDataSetTableAdapters.OrdersTableAdapter
            OrdersTableAdapter1
            = new DataAccessTier.NorthwindDataSetTableAdapters.OrdersTableAdapter();
        return OrdersTableAdapter1.GetOrdersByCustomerID(CustomerID);
    }

    Listing 6. The implementation of the GetOrdersByCustomerID method.

12. Click the Build menu, and then click Build Solution to build the solution and update the various references between the different layers of the solution. Now, we are ready to implement the user interface.
13. In Solution Explorer, navigate to the PresentationTier project and double-click the file Form1.cs to open the project's user interface in the Designer.
14. Resize the form to allow for more space between the two DataGridView controls on the form.
15. Open the Toolbox and drag a ComboBox control onto the blank area between the DataGridViews.
16. In the Properties window, give the ComboBox the (Name) property filterBox.
17. Set the ComboBox control's DropDownWidth property to the value 225. We will be displaying items in the control that require the extra width.
18. Click the little right-arrow on the upper-right corner of the ComboBox (as shown below) to reveal the ComboBox Tasks pane.

    

    Figure 7. Opening the ComboBox Tasks pane.
19. As shown above, click the Use data bound items check box in the ComboBox Tasks pane.
20. Set the Data Source property to customersBindingSource.
    
    
    • We want the combo box to be user-friendly and display the company name for each customer, but each selection should be mapped to the corresponding CustomerID value for each company name, since that is what we filter on.
21. Set the Display Member property to CompanyName and the Value Member property to CustomerID.
    
    
    • The ComboBox lets the user edit the item that is currently selected. Such behavior is not desired, since it may upset the referential integrity of the underlying data source if the user accidentally modifies items displayed in the box. Therefore, we alter the ComboBox's properties so as to prevent the user from editing the text of the currently-selected item.
22. Alter the ComboBox's DropDownStyle property to have the value DropDownList.

Next, we make the filter work by adding two buttons: a button saying Go (it seemed apt at the time), and another button called Show All Orders. The Go button is to be clicked by the user after the user selects the customer upon which the user wants to filter orders. The Show All Orders button resets the display to show the entire contents of the Orders table with no filter.

1. From the Toolbox, drag two button controls to the form, to land to the right of the ComboBox. Give them whatever (Name) property you think may be apt, however title the two buttons (by setting each's Text property) Go and Show All Orders, respectively, so they appear as in the figure below:

   

   Figure 8. Controls for filtering.
2. Make sure the controls pictured above lie in between the two DataGridView controls already on the form.
3. Double-click the Go button and add the folllowing code:
   C#

   private void btnGo_Click(object sender, EventArgs e)
   {
       try
       {
           this.UseWaitCursor = true;
           northwindDataSet.Orders.Clear();
   
           ServiceReference1.Service1Client DataSvc
               = new ServiceReference1.Service1Client();
           northwindDataSet.Merge(DataSvc.GetCustomers());
           northwindDataSet.Orders.Merge(DataSvc.GetOrdersByCustomerID(
               filterBox.SelectedValue.ToString()));
           this.UseWaitCursor = false;
       }
       catch (Exception ex)
       {
           ShowStopError(ex.Message);
       }
   }

   Listing 7. Code to implement the Go button to carry out the database filtering.

4. In Soluton Explorer, open the PresentationTier project and right-click the Service References folder.
5. Right-click the ServiceReference1 icon, and then click Update Service Reference.
   
   
   • This is needed since we changed the code in the WCF service providing access to the data source, so the information and client-side code enabling us to call the service's methods must be refreshed.
6. Finally, we come to the last steps of our walkthrough. Right-click anywhere in the Code Editor and click View Designer to open the form in Design mode.
7. Double-click the Show All Orders button to add a skeleton event handler in the form's code.
   
   
   • Visual Studio opens the form's code in the code editor, and places the cursor inside the body of the newly-created event handler.
8. In the newly-created event handler, add the code in Listing 9, shown below:
   C#

   private void btnShowAll_Click(object sender, EventArgs e)
   {
       try
       {
           this.UseWaitCursor = true;
   
           northwindDataSet.Orders.Clear();
   
           ServiceReference1.Service1Client DataSvc
               = new ServiceReference1.Service1Client();
           northwindDataSet.Merge(DataSvc.GetCustomers());
           northwindDataSet.Orders.Merge(DataSvc.GetOrders());
   
           this.UseWaitCursor = false;
       }
       catch (Exception ex)
       {
           ShowStopError(ex.Message);
       }
   
   }

   Listing 9. Code to implement the Show All Orders button.

9. In Solution Explorer, and underneath the PresentationTier project, double-click the app.config file.
10. Find the text maxReceivedMessageSize and make sure its value is set to 6553600.
11. That's it! Press F5 to test the solution. Select a company name in the list, click Go to see that company's orders in the lower DataGridView, and click Show All Orders to display the entire contents of the Orders table in the lower DataGridView.

Congratulations. You've reached the end of this extension of the Microsoft walkthrough and now have a fully-functional, mini database application that has an N-Tier architecture. Use this sample as a template for your future database development!

Points of Interest

Some points of interest before we leave. Implementing saving of data is relatively straightforward. Notice how we did not have to modify the underlying methods exposed by the WCF service to facilitate the implementation; all that was really necessary was to add some more components to the PresentationTier form. Implementing the refreshment of data via the Refresh toolbar button involved mainly copying some of the code use to implement the Form1_Load event handler.

The main thrust of this article involved implementing filtering. Notice how adding a filter involved making changes to all the various tiers of this application. First, the underlying TableAdapter objects in the DataAccessTier project were altered. Next, we updated the interfaces exposed by the WCF service to add methods that call on the queries in the OrdersTableAdapter object. Finally, we added controls and code to the PresentationTier project's form in order to provide the user with a means to apply the filter.

As always, a programmer's work is never done. There are yet more tasks and extentions one could make to this project. Among them are:

• Add validation. A walkthrough that demonstrates this is the Walkthrough: Adding Validation to an N-Tier Data Application article, which can be found on MSDN.
• Figure out how various deployment and scaling scenarios may work so the various tiers of this application can be placed on separate computers and servers, all linked via means of a network.
• ...And I am sure there are several other possible extensions that can be done as well. Think up some and let us know!

I hope this article was helpful. I do provide consulting services; if you are looking for an expert on database programming and user interfaces for your shop, or a technical documentation SME, please email me and let's work on a project together.

History

11-Jan 2009: Article completed.