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:
- Binding DB2 Data to Visual Studio 2005 Windows Applications
How to quickly build a Windows Application that binds various controls (ComboBoxes, DataGrids) using the new Visual Studio 2005 support announced for DB2 9.
- How to Build an ASP.NET Web Site Using DB2 Data with Visual Studio 2005
How to build an ASP.NET Web site that is backed by a DB2 data store.
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 we’re not specifically binding controls to an application: it’s 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 isn’t a primary key (it isn’t 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. (I’ll 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 table’s 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 table’s 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 doesn’t 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 table’s 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 doesn’t 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, I’ve 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 isn’t 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.
