In a previous series 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 the 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 I have been writing about the DB2 integration into Visual Studio 2005, I have discussed in other articles:
In this article, I want to show you an alternative method whereby you can even more quickly build a data grid and bind it to your DB2 data source. This approach is a little different from the one I wrote about in the first article; here were not specifically binding controls to an application: its even easier than that.
I find this method of building a .NET application excellent for prototyping an application or showing a quick proof of concept.
Note: Before you can create an ASP.NET-based Web site to which you will bind your DB2 data, a connection to that database must be defined in the Server Explorer. If you already have a database connection, continue with the steps outlined in this article. If you do not have a database connection, refer to the Addendum: Adding a DB2 Data Source section at the end of this article.
The Most Advanced Application You Ever Built in No Time...
If you have a valid DB2 database connection, you can quickly build an application that contains a data grid that will bind INSERT, UPDATE, DELETE, and SELECT operations to a native .NET data grid.
To build this application, perform the following steps:
1. Create a new C# application, called QuickDB2DataGrid, by selecting File->New Project->Windows Application, and clicking OK, as shown below:
2. Open the Server Explorer (by pressing Ctrl+Alt+S or by selecting View->Server Explorer from the menu bar), expand the STAFF table, which resides in the SAMPLE database, and view the properties of its columns (by selecting this option from the pop-up menu) to see if a primary key exists, as shown below.
Note: The Primary Key field in the Properties view will be 1 if it is a primary key on the table. For the STAFF table, the ID column is the only column eligible to be a primary key.
If the ID column isnt a primary key (it isnt by default), select the STAFF table, right-click, and select Open Definition, as shown below:
The Table Designer opens. Make the ID column a primary key by clicking the ID column in the Columns box, and changing the Primary Key attribute in the Column Properties box to True using the associated drop-down list, as shown below:
You just opened a really cool (and unique) feature with the Visual Studio 2005 and DB2 integration called a Designer. (Ill cover these handy tools in a future article.)
To save your changes, press Ctrl+S or simply close the designer using the x () button in the top-right corner of the Table Designer. Either of these actions will have the effect of altering the table to add the primary key. Before any changes are made, Visual Studio 2005 reminds you that you are changing the underlying tables structure and asks for confirmation, as shown below:
If you have the authority to make this change, Visual Studio changes the underlying DB2 table. The Table Designer is dynamically updated to show this change:
You can see in the previous figure the key icon () beside the STAFF tables primary key, which is now the ID column.
You could have built an application that simply populates a .NET data grid with a table that doesnt have a primary key. However, if you want to perform INSERT, UPDATE, and DELETE operations on this data, you need a primary key. Why? The DB2CommandBuilder, which is the code that automatically builds the INSERT, UPDATE, and DELETE commands, requires a primary key in order to be able to build the corresponding statements that it will use to change the underlying tables row.
It is 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 database to process the data change request, how can it be sure it will change the data for the row that was fetched: by a primary key! 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, but we want it slick and easy.
If the Table Designer is still open, close it now.
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. Your Visual Studio 2005 workspace should now look similar to this:
4. Click Add New Data Source in the Data Sources view. The Data Source Configuration wizard opens, as shown below:
5. Select Database and click Next.
Note that you can select data to populate your data set from different types of data repositories. If you selected the Web Service icon in the previous figure, Visual Studio 2005 would open a wizard that lets you discover Web services within your solution, on your local machine, or even in a UDDI directory (which DB2 9 supports). An example of browsing for a Web service in Visual Studio 2005 to populate your data set is shown below:
6. Select the SAMPLE database from the Which data connection should your application use to connect to the database? drop-down list.
Note: If the database that you want to connect to doesnt appear in this list, you can add a new database connection by clicking New Connection. This will open the same dialog box detailed in the Addendum: Adding a DB2 Data Source section mentioned earlier in this article.
7. Select the Yes, include sensitive data in the connection string radio button, and click Next.
8. The defaults on the next pane of this wizard are fine for this article. Click Next.
When you select the default Yes, save the connection as check box, Visual Studio 2005 saves the connection setting in an application configuration file that becomes part of your project. This makes deployment of your application easy.
9. Expand the Tables toggle, select the STAFF table, and click Next and then Finish, as shown below:
You can select multiple sources from your underlying data server to populate the disconnected data set. In the previous figure, Ive added a single table. You could have multiple tables, or a combination of tables and views. In addition to this, you could even use a function or stored procedure to populate the data set.
After adding the data set, the Visual Studio 2005 integrated development environment should look like this:
Note the STAFF Data Set in the Data Sources view. (Expand it to see all of its contents if it isnt automatically expanded for you.)
10. Use the left mouse button to drag the STAFF Data Set to your Windows Application form and resize the data grid so it looks like this:
11. Press F5 to build your application. It should look like this:
You can see how quick that was to build this application. You can use this data grid to INSERT, DELETE, and UPDATE data as well. Stop this application by closing this window in Visual Studio 2005.
Going Beyond the Defaults
While I showed you how to quickly build this data grid, Visual Studio 2005 offers you a number of customizations and other rapid application development features that you can take advantage of with your DB2 database. Ill outline a couple of them in this section.
Controlling the Default Data Grid
You have a lot of control over the default data grid thats generated for your sample applications. You can access these options by clicking on the Data Sets Properties toggle, as shown below:
Using this dialog box, you can suppress the adding, editing, and deleting options (included by default) of your data, and even enable the data grid to perform column reordering.
You can also preview the data that populates the grid, and even alter it via changing the underlying query that is used to populate the data set:
Use the Data Sources Windows to Customize the Form
You can use the Data Sources view to customize the data grid by dragging components of the data grid onto your form.
Perform the following steps to further customize your data grid:
1. Click the ID columns drop-down list and select the NumericUpDown option, as shown below:
Notice how the icon beside this column changes to identify that this column is now bound to a NumericUpDown control: .
2. Drag the ID column so that it sits above the data grid, as shown below:
3. Repeat Step 1 and Step 2 for the DEPT column, only this time ensure that it binds to a Label control, which you can select from this columns drop-down list as well. Your application should now look like this:
4. Press F5 to build your application again. This time, look at the controls you added to the top of the data grid. Your application should look like this:
Notice that the controls you dragged onto your application are now bound to the data set. While we didnt write any code to allow navigation within the data set, you can see that the rows and columns are indeed dynamic. Move the cursor to the row that has an ID=60, as shown below, to see for yourself:
You can see that the ID and DEPT controls that you added are dynamically updated.
You can do a lot more with these controls; I just highlighted two of my favorites.
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 thats gone into this environment for DB2 databases.
Theres 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!
Addendum: Adding a DB2 Data Source to the Visual Studio 2005 Server Explorer
If youre familiar with the Visual Studio.NET 2003 support that DB2 UDB provides, youll 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 by which 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:
Note: If you already have a database connection to the database you want to provide your ASP.NET Web site with data, you can skip this section.
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 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 list 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 frequently work with DB2 database connections, I recommend you select the Always use this selection check box. When you add another database connection, Visual Studio 2005 will automatically select the DB2 data provider if this option is selected.
3. Enter the server name and port number (separated by a : ) 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 enumerate all the databases configured to respond to DB2 network database identification requests and automate this process.
4. Enter your user account credentials in the User ID and Password fields. I recommend you save these credentials in the connection string (they are encrypted) by selecting Save my password. Selecting this option makes application development more streamlined, as you are not challenged to provide authentication details during subsequent accesses requests to the DB2 database.
5. Select the database name from the Select or enter a database name drop-down list, 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 dont have the SAMPLE database created on your workstation, you can create it now by entering the db2sampl command from a Windows-based command prompt.
6. Optionally 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.
7. Test the connection using the Test Connection button.
8. Click OK.
After adding your database connection, the Visual Studio 2005 Server Explorer should look similar to the following:
In the previous figure, you can see that Ive expanded the SAMPLE database connection. Below this database connection 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.
» 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 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: firstname.lastname@example.org.
IBM, DB2, and DB2 Universal Database 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, 2006. All rights reserved.
The opinions, solutions, and advice in this article are from the authors 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 authors knowledge at the time of writing.