Using the FILLBY Method to Filter DB2 9 Data Sets...with a Twist

Wednesday Feb 21st 2007 by Paul Zikopoulos
Share:

This article discusses how to use a FILLBY method to pass dynamic parameters to the application.

Since the middle of 2006, I’ve been writing about all the great integration features in IBM DB2 9 for the Microsoft Visual Studio 2005 integrated development environment (IDE) and the accompanying ADO.NET 2.0 API.

In an article called Binding DB2 Data to Visual Studio 2005 Windows Applications, I showed you how to quickly build a Windows application that binds various controls (ComboBoxes, DataGrids). In Build a DB2 Bound Data Application using Visual Studio 2005, I showed you an alternative way to even more quickly build a WinForm Windows application using a data grid that binds to your DB2 data server. You don’t specifically bind controls to an application: it’s even easier than that.

In this article, you can use either of those methods to build a new application, but I want to show you how to use a FILLBY method to pass dynamic parameters to the application, and then surprise you with a twist at the end.

The move from DataAdapters to TableAdapters

Although I didn’t spell it out in my previous DB2 9 and Visual Studio 2005 articles, the Visual Studio 2005 IDE adds a new helper class called a TableAdapter, which improves drag-and-drop application development. A TableAdapter is created automatically when you drag a data source from the Data Sources view onto a designer palette. The TableAdapter in Visual Studio 2005 replaces the DataAdapter that was part of Microsoft Visual Studio.NET 2003, which Microsoft has now deprecated in Visual Studio 2005.

One unique characteristic about TableAdapters is that they are not found in the .NET 2.0 Framework. Unlike typed data sets, which inherit from the System.Data.DataSet class, TableAdapters are entirely generated by Visual Studio 2005 using the data model you create with the Data Source Configuration Wizard, or in the Data Set Designer.

TableAdapters abstract the database type away from application code, which lends itself to more flexibility for application deployment. For example, let's say you have developed your application using SQL Server 2005. Later you decide to port that database to DB2 running on Linux, UNIX, or Windows. In the .NET 1.0 and .NET 1.1 Frameworks, which were integrated into Visual Studio.NET 2002 and Visual Studio.NET 2003, you would have had to change the object types you were using to access the database; in Visual Studio 2005, it’s a completely different (and more streamlined) story.

Building an application using the FILLBY method

To build an application using the FILLBY method in your application’s tool strip, perform the following steps:

1.  Create a new C# application, called MyFILLBYProject, by selecting File>New Project>Windows Application, selecting Visual Studio C# (which may be under Other Languages depending on how you set up your IDE), entering the name in the Name field, and clicking OK.

2.  Open the Server Explorer (by pressing Ctrl+Alt+S or by selecting View>Server Explorer from the menu bar), and select a table. (In my example, I selected the XTREME table, which I created in the SAMPLE database, but you can use any table you want.) View the properties of your table’s columns by selecting Properties from the right-click context menu and see if your table has a primary key.

A column that is defined as a table’s primary key has a 1 in the Primary Key property field, while a column that is not a primary key has a 0 (or may be blank).

 

For this article, the table you select doesn’t need a primary key because I’ll show you how to use the tool strip to filter data with a FILLBY method. However, if the table you select doesn’t have a primary key, you won’t be able to perform INSERT, UPDATE, and DELETE operations on this data. The DB2CommandBuilder uses a primary key for these types of data operations in order to build the corresponding statements that it uses to change the underlying table’s row.

It’s easy to understand this requirement when you think about it. A data set is a disconnected data persistence container. When the DataAdapter reconnects to the actual data server to process any data change requests, how can it be sure it will change the data for the row that was fetched? Using a primary key, of course! With a primary key, the DB2CommandBuilder can build a WHERE clause for the dirty row to ensure that it is positioned on the same row in the database as the data set. If there is no primary key, then the DB2CommandBuilder cannot guarantee it is positioned on the same row, and will not build statements to change data. You can, of course, work around this method if you know the metadata of the table and hand-generate these data manipulation statements (DML) yourself. However, we want a slick and easy solution, so if you plan to code any INSERT, UPDATE, and DELETE operations into your application, ensure that you have defined a primary key on the table where the data resides.

3.  Ensure that the Add Data Source view is displayed in Visual Studio 2005 by pressing Shift+Alt+Delete or selecting Data>Show Data Sources from the menu bar.

4.  Click Add New Data Source in the Data Sources view, select Database, and click Next:

5.  Select the SAMPLE database from the Which data connection should your application use to connect to the database? drop-down list.

6.  Select the Yes, include sensitive data in the connection string radio button, and click Next. (I’m assuming that you’re just working on your own test machine here and notice that Visual Studio 2005 collapses the Connection string box to hide these details from prying eyes.)

7.  Accept the defaults on the next pane of this wizard. Click Next.

8.  Expand the Tables toggle, select the table that you want to include in your application, and click Finish:

You can select multiple sources from your underlying data server to populate the disconnected data set. In the previous figure, I’ve added a single table called XTREME (and all of its columns). You could have multiple tables, or a combination of tables and views. In addition, you could even use a function or stored procedure to populate the data set. (I will show you this method in a future article.)

After you’ve added the data set, the Visual Studio 2005 IDE should look like this:

Note that your table has populated a data set in the Data Sources view. Expand it to see all of its contents if it isn’t automatically expanded for you.

9.  Use the left mouse button to select and drag two columns from your data set to your Windows Application form, and then resize the data grid so it looks like this:

10.  Now add a DataGridView to list all the data in the data set from Toolbox and bind it to the <your_table_name>BindingSource object such that the designer palette looks like this:

11.  Press F5 to build your application. It will look similar to this:

The default Fill method called by Visual Studio 2005 doesn’t have any capabilities to filter rows; rather, it just does a dump of all the data that’s in a table. This may not be optimal if you’re prototyping an application that goes against a table with a large number of rows.

You should be somewhat careful when building an application like this with large tables, or you could grind your system such that you’ll have an unscheduled coffee break. The FILLBY method provides an opportunity to filter the result set and subsequently the amount of data being retrieved from the data server.

12.  Add a query for this data set by right-clicking the <your_table_name>TableAdapter and selecting Add Query. (Since the table I built a data set for is called XTREME, my table adapter component tray shows it as xTREMETableAdapter.)

13.  The Search Criteria Builder window opens. Click Query Builder.

14.  Use the Microsoft Query Builder to build a query such that you set one of the columns that the data set retrieves as a parameter that will filter the data. For example, in the previous figure, I could select to filter this data set based on the COUNTRY column by changing the Filter field to =? (as shown in the example below).

You can test the data that your query will retrieve by clicking Execute Query. Simply fill in a data value for the defined parameter and click OK:

15.  Click OK to close the Query Builder and then OK again to close the Search Criteria Builder window. Your WinForm should look similar to this:

In the previous figure, I’ve highlighted the new tool strip that was automatically created for you when you created the new query statement. (You may need to resize your WinForm in order to see it.)

You should also see a new component for this query in the component tray:

16.  Press F5 to build your application again. It should look like this:

17.  Enter a column value that corresponds to the column you selected to filter the table by when you built the qualifying query and click FillBy1. (In my example, I use the COUNTRY column, whose values include Canada, USA, Australia, and so on.) Your application should filter the results and look like this:

You can see that the data grid was filtered to include only the parameter entered in the FillBy1 tool strip. This parameter is dynamic; you can change it whenever you want. Just click FillBy1 each time to refresh the data.

As you navigate your data set, you can see that the COUNTRY and CITY text boxes are automatically updated to correspond to the data grid pointer ():

 

Wrapping it up...

In this article, I showed you how you can easily add controls to manage large data sets that you may be retrieving from your DB2 9 data server without writing a single line of code! With a few added clicks, you created an application that could be well suited for quick prototyping and data discovery.

What was the twist?

The DB2 9 table XTREME isn’t really a DB2 table. It’s actually a Microsoft Excel 2007 spreadsheet with a nickname created over it so that it is exposed to developers as a DB2 table. This technology is referred to as federation:

 

You can actually use products such as DB2 Connect (for DB2 for i5/OS and DB2 for z/OS integration), WebSphere Federation Server, WebSphere Information Server, or the Homogeneous Federation add-on feature packs available with all DB2 9 editions to add capability like this to your data environment.

What’s the benefit? All of your data artifacts can be abstracted so that developers just go after data. They don’t need to concern themselves with the fact that SQL Server uses a MONEY data type for currency, while Oracle uses a NUMERIC, and DB2 a DECIMAL. If Excel doesn’t have a specific function, the DB2 federation software will compensate for it. Imagine how simple it becomes to build applications using these concepts. You can expose WebSphere MQSeries message queues as tables. Just insert and delete from the table to write and destructively read from a message queue. When was the last time you met a VB.NET developer who could do that? With this type of technology, they can in an instant. How cool is that?

Now that you’re building applications using data artifacts, you can easily join disparate data sources without replication, APIs, workarounds, manual conversion, and the like. For example, let’s say you wanted to join your Excel table data with a table in DB2 9:

 

In the previous example, the BUYERS table resides in DB2 9 and it’s joined with the Excel spreadsheet used in this article’s example.

This is perhaps the biggest differentiator when it comes to DB2 programmability and productivity aside from the tight integration into the rapid application development tools found in today’s most popular IDEs. It’s important to note that in many cases, connections to remote data sources aren’t via the least common denominator (like OLE DB, for example). If you were joining an Oracle table, an Ora 8 connection library would be loaded. Federation, as it’s implemented in a DB2 environment, is perhaps the first real loosely coupled architecture before the service-oriented architecture framework made this catch word so popular.

» See All Articles by Columnist Paul C. Zikopoulos

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 UDB and has written over one hundred magazine articles and several books about it. Paul has co-authored the books: DB2 9 New Features (available soon), DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.

Trademarks

IBM, DB2, DB2 Connect, DB2 Universal Database, i5/OS, MQSeries, WebSphere, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2007. All rights reserved.

Disclaimer

The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved