In a previous set of articles,
I showed you all the great integration features between the IBM DB2 9 for Linux,
UNIX, and Windows (DB2 9) data server and Microsoft Visual Studio 2005. In another series,
I showed you the integration between DB2 and Visual Studio 2003. Quite simply,
DB2 has been providing – in my opinion – the richest and most complete database
integration for .NET developers, often setting the standard for other database
vendors and defining database development productivity.
With DB2 9.5 Fix Pack (or the generally available version of DB2 9.5 with the latest IBM Database Add-ins for Visual Studio) DB2 now supports Visual Studio 2008 which became generally available earlier this year
.
In this article, I want to get you started on the path to
DB2 and Visual Studio 2008 for .NET developers; additionally, I’ll give you
some hints and tips to get you in the express lane when it comes to enabling .NET
developers to build DB2 data-bound applications.
Things to do in order to get started with Visual Studio 2008 and DB2
Before you get started using Visual Studio 2008 to build
your .NET DB2 applications, you need to have the right client connectivity
software to connect to the target data server and the component (referred to as
an add-in) that provides the rich integration for your DB2 server.
As of DB2 9.5, if you want to connect your .NET application
to a DB2 for Linux, UNIX, or Windows data server, you need to a minimum deploy
the new IBM Data Server Driver for ODBC, CLI, and .NET. This new driver takes
up a mere 10 MB and affords you the opportunity to use the smallest footprint
possible for .NET connectivity. This driver supports the .NET 2.0, 3.0, and 3.5
frameworks, just like Visual Studio 2008. Before DB2 9.5, you had to install
the DB2 Runtime Client (about 120 MB) to enable this connectivity. You can
download the IBM Data Server Driver for ODBC, CLI, and .NET at: http://www-306.ibm.com/software/data/db2/windows/dotnet.html.
Once you’re able to connect to a DB2 server using .NET, you
need to install the IBM Database Add-Ins for Visual Studio to get the rich
integration provided between DB2 and Visual Studio 2008. As of DB2 9.5, this
add-in is used for any IBM data server connections; whether you’re building a .NET
application that runs on DB2 for Linux, UNIX, or Windows, DB2 for z/OS, DB2 for IBM i
(formerly known as DB2 for i5/OS), or IBM Informix Dynamic Server (IBM IDS),
you use the same add-in. This makes deployment for heterogeneous environments more
streamlined because the IBM Database Add-Ins for Visual Studio is a mere 30 MB.
In DB2 9, this add-in used to be called the IBM DB2 Add-In for Visual Studio
2005. As you can see, its name has since changed (the version information has
been removed, as well as the DB2 moniker) to reflect the fact that the same
add-in can now be used in not only the Visual Studio 2005 or Visual Studio 2008
integrated development environments (IDEs) but also for any IBM data server.
The only way to get this add-in in DB2 9 was to install a Windows-based
DB2 Client or a server image; this meant a significant footprint (over 150 MB).
Quite simply, DB2 9.5 gives you the opportunity to move the footprint required
for .NET development from over 250 MB to a mere 40 MB by installing the IBM
Data Server Driver for ODBC, CLI, and .NET and the IBM Database Server Add-Ins for
Visual Studio. Keep in mind that if you wanted to connect this development
environment to DB2 for z/OS or DB2 for i, you would need to add a DB2 Connect
license into the connection flow (either directly or through a DB2 Connect
gateway); however, this won’t affect the size of the footprint required for
connectivity. You can download the IBM Database Add-Ins for Visual Studio at: https://www14.software.ibm.com/webapp/iwm/web/reg/download.do?source=swg-vsai&S_PKG=dl&lang=en_US&cp=UTF-8.
Note: As of DB2 9, a
DB2 data server release with a full version number (or a .5 version number)
generally contains new functionality or significant upgrades (for example, DB2
9.5). In contrast, the client connectivity portion of DB2 (clients or drivers) is
updated more frequently. Since they are not tied to the data server release
schedule, maintenance upgrades (also called fix packs) are just as likely as
new versions and point releases to contain new connectivity functionality. Our development
laboratories work very hard to ensure backward compatibility. This allows you
to gain the benefits of newer client-side functionality in a more dynamic
nature. For example, Visual Studio 2008 support is part of the IBM Database
Add-Ins for Visual Studio as of DB2 9.5 Fix Pack 1. IBM was able to deliver
this timely support for Visual Studio 2008 because of this new client delivery
architecture.
When you use this lightweight deployment option to configure
your developer’s desktop, you should keep in mind that the IBM Database Add-Ins
for Visual Studio and the IBM Data Server Driver for ODBC, CLI, and .NET must
be at the same code level. For example, if you plan to support the Visual
Studio 2008 IDE, you must install these components at the Fix Pack 1 level. If
Fix Pack 2 were to bring other functional changes to either of these
components, and you wanted to leverage them, you would have to ensure that both
components were at Fix Pack 2.
For anything related to .NET development, this Web site is
an all-inclusive starting point for pretty much anything you need to get
started: www.ibm.com/software/data/db2/windows/dotnet.html.
If you want a free copy of DB2 to get started with .NET development, you can
find it at: www.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-db2expresscviper2.
(You can even use this edition in production.)
Assuming you have configured your .NET development
environment for DB2 (DB2 will take care of this automatically for you when
you install the add-ins on a workstation where Visual Studio is already
installed), when you start Visual Studio 2008 you should be able to see that
the IBM Database Add-Ins for Visual Studio have been successfully registered:
Adding a DB2 Data Source
Before you can create a .NET application that works with a
DB2 data server, or work with your DB2 data server using Visual Studio 2008 for that matter, you need
to set up a database connection in the Visual Studio Server Explorer.
To add a DB2 database connection to your Server Explorer,
perform the following steps:
1. Right-click
the Database Connections folder in the Server Explorer and select
Add Connection:
2. The Add
Connection dialog box opens.
3. Ensure
that the Data Source field is set to IBM DB2, IDS,
and U2 Servers (IBM DB2, IDS, and U2 Data Providers)
like this:
If this is not
the case, click Change, select IBM DB2, IDS and
U2 Servers in the Data source box and the appropriate
provider in the Data Provider box.
You can see there’s a data source
option called IBM IDS Server. You would use this
option to connect to versions of IBM IDS before Version 11.10. If you can, I
strongly recommend using the IBM DB2, IDS and U2
Servers driver. Refer to the “Addendum: Two Informix .NET Drivers?” section
at the end of this article for more details if you are connecting to an IBM IDS
server.
4. Enter
the server name in the Select or enter server name field. You can click Refresh
to return a list of DB2 servers discoverable on your subnet network.
5. In
the Enter information to log on to the server field, enter a user account
that’s authorized to access that database using the User ID and Password
fields:
If you select the Save my
password check box, these credentials will be persisted so you don’t have
to enter a password every time you connect to your data server in the Solution
Explorer. For example:
6. Enter
the database alias name of the target database that you want to connect to in
the Select or enter a database name field.
You can use this field’s drop-down
list (
)
to see all the databases in your local instance catalog.
7. Expand
the Specify connection options twistee and select both the Automatic
refresh of connection when IDE loads and Run procedures to discover
results sets options:
The Automatic refresh of
connection when IDE loads check box instructs Visual Studio 2008 to refresh
the schema cache every time the Visual Studio 2008 IDE starts. Part of the rich
feature set in the IBM Database Add-Ins for Visual Studio is an asynchronous
schema cache of your database connection. This feature enables you to work in a
disconnected mode from your database and still have access to its schema objects
for drag-and-drop form creation, design-time SQL assistance, and more. Normally,
I recommend selecting this option because sometimes objects may have been
created between Visual Studio 2008 sessions and they may not show up in the
Server Explorer until the object cache is refreshed explicitly by the user or
implicitly by Visual Studio itself; selecting this option automatically
refreshes the schema cache when the IDE starts.
The IBM Database Add-Ins for
Visual Studio 2008 also enables you to define new result sets – and views – for
your stored procedures. A result set is the set of rows that a stored procedure
returns for a SELECT statement. You can either discover result set definitions
by specifying values for the input values, or you can manually define a result
set and its columns. The Run procedures to discover results sets option
instructs Visual Studio 2008 to run your stored procedures and define their
results sets; if you don’t select this option, you’ll have to do this manually.
For example, the following figure shows what happens when you click a stored
procedure’s Results Sets node for a database connection that doesn’t
have the Run procedures to discover results sets feature enabled:
In contrast, if you enable this
feature, when you click on Results sets it would look like this:
8. Optional:
If your database has a large number of objects, you can filter the connection
object you are creating by entering a schema name in the Schema filter
field:
For example, the setting in the
previous figure would have the effect of only showing those objects within the
PAULZ schema in the Server Explorer. You can’t specify more than one schema to
filter on in this window; if you want to filter on a different schema, you need
to create a different database connection object.
You can use the Exclude system
schemas check box as a higher-level schema qualifier such that only user
schemas are shown in the Server Explorer. For example, you may have a number of
user schemas in your database (PAULZ, TEST, CHLOE); if you left the Schema
filter field empty but selected this option, it would hide objects within
the SYSWS, SYSVIEWS, SYSSTAT, SYSPROC, SYSIBMADM, and SYSIBM schemas; this is a nice way
to suppress seeing those administrative schemas that are part of every DB2
database but that you typically don’t interact with.
You can use the Select Table Type
Filter window to select the types of tables that you want to see in the Server
Explorer under a data server connection object. To filter the types of tables,
click the table filter button (
):
Using the Select Table Type Filter
window, you can select either the default table types for a data server or the table
types to show for a specific data connection.
