Microsoft.NET

……………………………………………….Expertise in .NET Technologies

30 Reasons to Love the DB2 Add-in for VS.NET 2005

Posted by Ravi Varma Thumati on October 8, 2009

To finish the year off with a bang, IBM released a new DB2 Add-in for Visual Studio .NET 2005. This one goes way beyond procedure wizards and data provider classes, giving .NET coders so much DB2 Tooling that they can conceivably create apps without writing a single line of code. Take a look at the 30 top features you need to know about. 

It’s all about options. That seems to be IBM’s motto as they continue to expand DB2 functionality across all development platforms. What might once have been a strange bedfellow is fast becoming just another tool for DB2 programmers as IBM continues to put the “universal” in “Universal Database”.

Take, for instance, Visual Studio .NET. For some time now, DB2 has allowed developers to create stored procedures in just about any language. Last year, IBM released a Visual Studio .NET add-in with tooling to help .NET developers pump out procedures in C# and VB.NET. IBM also released the DB2 Data Provider for the .NET 2.0 frameworks, which gave developers a set of classes for working directly with a DB2 data source (see below for a refresh).

Now, an all-new add-in for Visual Studio .NET 2005 goes even further. With powerful new IBM Tooling, VS developers can:

  • Build Windows and Web applications without writing code.
  • Build and alter DB2 databases through the Server Explorer window.
  • Create, manage, and even clone database objects through the IBM Designers.
  • Automatically cache schema information and pre-defined result sets for faster design and development.
  • Debug SQL procedures in new ways using the IBM Designer for Procedures.

With changes at high and low levels, the new Tooling does a lot. But for now, I’ll focus on one particular area of interest: the IBM Designers and what they offer to .NET developers. You can find more complete documentation, with screenshots, on the IBM developerWorks site.

Overview of IBM Designers

All of the designer views share a few features in common:

  1. Similar look and feel between all views, with toolbar and menu-level access to other views.
  2. Smart editor with statement completion, Intellisense, and color coding.
  3. Automatic or manual caching from a DB2 Connection (as defined in the Server Explorer).
  4. File -> save function with executes the pending script being created in that view.
  5. A section for extended properties, pre-populated with default values.

In addition, each of the designer views has a little something extra to offer.

Table Designer

With the Table Designer, you can quickly create or alter tables in your DB2 database. Here are the available views.

  1. Columns View: define columns and data types. Also set extended properties like primary keys and default values.
  2. Keys View: define primary, unique, and foreign keys. The tool pre-populates your options based on column eligibility.
  3. Indexes View: create indexes for the table columns you added in the Columns view.
  4. Triggers View: define and edit triggers for the table, including event properties.

10.  Privileges View: grant and revoke roles and privileges for database users.

11.  Script View: look at a creation script for the table, with DROP statements already commented out in case your trigger finger twitches.

Altering a Table

When you save the table you just created, or open an existing table, the Table Designer goes to “alter mode”. The options remain essentially the same, except for two handy differences:

12.  Columns View: possible data types are pre-populated based on eligible conversions.

13.  Script View: you can now look at an alter script instead of a creation script. Saving then executes this script.

14.  Clone Definition: you can copy a table definition to a new table by choosing “Clone Definition”. All views for the new table are pre-populated based on the original table.

View Designer

Create and manage views using functionality similar to tables.

15.  View View: while viewing the View view, you can create new views or review existing views. This includes checking syntax and even running test executions, which always roll back when completed.

16.  Other Table Designer functionality is also available:

  1. Triggers View
  2. Privileges View
  3. Script View
  4. Show Data
  5. Show Script
  6. Save, which automatically puts you in Alter mode as if editing an existing View

Data View Designer

When you choose “Show Data” from the context menu or top-level menu, you can directly manage the data of a table.

17.  If the table has a primary key defined, you can add, edit, or delete rows.

18.  Sort, filter, and/or select columns to appear.

19.  Import/export table data as XML.

Script Designer

This is your window for playing with SQL directly. Here, you can:

20.  Execute SQL scripts that modify table data or structures.

21.  View any execute results in grid layout.

Procedure Designer

When you select “Add New Procedure” from the context or main menu, you open the Procedure Designer. In addition to the procedure creation and management one might expect, you can do a few other things:

22.  Add/edit parameters for the procedure using helpfully pre-populated data types. You can also edit extended properties, such as parameter direction, length, etc.

23.  Modify a procedure body that was automatically generated for you on creation. This auto-gen procedure defaults to a single result set, though you can easily edit it to return multiple result sets.

24.  Clone procedures as you would table structures. This also duplicates all the original property values and gives the procedure a unique name.

Debugging Procedures

Procedure debugging, one of the jewels of previous DB2 Add-ins, has been updated significantly. While previous techniques have been deprecated, you can now debug using the Procedure Designer.

25.  Prepare a procedure for debugging by setting its debug mode option to ALLOW either during creation or later by altering it (which essentially drops and recreates the procedure).

26.  Use breakpoints and the Step Into button to go through the code line by line.

27.  Watch or change parameter values and observe the effects, the technique I like to call “What happens when I do this?”

Defining Result Sets

While it’s not a tool or designer, per se, the ability to define result sets for development purposes gives IBM tooling an edge over other add-ins.

28.  Use the result set interface to manually define sample data for a procedure. You can also automatically discover a result set in most conditions.

29.  Use the cached result set to quickly write code. This data is used to auto-complete your syntax in the various editors.

DB2 .NET Data Provider (Reason #30)

Though not necessarily a new feature, the DB2 .NET Data Provider deserves a special shout-out. You can conceivably create entire apps using only the new DB2 Tooling. But most coders will eventually want to dive into some actual code using the various Data Provider classes.

The IBM.Data.DB2 namespace provides all the classes you would expect or need. Patterned after existing Framework classes, they include the basics, like DB2Connection and DB2Command, as well as over a dozen ancillary classes.

For example, here are the four key DB2 objects, alongside their parallel Framework objects:

DB2 Add-In

.NET Framework

Purpose

DB2Connection

Connection

Connect to a data source.

DB2Command

Command

Execute a command against a data source after connecting to it. Also exposes Parameters.

DB2DataReader

DataReader

Read a forward-only, read-only stream from the data source, optimized for speed.

DB2DataAdapter

DataAdapter

Populates a DataSet and also resolves updates with its data source.

Going Forward

Over the coming months, IBM developerWorks will be publishing new walkthroughs and tutorials to show you new techniques for using DB2 tooling with VS.NET. So if you’ve been waiting for a smoother way to hook up your DB2 data to a .NET app, now’s the time to try it out. With better debugging and a whole collection of powerful Designer views, the latest DB2 Add-in is just what you need.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: