The Big Picture
DB2 and Visual Studio .NET
DB2 tools for Visual Studio .NET
This content is part # of # in the series: The Big Picture
This content is part of the series:The Big Picture
Stay tuned for additional content in this series.
DB2 reaches a wide audience of developers covering both the .NET and J2EE™ platforms. These developers have their own strong preferences in terms of application development API's and application development tools. IBM has always recognized the importance of these two camps and catered to both their needs.
For Windows® Java™ developers, IBM has a strong offering in terms of JDBC™ and JCC drivers, standalone DB2 Development Center, and the integrated plug-ins for the WebSphere® and Rational Studio® IDE's.
For .NET Windows developers, IBM has recognized the strong demand for a tighter level of integration into that environment. IBM is strongly committed to the .NET platform, specifically Visual Studio .NET, where it has been part of the Microsoft Visual Studio Integrator Program (VSIP) since early 2002. IBM has delivered a native .NET managed provider and a tightly integrated set of tools for the Visual Studio 2002 and 2003 IDE's.
This article focuses on IBM's current .NET support on the Windows client platform.
Some of the main integration points on the .NET platform and Visual Studio .NET include:
- DB2 .NET Managed Provider - The native DB2 .NET managed provider enables application developers to build and run applications using the standard ADO.NET programming model. This native provider provides an improved level of support over the ODBC and OLE DB generic managed providers for DB2.
- Solution Explorer - The DB2 database project templates allow for script-based development as part of the standard set of Visual Studio solutions supporting multi-configuration, source control management, project, and project item build order, as well as editing and debugging of SQL scripts using the built-in editor and debugger.
- Server Explorer - The IBM Explorer provides the same look and feel as the Visual Studio server explorer, enabling catalog access and rapid .NET application development using drag-and-drop functionality.
- SQL Wizards - The rich set of easy-to-use wizards allows for the creation of new server-side tables, views, indexes, triggers, procedures, and functions from either the Solution Explorer or the Server Explorer.
- SQL Editor - The built-in Visual Studio text editor has been extended to provide native support for DB2 SQL scripts having "intelisense" and syntax colorization as well as advanced script options.
- SQL Debugger - The built-in Visual Studio debugger has been extended to provide debugging support for DB2 SQL routines, allowing for source level debugging of cross-platform SQL stored procedures as part of the DB2 database project.
- Dynamic Help - The Visual Studio context-sensitive help has been extended to provide online help for the DB2 .NET managed provider and the Visual Studio AD tools.
- DB2 Tools Toolbar - The various DB2 development and administration centers may be launched directly from the Visual Studio IDE using the DB2 Tools toolbar.
The DB2 AD Tools for Visual Studio .NET were shipped initially as part of DB2 and DB2 Connect V8.1.2. The functionality described here is part of the DB2 and DB2 Connect V8.2, which was released in October of 2004.
Platform support matrix
The following table helps to illustrate the level of support for each of the DB2 server platforms. Note that the distributed column refers to the Windows and UNIX® server platforms.
Table 1. DB2 platform support matrix
|Server Level||V6, V7, V8||V5r1, V5r2||V8|
|Client Level||Connect V8.2||Connect V8.2||ADC V8.2|
|SQL Procedure Wizard||Yes||Yes||Yes|
|CLR Procedure Wizard||No||No||Yes|
|SQL Function Wizard||Yes (scalar)||Yes (scalar)||Yes (scalar/table)|
|WAS Web Services||No||No||Yes|
IBM Informix database support (IDS)
IBM Informix® .NET native data provider is shipped with IBM Informix Client SDK. Version 2.90 of the SDK contains limited tooling support for the IDS .NET managed provider. The Data tab of the Visual Studio ToolBox is extended to list the IDS ADO.NET objects. You can drag and drop these objects on your win and Web form containers. You can also customize these objects using custom editors.
When you install the IDS client SDK, the .NET managed provider and the Visual Studio tooling are automatically installed and configured.
IBM DB2 Everyplace support (DB2e)
There are two versions of the DB2e managed provider, namely,
Data.DB2.DB2e, which runs on the .NET
runs on the .NET Compact Framework.
Basic RAD tooling is provided for the
only since V8.1.4. This tooling provides integration with the VS.NET Data
Toolbox where the developers can set properties of the DB2e ADO.NET
objects such as
DB2eDataAdapter, and so on.
DB2 managed provider
The DB2 .NET managed provider allows for data-centric application development using the standard ADO.NET programming model. Although .NET developers have the choice of using the ODBC, the OLE DB, or the native provider to develop their DB2 applications, the native provider should be your first choice when it comes to RAD tooling and performance.
Provider object model
Like all the other ADO.NET providers, the DB2 managed provider implements
the same set of standard ADO.NET classes and methods. These classes are
defined under the
Figure 1. DB2 ADO.NET object model
The main set of classes includes:
DB2Connection- The data connection class enables you to specify the connection string used to connect to your target DB2 server. Connection pooling is enabled by default. The open and close method calls may actually translate into reserving and releasing a live connection from the application connection pool.
DB2Command- The command object allows for executing any supported SQL statement or stored procedure using a data connection object. Command execution also supports an optional timeout value.
DB2DataAdapter- The data adapter object contains the four optional commands for the select, insert, update, and delete operations, which may be SQL statements or stored procedure calls.
DB2DataReader- You typically use the DB2DataReader for fast forward-only access to disconnected result sets that are returned from executing SQL statements or stored procedure calls.
As stated earlier, although you have the choice of using the ODBC or OLE DB providers, the .NET native provider will yield the best performance due to the elimination of the extra ODBC, OLE DB layers on top of DB2.
The lab has done internal performance testing of the three supported managed providers where we tested data retrieval of two different row sizes with varying number of rows. The results clearly showed that the native DB2 .NET managed provider gave the best performance numbers.
Some of the performance enhancements that were included in the DB2 .NET managed provider include MTS support, loosely coupled transactions, delayed enlistments, and others.
Figure 2. DB2 Providers Performance
One of the key new enhancements for the DB2 V8.2 Development Add-In is the set of functionally rich scripting wizards for generating the required CREATE DDL for tables, views, triggers, indexes, stored procedures, and functions.
There are two integration points for launching the DB2 scripting wizards; namely, from the DB2 Database Project in the Solution Explorer view, and from the DB2 Data Connection in the IBM Explorer view.
The scripting wizards guide you through the steps required to customize the generated DDL and SQL statements for creating new DB2 schema objects and routines.
When the wizards are launched from the IBM Explorer, you can execute the generated DDL directly, or optionally add it to a new or existing DB2 database project.
When you launch the wizards from the Solution Explorer, the generated script is added to your DB2 database project for later compilation or project build. The DB2 scripting wizards give you a great advantage over the generic Database Project for SQL Server.
Table wizard (and import dialog)
The table wizard allows for generating the
CREATE TABLE DDL
for the DB2 family of servers. You can specify advanced value options
(identity, generated, and so on), unique keys, foreign keys, primary keys,
and check constraints.
One key feature of the table wizard is the ability to import column definitions for other tables and views in the data connection using a common import columns dialog that is also used by a number of other add-in wizards and dialogs. This feature allows for the ability to clone tables as well as define the foreign key columns.
The index wizard allows for defining the
CREATE INDEX DDL for
the DB2 family of servers. You can specify advanced options on the index
as well as define multiple columns in the same index with ascending or
descending order clause. The wizard automatically detects the column
definitions of the base table being indexed.
The trigger wizard allows for defining the
CREATE TRIGGER DDL
for the DB2 family of servers. You can create triggers for tables and
views where the table and view definition is automatically detected.
Instead Of triggers are also supported. You can customize all of the
trigger DDL options including the when to trigger, the frequency, and the
action to name a few.
The view wizard allows for defining the
CREATE VIEW DDL for
the DB2 family of servers. You can use the built-in schema intelisense
support to easily define the required SQL query for your view.
SQL procedure wizard
The SQL stored procedure wizard allows for defining the
PROCEDURE DDL and routine body for
the DB2 family of servers. You can define zero or more result sets,
automatically discover or import parameters, and use the schema
intelisense to code the routine body. The wizard also allows you to
specify the advanced SQL build options for z/OS SQL stored procedures or
use the connection level default build options.
SQL function wizard
The SQL user-defined function wizard allows for defining the
FUNCTION DDL and routine body for
a scalar or tabular function for the DB2 family of servers. The return
types as well as any input parameters are automatically discovered. You
can use the schema intelisense to code the body of the function routine.
DB2 database project
Using the Visual Studio Solutions Explorer you can create one or more DB2-specific scripting projects to manage the execution of your server scripts. The script files may contain any supported DDL, DML, and DCL SQL statements.
DB2 database project
The DB2 database project is a full-featured Visual Studio project template supporting multiple configurations, compile/build, project build order, source control management and versioning, and startup routines.
You can create new project items using text-based script templates or
directly launch any one of the scripting wizards detailed earlier to
generate the required
CREATE DDL for the various DB2 objects.
The full-featured support of the DB2 database project makes it far superior to the limited generic SQL Server database project.
Multiple configurations support
Solutions in Visual Studio support a multi-configuration option where project items may be built with a different set of project and project item properties, depending on the selected configuration.
The DB2 database project fully supports this multi-configuration feature. The build for debug or build for release, which applies to SQL stored procedures on Linux, UNIX, and Windows, makes use of this feature to generate the required debugging information for these stored procedures. Additionally, you could specify a different set of z/OS® SQL build options per configuration and a different target data connection per configuration for compiling your script files. You can thus deploy the same set of scripts from the testing data connection to the production data connection with a simple configuration name switch.
Item build order
The default build order for your project script items is to build them in the order they were added to the project. More often than not, you will require a different script build order based on script dependencies. An example would be the need to first create the set of tables before creating the stored procedure that access them.
You can use the project items build order, a feature unique to the DB2 database project, to define the configuration specific script items build order.
Source version control
As with most other project templates in Visual Studio, the DB2 database project has implemented the proper interfaces for integrating with any configured source control management system such as Clear Case or Visual Source Safe.
The standard Visual Studio .NET text editor and language services have been extended to support the DB2-specific script syntax colorization, data connection schema intelisense, advanced scripting options, and SQL code fragments insertions.
Some of the advanced scripting options includes the ability to specify collapsible code sections (also known as hidden text), compile error ignore, result sets output capture, and platform-specific compilation.
Using the DB2 database project properties, you can specify the compilation data connection as well as the startup stored procedure and parameter values. This startup stored procedure is invoked when you run or debug the DB2 database project. The default action is to roll back after executing the stored procedure. This can be modified using the user options XML file.
The IBM Explorer is a new view introduced to support DB2-specific managed provider data connections. This view offers greater functionality to the Server Explorer data connections. This additional functionality did not, however, alter the Server Explorer RAD programming paradigm.
The IBM Explorer data connections display catalog information using a tables, views, procedures, and functions folders. In addition to these catalog folders, the new data adapters folders allows for defining re-usable ADO.NET data adapters that may be shared across applications and with other team members.
When adding data connections, you can add local and remote database connections. Remote connections may be discovered if the DB2 Admin Server is enabled on the server platform. You can alternately add remote connections that have been catalogued locally or by simply specifying the host_name:port_number for the server field.
Multiple named connections and filtering
Using the IBM Explorer, you can add multiple data connections to the same target DB2 server and database each having a unique name. Multiple connections combined with the folder level filters, can be used to project different views on the same database. The filtering capability allows you to reduce the amount of catalog information to what is relevant to your application components.
Caching and refresh
One of the early design goals of the IBM Explorer was to enable developers to work in disconnected mode. The catalog information is cashed locally on the client workstation. Subsequent launch of the Visual Studio IDE will re-use this cached catalog information.
To help users recognize stale data, a time-stamp property is shown at the folder level to reflect the last date and time when the cached information was retrieved. You have the option of refreshing the cache from the server at any time.
Support for tables and views
The tables and views folders may be filtered prior to any catalog data
retrieval. Using these folders you can launch the appropriate wizard to
create new tables and views. You can retrieve table and view data, alter
the data, and propagate the updates back to the server. You can also drop
these objects or generate the
CREATE DDL script for them for
The details view on the tables and views allows you to manage the indexes and triggers for these objects.
You can drag tables and views and drop them on form designers to automatically generate and configure the required ADO.NET component tray objects.
Support for procedures and functions
Procedures and functions folders may be filtered prior to any catalog data
retrieval. You can create new SQL procedures and functions directly from
these folders by launching the appropriate scripting wizard. You can also
execute a test run of these objects. By default, a rollback is issued
after the procedure or function is executed from the IBM Explorer. You can
force a commit after the execution by alter the appropriate value in the
userOptions.xml file. Additionally, you can drop procedures
and functions or view the cataloged source code for them for further
You can drag procedures and functions and drop them on form designers to automatically generate and configure the required ADO.NET component tray objects.
Re-usable data adapters
The ADO.NET data adapters are powerful data objects in that they encapsulate the insert, update, delete, and query operations. Developers spend a great deal of time defining and customizing these data adapters; however, sharing them among forms, across projects, or among users is next to impossible.
The IBM Explorer data connection has introduced this new folder under DB2 data connection allowing users to define and re-use data adapters. The same set of RAD features may be used to drag and drop these adapters onto win and Web form designers as well as to preview and modify the data retrieved by these data adapters.
Once data adapters are defined in your data connection, you can automatically generate data sets for these adapters and add the data set definitions to your project. This can be done without having to drag and drop the adapter onto your form designer. You can then use your formatted and typed dataset in your application development, including the ability to generate data forms using the data form wizard.
One powerful feature of the re-usable data adapters is the ability to deploy them to the DB2 embedded WebSphere application server or to generate IIS web methods for deployment as web services. This zero-code operation allows for fast deployment of your data connection SQL or stored procedures as Web services.
Another interesting feature of the re-usable data adapters is the ability to import and export one or more data adapters as XML files for sharing with other users or for check-in/check-out into source control management.
DB2 RAD and Visual Studio .NET
The DB2 V8.2 Development Add-In for Visual Studio .NET provides two key application development capabilities; namely, developing server-side schema and logic as detailed earlier using scripts, and developing client-side or middle tier ADO.NET application components using rapid application development (RAD) features.
Drag-and-drop from IBM Explorer
You can drag any of the IBM Explorer schema and logic objects and drop then on your winform and webform designers. The drag-and-drop operation will automatically generate the required ADO.NET connection, command, and data adapter objects and add them to the form's component tray.
Additionally, you can drag and drop your preconfigured ADO.NET data adapters from the Adapters folder of your connection and drop them onto your component tray.
When working with component-based objects, such as a form or Web
service designer, you can drag and drop objects directly from the Data
section of your toolbox and drop them onto the designer canvas. The
DB2DataAdapter objects are automatically added to the toolbox
when the DB2 .NET managed provider is registered on your system.
Unlike drag-and-drop from the IBM Explorer, toolbox objects must be configured using the custom ADO.NET editors outlined below for proper operation.
Connection string editor
The connection string editor allows you to configure your component tray
DB2Connection object. The connection string is automatically
generated based on the IBM Explorer data connection you choose and the
Data adapter wizard
The DB2 data adapter configuration wizard may be used to set the options of your component tray data adapters including the data connection, select command, insert command, delete command, and update command.
The wizard can be used to define commands that execute direct SQL or commands that call stored procedures.
You can also define the shape of your select command, be it an SQL statement or stored procedure call, by either discovering the result set or manually defining the one or more returned result sets.
For stored procedures, you can map the input and output parameters of the stored procedure to the data set source columns as defined in the select command.
The command editor may be used to configure
in the component tray. The same set of UI elements used to define the
various data adapter commands are used in the standalone command editor.
SQL parameters found in your SQL statement are automatically discovered
but can also be manually defined.
Generate data set
Once you have configured your data adapter, you can generate the data set definition that will host your data adapter disconnected result sets. The generate data set dialog allows you to both generate new data sets and include the resulting set data tables in an existing project data set.
Once the data set is defined, you have the option of including the data set object instance in your component tray for rapid data source mapping to form controls in your forms designer.
CLR procedures and functions
DB2 has a long history of supporting a multitude of stored procedure programming languages, including C, Java, Perl, Cobol, and REXX, to name a few. It was only natural to add support for CLR-based stored procedures and functions supporting all of the CLR languages such as C# and Visual Basic.
DB2 released support for CLR stored procedures and functions long before
any other database server, including Microsoft SQL Server. This included
engine support where the CLR procedures and functions are executed,
managed provider support using the
DB2Context object, and the
Visual Studio AD tools.
A new Visual Studio project template was added to the standard list of C# and Visual Basic projects, namely, a DB2 Class Assembly project. This project template is almost identical to the C#/VB Class Assembly project, but with the added automatic reference to the IBM.Data.DB2 namespace and assembly, as well as the pre-canned class samples for stored procedures. You can use this project template to define multiple CLR stored procedures and functions.
Once your CLR project is compiled, you can then generate the required DB2 DDL script to define the one or more CLR stored procedures and functions. The built-in DB2 CLR Procedure Wizard may be used to automatically detect projects in your solution that may have candidate CLR procedures. The CLR wizard will then allow you to customize the generated DDL and data type mapping. Once generated, the DDL script is then added to a DB2 database project in your solution for later deployment. The project assemblies are also added to the list of assemblies that must be deployed to the target DB2 server as part of the DB2 database project build process.
You can manage the list of CLR assemblies to be deployed to your server using the Assemblies menu action on your DB2 database project. You can add, remove, as well as choose the debug versus release version of the assemblies to be deployed.
Once deployed, CLR procedures behave like any other language stored procedures and may be used as part of your applications.
The integrated cross-language Visual Studio debugger was extended to support the back-end DB2 SQL debugger. You can now step into nested SQL stored procedure calls, set line breakpoints, variable value change breakpoints, as well as modify variable values while executing your database server routine.
The initial debugger support is built into the DB2 database project where you can specify the startup-stored procedure which will be executed in debug mode. This support was limited to the DB2 for Linux, UNIX, and Windows servers.
Moving forward, the debugging support is receiving greater attention where the list of platforms supported will be expanded to include z/OS and iSeries. Additionally, directly launching the debugger from the Server Explorer or as part of a full-blown application debug session is also under investigation.
Online help and user options
There is a rich set of user customization options supported by the DB2 AD
Tools for Visual Studio. Some of these options are exposed directly from
the standard Visual Studio Tools->Options menu under
the IBM Tools folder. Additional options may be accessed directly using
When you register the DB2 AD Tools and Managed Provider, the online help for both the tools and managed provider are automatically registered with the Visual Studio built-in help facility. You can access both the dynamic as well as the content-based help. Additional online help may also be found using the DB2 Information Center, which can be launched from the DB2 Tools toolbar.