The Big Picture: DB2 and Visual Studio .NET

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Introductory

Abdul Al-Azzawe, DB2 Debugger and Development Tools Architect, IBM

17 Feb 2005

Want an overview of the IBM® DB2® Universal Database™ V8.2 Development Add-In and managed provider for the Microsoft® .NET platform? Learn about the key new RAD features, DB2 database project, the rich scripting wizards, and the CLR stored procedures support that are tightly integrated into the Visual Studio .NET IDE and see how they will simplify your application development tasks.

Overview

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.

Integration features

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.

Availability

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

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)

DB2e tutorials on developerWorks For more information, refer to the following two articles on developerWorks: Synch mobile apps with DB2 Everyplace and .NET Develop DB2 Everyplace apps with .NET

There are two versions of the DB2e managed provider, namely, Data.DB2.DB2e , which runs on the .NET Framework, and IBM.Data.DB2.DB2e.CF , which runs on the .NET Compact Framework.

Basic RAD tooling is provided for the Data.DB2.DB2e provider 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 DB2eConnection, DB2eCommand, DB2eDataAdapter, and so on.

Back to top

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 IBM.Data.DB2 namespace.

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.

Performance implications

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.

Back to top

Scripting wizards

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.

Wizards overview

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.

Index wizard

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.

Trigger wizard

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.

View wizard

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 CREATE 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 CREATE 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.

Back to top

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.

Project overview

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.

SQL editor

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.

Startup procedure

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.

Back to top

IBM Explorer

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.

Discovery

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 further editing.

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 editing.

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.

Back to top

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.

Toolbox controls

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 DB2Connection, DB2Command, and 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 user/password entered.

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.

Command editor

The command editor may be used to configure DB2Commands found 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.

Back to top

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.

Back to top

SQL debugger

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.

Back to top

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 the %APPDATA%\IBM\DB2\vsnet\userOptions.xml file.

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.

Resources

• The tutorial DB2 z/OS SQL procedures in VS.NET - build options demonstrates the use of the advanced features of the IBM DB2 Development Add-In for Visual Studio .NET to create and deploy SQL stored procedures from a development environment to a production DB2 for z/OS database environment.
  
  
• The tutorial ADO.NET data adapters using DB2 UDB V8.2 procedures walks you through the steps required to build an ADO.NET application that uses SQL stored procedures to select, insert, update, and delete rows from database tables and views.
  
  
• The tutorial DB2 Data Bound ASP.NET Form using VS.NET demonstrates how to use the DB2 Development Add-In for Visual Studio .NET to build a rich ASP.NET WebForm in C# using design-time and run-time data binding.
  
  
• The tutorial Binding DB2 Stored Procedures to Visual Basic WinForms demonstrates the use of the Visual Basic WinForms project template and the IBM DB2 Development Add-In to create a client application that accesses stored procedures and tables residing in a DB2 UDB for z/OS database. This tutorial also applies to DB2 on distributed platforms.
  
  
• The tutorial Developing a VB.NET Federated Application for Microsoft Access demonstrates the use of the ODBC wrapper technology available through DB2 Information Integrator and the IBM DB2 Development Add-In to create a client application that accesses tables residing in a Microsoft Access database. To showcase the ease of using these IBM technologies, we will walk you through the steps required to build a sample application.
  
  
• To learn more about DB2, visit the developerWorks DB2 zone. You'll find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Get involved in the developerWorks community by participating in developerWorks blogs.
  

About the author

		Abdul Al-Azzawe is the DB2 Tools architect for Visual Studio .NET. He joined IBM in 1990 and worked in the Toronto lab for ten years as part of the DB2 engine development team with special focus on the Windows platform. In March of 2000, Abdul joined the IBM Silicon Valley lab to architect the release of the DB2 integrated SQL debugger, the DB2 Development Center, and the DB2 Development Add-Ins for Visual Studio 6.0. In May of 2002, Abdul joined the IBM San Francisco team to architect the integration with Visual Studio .NET, one of his proudest achievements in his career at IBM.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Back to top