Binding DB2 Data to Visual Studio 2005 Windows Applications

Wednesday Jul 26th 2006 by Paul Zikopoulos
Share:

Paul Zikopoulos shows how easy it is to be a .NET developer and work with DB2 UDB V8 or DB2 9 databases. Specifically, I will show you how to quickly create a Windows application and bind DB2 UDB V8 data to various controls on the Windows form.

In a previous set of articles, I showed you all the great integration features between the IBM DB2 Universal Database for Linux, UNIX, and Windows Version 8.x (DB2 UDB) product and Microsoft Visual Studio.NET 2003 integrated development environment (IDE).

In early June 2006, IBM announced the next release of the DB2 UDB product, DB2 9 (formerly known as DB2 Viper). Part of this announcement includes the support for Microsoft Visual Studio 2005 and its accompanying ADO.NET 2.0 driver.

As you may recall, Microsoft announced the Visual Studio 2005 product late in 2005, along with SQL Server 2005. Around the same time, the DB2 team delivered a beta of the now officially announced integration into Microsoft Visual Studio 2005 and ADO.NET 2.0. You can download this beta (not needed after DB2 9 becomes generally available) at: http://www-306.ibm.com/software/data/db2/windows/dotnet.html.

In this article, I want to show you how easy it is to be a .NET developer and work with DB2 UDB V8 or DB2 9 databases. Specifically, I will show you how to quickly create a Windows application and bind DB2 UDB V8 data to various controls on the Windows form. Of course, I could have easily bound data from a DB2 9 database (or a combination of the two versions of the product) but I wanted to illustrate the flexibility of the integration that DB2 offers into the V8 product set.

Adding a DB2 Data Source

Before you can create an application to which you will bind your DB2 data, you need to have a connection to that database in the Server Explorer. If you are familiar with the Visual Studio.NET 2003 support that DB2 UDB provides, you’ll recall that .NET developers writing applications on the DB2 platform were required to work within the IBM Explorer. The IBM Explorer was functionally equivalent to the Server Explorer; however, there were no open interfaces into the Server Explorer in Visual Studio.NET 2003 that DB2 UDB could leverage to provide some of the features unique to the DB2 UDB plug-in.

The architecture of Visual Studio 2005 changed such that there are now interfaces that let you develop applications that connect to DB2 UDB V8 and DB2 9 databases using the Server Explorer. This provides a more native experience for .NET application developers used to developing applications on SQL Server databases.

To add a DB2 database connection to your Server Explorer, perform the following steps:

1.      Right-click the Database Connections folder in the Server Explorer and select the Add Connection option. The Add Connection dialog box opens:

2.      Ensure that the Data source field points to the .Net Framework Data Provider for IBM DB2 data provider so that the Server Explorer will use the ADO.NET data provider written by IBM specifically for DB2 UDB 8 and DB2 9 databases.

The DB2 ADO.NET provider is not the default provider shown in this field. To change the database provider to use the one for DB2, click Change and select the IBM DB2 option from the Data source box, as shown below. You should also ensure that the IBM DB2 Data Provider for .NET Framework is selected in the Data provider field, but this should be the default:

Note: If you plan to work frequently with DB2 database connections, select the Always use this selection check box so that, when you add another database connection, Visual Studio 2005 will automatically select the DB2 data provider.

3.      Enter the server name and port number (separated by a colon) in the Enter server name field. If you are connecting to a local database, you can use the localhost alias for your workstation.

Depending on the version of DB2 that you are running your beta on, you can optionally click Refresh to automatically list all the databases configured to respond to DB2 network database identification requests and automate the process of entering the required information.

4.      Enter your user account credentials in the User ID and Password fields. I recommend that you save these credentials in the connection string (they are encrypted) by selecting Save my password. Selecting this option makes application development more streamlined because you are not challenged to provide authentication details during subsequent access requests to the DB2 database.

5.      Select the database name from the Select or enter a database name drop-down box, or enter the name manually.

Note: In this article, I chose to connect to the SAMPLE database that is shipped with DB2 UDB V8. If you don’t have the SAMPLE database created on your workstation, you can create it now by entering the db2sampl command from a Windows-based command prompt.

Optional:

You can use the Specify Connection Options and Specify Filtering Options sections to further customize your database connection. The options associated with these toggles are shown below:

The DB2 support for Visual Studio 2005 comes with a rich set of connection time and filtering options. For the purposes of this article, you can just select the defaults.

6.      Test the connection using the Test Connection button.

7.      Click OK.

After adding your database connection, the Visual Studio 2005 Server Explorer should look similar to this:

In the previous figure, you can see that I have expanded the SAMPLE database connection object; below it is a connection object to a SQL Server 2005 database. Notice the beside this database connection object: all databases appear this way until you click them to make the database connection.

Binding DB2 Data to a Windows Application in a Wink of an Eye

Now that you have a valid DB2 database connection, you can build your first ADO.NET 2.0 application that accesses your DB2 database.

In this section, I’ll outline how to build an application that quickly binds all of the rows in the STAFF table (which resides in the SAMPLE database) to a data grid, and also provide two separate controls that will allow you to quickly jump to any row in this table based on an employee’s last name or their department number.

To build this application, perform the following steps:

1.      Create a new C# application, called QuickDB2DataBind, by selecting File-->New Project-->Windows Application, and clicking OK, as shown below:

2.      Press Ctrl+Alt+X to switch to the Toolbox View. The IDE should now look like this:

3.      Drag a Label object from the Common Controls section of the Toolbox and drop it inside the designer palette (by default, called Form1); drag a second Label object and drop it below the first, like this:


4.      Right-click the first Label object on the designer palette, select Properties, and change the Text field to Last Name; change the second to Department. When you’re finished, the designer palette should look like this:

5.      Drag a ComboBox control from the Common Controls section of the Toolbox and drop it to the right of Last Name. Drag a second one for Department. When you are finished, your designer palette should look similar to this:

Note: The terms ComboBox and drop-down box are used interchangeably throughout this article.


6.      From the Data section of the Toolbox, drag a DataGridView object and size it such that it takes up the whole bottom of the Windows application you are creating, as shown below. Dismiss the auto pop-up dialog box by clicking the icon if it appears when you drag this object to the designer palette.


7.      Right-click the drop-down box beside the Last Name label, select Properties, click the drop-down box beside the DataSource property, and select Add Project Data Source, as shown below:

8.      The Data Source Configuration wizard opens. Select Database as the source for your application’s data, and click Next.


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

Note: If the database that you want to connect to doesn’t appear in this list, you can add a new database connection by clicking New Connection. This will open a dialog box that is similar to the one detailed in the “Adding a DB2 Data Source” section.

10.  Select the Yes, include sensitive data in the connection string radio button, and click Next.

This option has the effect of including your user ID and password in clear text within the application. You can see in the previous figure that the Connection string toggle is collapsed. If you were to expand this toggle, you would see your user account credentials in unencrypted text. This is fine for the purposes of this article but depending on your environment, may or may not be an acceptable option.

11.  The defaults on the next pane of this wizard are fine for this article; click Next to accept them.

12.  Expand the Tables folder and select the STAFF table where the data that you want to bind to your application resides and click Finish, as shown below:

You can see that data for your applications doesn’t have to come just from DB2 tables. It can come from stored procedures, views, functions, and more.

After you click Finish, Visual Studio 2005 will automatically create a number for ADO.NET 2.0 data access objects for your DB2 database, as shown below:


13.  Right-click the drop-down box beside the Last Name label, select Properties, click the DisplayMember drop-down box, and select the Name column, as shown below:

This step essentially binds the NAME column in the SAMPLE database’s STAFF table to this drop-down box. When you run your application and click the Last Name drop-down box, it will show all of the values in this column.

14.  Right-click the drop-down box to the right of the Department label, select Properties, and click the drop-down box beside the DataSource property; this time, select the sTAFFBindingSource object, as shown below:

Since both of the ComboBoxes for your application will retrieve data from the same table, you can simply reuse the bindings that you generated for the Last Name ComboBox.

15.  Perform the same steps to bind the DEPT column to the Department ComboBox as you did for the NAME column to the Last Name ComboBox in Step 13.

16.  Right-click the DataGridView on the designer palette, select Properties, and click the drop-down box beside the DataSource property; select the sTAFFBindingSource object as you did for the Department ComboBox in Step 14.

This step binds all of the data in the table to the data grid, not just to a column of data as in the case of the ComboBoxes. Note that when you bind the STAFF table to the data grid, all of the columns in the table are automatically listed.

17.  Resize your application form such that you can see the entire data grid you added in the previous step, as shown below:

18.  Press F5 to build your application.


When the application starts, it should look similar to this:

You can see that all of the data in the STAFF table is displayed in the bottom data grid.

Note: The data that resides in the STAFF table used for this article may not exactly match the default data in the SAMPLE database since I’ve added data to this table over time.

If you click either of the ComboBoxes that we created, you can see the DB2 data bound to each of these controls:

You can use these controls to navigate the data grid at the bottom of your application. For example, if you selected Rothman from the Last Name ComboBox, the cursor in the data grid would move to that row, as shown below:

That Was Fast...

As you can see, creating this simple application was very quick. (The length of this article can be accounted for by the step-by-step documentation, which should allow even non-developers to successfully build the application outlined in this article.) Truly, this rapid application development experience is a result of the synergy between the Visual Studio 2005 IDE and the integration work that has gone into this environment for DB2 databases.

There’s so much more for .NET developers who write applications for the DB2 platform to take advantage of. Stay tuned for my next article on this topic!
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 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 and DB2 Universal Database are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Windows is a trademark 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, 2006. 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