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.
» 
