Important: Read the disclaimer before reading this article.

Note from the editor: The IBM DB2 Development Add-Ins for Visual Studio .NET are available as part of DB2 V8.1.2 and later. The information in this article applies to Visual Studio .NET 2002 and Visual Studio .NET 2003 only.

Introduction

Microsoft® Visual Studio .Net is fast gaining momentum as a key integrated software development environment on the Windows® platform. The IBM® DB2® Development Add-Ins for Visual Studio .Net is a set of tightly integrated application development and administration tools specifically designed for DB2 Universal Database^TM (UDB). The primary goal of the DB2 extensions is to provide to first class DB2 development experience within the Microsoft Visual Studio development environment.

This article gives you a detailed overview of the new DB2 Data Connections folder of the IBM Explorer in Visual Studio .NET. First, we will look at what you can do with the Server Explorer as a starting point to highlight the added capabilities of the DB2 Data Connections folder.

Using the Visual Studio Server Explorer

You can use the Server Explorer to view and manipulate data links, database connections, and system resources on any server to which you have network access.

Although functionally rich, the Server Explorer has some general limitations:

• Individual Data Connection subfolders lack filtering support prior to folder data retrieval.
• Individual Data Connection subfolders do not cache item data across sessions, thus requiring live connection and requeries to populate the various folders.
• The Data Connection folder does not support adding DB2 managed provider connections - only ODBC and OLE DB connections are supported.
• A frequent pop-up dialog prompting for DB2 user name and password.

The IBM DB2 Development Add-In extends the Visual Studio .NET environment by adding a new tool window called IBM Explorer, which addresses most of these limitations and provides you with additional features.

Back to top

Introduction to the DB2 Data Connections folder of the IBM Explorer

The IBM Explorer tool window (Figure 2) provides Visual Studio .NET users with access to IBM database connections using the Data Connections folder. The Data Connections folder in the IBM Explorer is similar to the Data Connections folder in the Server Explorer, but it is specifically designed for DB2 managed provider connections.

With the Data Connections folder in the IBM Explorer you can:

• Work with multiple named DB2 connections supporting "connect on demand" technology.
• Access database catalog information asynchronously.
• Specify database catalog filters and local caching for higher performance and scalability.
• View properties of server objects including tables, views, stored procedures, and user-defined functions (UDFs).
• Retrieve data from tables and views.
• Execute test runs for any language stored procedures and UDFs.
• View source code for SQL stored procedures and UDFs.
• Drop (delete) of tables, views, stored procedures, and UDFs.
• Generate ADO .NET code using drag and drop.

Back to top

Working with connections

Using the Add Connection menu action on a connection folder, you can add any number of DB2 managed provider connections. You can add multiple connections to the same database alias, provided that a different user name and password are associated with each connection. This feature means that you can have different folder filters as well as different access authorities for the same connection database alias.

The DB2 database connection folder currently supports four subfolders: tables, views, procedures, and functions.

The DB2 data connections folder currently supports DB2 for Linux, UNIX®, Windows V8.1, and DB2 UDB for z/OS^TM and OS/390® V7.

Adding new connection

To add a new DB2 database connection, simply select the Add Connection menu action for the Data Connections folder.

As shown in Figure 3, when adding a new connection, you can specify default connection options, such as the default schema, as well as the default build owner for z/OS. You can also specify the default schema filter that will be used for the connection subfolders.

A default connection name is given to newly added connection. It is a combination of the database alias name and the user name. You can change this name later using the Rename menu action on the connection folder.

Modifying connections

To modify the connection properties at any time, including the database alias, user name, password, as well as the connection options. Simply select the Modify menu action on the connection folder.

In the options tab (shown in Figure 4) , you can specify the default SQL Schema or SQL ID name that will be used for unqualified DB2 objects. For example, if you issue SELECT * from MYTABLE, DB2 will first look for <SQL Schema>.MYTABLE and if not found, then <User name>.MYTABLE. The build owner is the z/OS secondary authorization SQL ID that will have permission to DROP and ALTER SQL stored procedures.

Deleting connections

Use the Delete menu action on a connection folder to remove the selected connection from your list of data connections. Be careful, because as all of the locally cached data associated with this connection will be lost.

Back to top

Working with tables and views

The IBM Explorer presents you with the list of server-side tables and views for any given data connection. In the case of tables, both regular tables as well as nicknames (names for remote data sources) are supported in the same folder. The tables and views folders allow for filtering and refreshing from the database server.

As shown in Figure 5, properties for the tables and views are shown in the Visual Studio .NET properties dialog. These properties include the object type, comment, and schema (owner) name.

Expanding a specific table entry will show the list of columns for that table. As shown in Figure 6, when you select a specific table or view column, you will see the properties for that column in the Visual Studio .NET properties dialog. Column properties include the name, data type information, allows null value flag, identity flag, and primary key flag.

Setting filters for tables and views

With the IBM Explorer, you can set filters before retrieving any catalog information for tables and views from the DB2 server. The table and view filters are identical in features.

When a new filter is specified or when you issue a Refresh command on the tables/views folder, an asynchronous task is executed to retrieve the new list of tables/views. If the task is successful, the corresponding folder is updated with the newly filtered list of entries.

The table and views filter means you can limit the list of entries based on schema or actual name of the entries. If you select the Meet all conditions option, the two conditions must be met; otherwise, if either condition is met, the entry is included.

Retrieving data from tables and views

For any table or view in the folder, you can request to retrieve a sampling of the rows for that entry from the server. When successful, results are shown in a data grid (Figure 8), and status messages appear in the IBM DB2 Output pane.

Deleting tables and views

When you delete a table or view, the corresponding entry is dropped from the database server. You cannot undo a delete . You must have drop permission on the table or view in order for the action to succeed.

Back to top

Generating ADO.NET code for tables and views

Using the IBM Explorer, you can drag one or more tables or views and drop these elements on a forms designer, which creates a new DB2 data connection object (if no other data connection object with similar properties exists). A new DB2 data adapter object is also created. This data adapter will have four DB2 commands: select, insert, update, and delete. One adapter is created per table or view. The DB2 command SQL text is based on the set of columns for the dragged table or view.

As shown in Figure 9, the generated DB2 ADO.NET objects are added to the form designer's component tray. Only the DB2 connection and data adapter objects are visible. The generated DB2 commands are invisible.

Adding the DB2 objects to the component tray translates into inserting the following code fragments to your form's code file:

• Declaration of the DB2 managed provider objects as private properties in your form class.
• Insertion of the DB2 objects initialization values in the form's InitializeComponent generated method code region.

All of the required DB2 command parameters are added to the generated UPDATE, INSERT, and DELETE commands.

You can view the generated SQL text and command parameters for the various DB2 data adapter commands by accessing the properties of the DB2 data adapter, as shown in Figure 10.

You can click the Configure Data Adapter command hyperlink to launch the Data Adapter Configuration wizard. This wizard lets you modify the connection properties and the commands for SELECT, INSERT, UPDATE, and DELETE.

Alternately, you can select the DB2 connection object and launch the data connection properties editor to modify the connection string. You can also select the individual command text for each of the data adapter commands and launch the command text editor to modify the command SQL and its associated parameters.

Using the data adapter configuration wizard, shown in Figure 11, you can elect not to create the optional INSERT, UPDATE, and DELETE commands, as some or all of these optional commands may not apply to your application requirements.

You can also elect to re-generate the INSERT, UPDATE, and DELETE commands SQL based on the SELECT command SQL using the DB2 command builder object.

When command text is regenerated, you will need to manually update the parameters list for the command, as shown in Figure 12. This includes specifying the parameter names, data types, as well as the value type, namely, default, original, or current.

Run-time binding of tables and views to form controls

Using the generated data adapter, you can dynamically bind your form controls to the data adapter. To do this, you must:

1. Declare a private dataset object in your form class.
2. Instantiate the dataset in the form load event.
3. Fill the data set with data from the data adapter. You can do this in the form load event.
4. Dynamically bind the form controls to the dataset. This can also be done in the form load event.
5. Invoke the data adapter Update method when required to propagate any dataset updates back to the DB2 database server.

Listing 1. C# code snippet showing dynamic binding of form's data grid to a DB2 data adapter

 
<font color="0000FF">public class</font> Form1 : System.Windows.Forms.Form 
{ 
	<font color="0000FF">private</font> DataSet dataSet1; 
 
	<font color="0000FF">private void</font> Form1_Load(<font color="0000FF"
	>object</font> sender, System.EventArgs e) 
	{ 
		<font color="0000FF">try</font> 
		{ 
			dataSet1 = <font color="0000FF">new</font> DataSet(); 
			db2DataAdapter1.Fill(dataSet1); 
			dataGrid1.SetDataBinding(dataSet1,""); 
		} 
		<font color="0000FF">catch</font>( Exception exp) 
		{ 
			MessageBox.Show(exp.Message); 
		} 
	} 
 
	<font color="0000FF">private void</font> button1_Click(<font color="0000FF"
	>object</font> sender, System.EventArgs e) 
	{ 
		db2DataAdapter1.Update(dataSet1); 
	} 
} 

Design-time binding of tables and views to form controls

For proper design-time binding of form controls to a data adapter, an ADO.NET DataSet must be generated for the given data adapter.

To generate a DB2 data adapter dataset, simply select the required DB2 data adapter and click the Generate DataSet action.

You can also issue the Generate DataSet command against any other OLE DB or ODBC data adapters. Instead of generating the data set of the OLE DB or ODBC data adapter, simply select the DB2 commands you're interested in. This will allow you to use the standard data set generator feature of Visual Studio .NET against DB2 data adapters.

After a data set is generated, you can use the Visual Studio .NET properties dialog to modify the data binding property of any form control so that it directly points to the dataset. For a data grid, you must map the Data Source property to the dataset name, and the DataMember property to the table name within the dataset.

Limitation of data adapter code generation

There are few general limitations when relaying on automatic data adapter code generation:

• When generating the update and delete commands for views, the generated SQL statements may not be sufficient to uniquely identify a row for update or delete. These commands may not work properly unless primary keys are included in the list of view columns.
• When generating insert commands for views, the insert may not succeed in certain cases. These cases include views for tables where some of the non-nullable columns are excluded from the view.
• Generation of automatic insert and update commands for tables having identity columns is not possible. The automatic code generation will not be able to handle these scenarios.
• When generating insert, update, and delete commands for nicknames or read-only views, the generated SQL statementswill fail because such commands are not valid in these situations.

Back to top

Working with procedures and functions

The IBM Explorer presents you with the list of server-side procedures and functions for any given data connection. Although you can only create SQL procedures and functions in the DB2 database project, the procedures and functions folders are not limited to those written in SQL. From this folder, you can filter and refresh from the database server.

Properties for procedures and functions are shown in the Visual Studio .NET properties dialog shown in Figure 14. These properties include the object type, comment, schema (owner) name, specific name, parameter style, and so on. For SQL procedures, the package name is also shown, which means you canuse the DB2 Visual Explain graphical utility to view the SQL access plan for the SQL code contained in the SQL procedure.

As shown in Figure 15, when you expand a specific procedure or function entry, you will see the list of parameters, and in the case of table functions, the list of columns for that entry. When you select a specific parameter or column, you will see the properties for that entry in the Visual Studio .NET properties dialog.

Setting filters for procedures and functions

With the IBM Explorer, you can set filters before retrieving any catalog information for procedures and functions from the DB2 server. The procedures and functions filters are identical.

When a new filter is specified, or when you issue a Refresh command on the procedures or functions folder, an asynchronous task is executed to retrieve the new list of procedures or functions. If the task is successful, the corresponding folder is updated with the newly filtered list of entries.

The procedures and functions filter lets you limit the list of entries based on schema, actual name, and language of the entries. If you select the meet all conditions option, the two conditions must be met; otherwise, if either condition is met, the entry is included.

Running procedures and functions

Using the IBM Explorer, you can execute a test run of astored procedure (or any language) and UDF. An automatic rollback is executed at the end of the test run. If the procedure or function requires input parameters, the Parameter Values dialog is displayed to enable you to specify values for any input or input/output parameters.

When the run is completed, the results are shown in a DB2 Database Results data grid. This grid also includes any input and output parameter values.

Viewing source code for procedures and functions

For any SQL procedure or function, you can use the IBM Explorer to view the SQL source code for the object as it is stored in the database. It will not match the script file that is used to create these objects, because such script files may contain other SQL statements such as DROP, GRANT, and so on.

Deleting procedures and functions

When you delete a procedure or function, the corresponding entry is dropped from the database server. You cannot undo this deletion . You must have drop permission on the procedure or function in order for the action to succeed.

Back to top

Generating ADO.NET code for procedures and functions

Using the IBM Explorer, you can drag a procedure or function and drop iton a forms designer. As shown in Figure 18, a new DB2 data connection object is created if no other data connection object with similar properties exists. An associated DB2 command is also created to invoke the procedure or function. Unlike the drop of a table or view, no data adapter object is created. This is a standard behavior, becauseadditional procedures and functions may be required to perform the update, delete, and insert, assuming these operations are even possible.

The DB2 commands text will be based on the type of object dragged (see Table 1, below). If the procedure or function requires any parameters, these parameter objects are automatically added to the command.

Table 1. Types of ADO.NET commands generated for procedures and functions

There are many ways to make use of the procedure or function command that is in the component tray. If the purpose of the command is to retrieve a data set, then you can simply drop a DB2 data adapter object from the Data tab of the designer toolbox and then assign the procedure or function command to the SelectStatement of the data adapter. You can then hook up your data adapter to a data grid and view the generated data set.

You can modify the connection string for the generated connection object by using the DB2 Data Connection custom editor for the connection string property. This lets you change the database alias, user name, and password.

You can modify the procedure or function command including its parameters list using the DB2 Command Text custom editor for the command text property.

Binding procedures and functions to form controls

For information on binding procedures and functions to form controls, see Design-time binding of tables and views to form controls section and Run-time binding of tables and views to form controls.

Back to top

User cache and connection passwords

The Data Connections folder of the IBM Explorer caches the data connection catalog data on your client. The next time you invoke Visual Studio .NET, it can use the cached data, thus avoiding the need to re-execute the database queries to retrieve the catalog data. You can always refresh the cache by explicitly invoking the refresh action on a specific database connection folder or its individual subfolders.

The catalog cache is typically stored under the %APPDATA%\IBM\vsnet path using the file name, userCache.xml. Do not modify this file manually . If the file is corrupted, it will be replaced automatically with a blank one.

The passwords used for connecting to the various DB2 databases are stored in a separate file under the same path. The file name is passwords.txt. Data in this file are automatically encrypted for security reasons. By saving the passwords, you do not have to specify the user name and passwords on every launch of Visual Studio .NET.

Back to top

Conclusion

The data connections folder of the IBM Explorer provides you with a rich set of features that are specifically designed for DB2 managed provider connections, providing you with advanced RAD capabilities to greatly simplify application development for the DB2 server family. Some of the key features of IBM Explorer include:

• Support for DB2 managed provider data connections.
• Asynchronous execution of catalog queries with support for multiple simultaneous query execution.
• Catalog data filters for tables, views, procedures, and functions before retrieval.
• Local client-side and user-based caching of catalog data and data connection password encryption.
• Working with databases in disconnected mode using data caching and connect on demand technology.
• Access to DB2 tables, views, and stored procedures, and UDFs in any language.
• View properties, retrieve data, and drop (delete) of tables and views.
• View properties, view code, test run, and drop (delete) of stored procedures and user-defined functions.
• Drag and drop of catalog objects onto WinForms for automatic ADO.NET code generation.
• Support for design-time as well as run-time data binding of form controls.

Back to top

Disclaimer:

To the extent that this article describes plans for upcoming product features, the reader understands that product plans are subject to change. Accordingly, the reader should not make product design plans based on such plans or otherwise rely on the statements made herein.

Back to top

Key features of the DB2 Development Add-Ins

The key extensibility features of the IBM DB2 Development Add-Ins for Visual Studio.Net include:

• Solution explorer for building DB2 server-side objects
• Server explorer for gaining access to DB2 server-side objects from any tier of an application
• SQL editor for using the Visual Studio .NET editor to edit and view DB2 scripts
• Dynamic help
• Output views for showing the results of compiling or testing DB2 script files and server objects
• Managed provider for coding server-side objects using the ADO.Net programming model

Resources

• IBM DB2 Development Add-Ins for Visual Studio .Net
  
  
• DB2 Database Project for Visual Studio .Net
  
  
• Creating and Linking LOBs in a Data Application for Microsoft .Net using DB2 Universal Database
  
  

About the author

Abdul H. Al-Azzawe is a senior software engineer at the IBM Silicon Valley Lab in San Jose. An IBM veteran, he joined the company in 1990. Abdul is the lead architect for the SQL debugger and the application development tools for DB2 on Windows. Prior to his current assignment, he was a member of the core DB2 engine development team at the IBM Toronto Lab.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Introduction to the DB2 Data Connections folder of the IBM Explorer
• Working with connections
• Working with tables and views
• Generating ADO.NET code for tables and views
• Working with procedures and functions
• Generating ADO.NET code for procedures and functions
• User cache and connection passwords
• Conclusion
• Disclaimer:
• Key features of the DB2 Development Add-Ins
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers